Perform Analysis Services database backups using Powershell

I was recently looking into performing SQL Server Analysis Services database backups using Powershell within an automated task, with the following requirements: 

  • Perform a backup of all databases for running instances of Analysis Services.
  • If the target location does not exist, create the directory.

Firstly we need to load the Microsoft SQL Server Analysis Services binaries in order to connect to the instance and invoke actions. 

Capture1

I will also retrieve the computername and store this in a variable to use later in the server connection properties. 

Capture2

Using the Get-Service cmdlet, I will return all running instances  by filtering the output to include where the service name is like *MSOLAP$* and the status is ‘Running’.

Capture3

Once, I have returned the running instances of Analysis Services I will loop through each one to firstly obtain the instance name by manipulating the service name returned in my collection and then connecting to the instance using the binaries previously loaded into the powershell session. 

Capture4

Now, I will be required to loop through each database within the instance and to check if the target location exists and if not create the folder. 

Capture5

Finally, we will create a backup of the database to which I will append the date string ddMMyyyy_HHmmss to the the filename. 

Capture6

The above can be automated by creating a task in Task Scheduler and configuring a trigger to perform the action of invoking the powershell script. 

The powershell script can be downloaded from: 

 https://app.box.com/s/x000axp5qu37y3uzs1ue

Advertisements

Preparing a virtual machine template with Microsoft SQL Server 2012

A recent requirement was to create a template within VMware to deploy a configured virtual machine with a named Database Engine instance of SQL Server 2012 installed. Previous versions of SQL Server did not allow for a clean sysprep image, in SQL Server 2012 this has changed with the introduction of the concept image preparation and image complete of stand-alone instances of SQL Server. So it looks like its time to through away those configuration scripts for previous automated deployments of SQL Server 2005 and 2008!

So how did I go about this…

I currently have a base template of Windows 2012 Server Datacenter Edition, which I will deploy a virtual machine to configure as my template for SQL Server installations, also prior to beginning this process, I have created an additional hard disks for my virtual machine to host the database files.

Now for the configuration steps to create the SQL Server template. Firstly we need to prepare a stand alone instance of SQL Server.

1) Mount the installation media in the CD/DVD drive (or copy the contents of the media to an installation folder) and begin the setup process.

2) From the SQL Server Installation Center, select Advanced.

3) Select Image preparation of a stand-alone instance of SQL Server.

4) Select product updates available which will be installed during the SQL Server features setup.

5) Select the features you want to prepare, currently only Database Engine and Reporting Services features can be selected.

6) Configure your Instance ID and Instance root directory.

7) The SQL Server features will now be ready to be included in the prepared image, this will create a configuration file at ‘C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\YYYYMMDD_HHMMSS\ConfigurationFile.ini’.

8) Select Prepare to complete the Image process. If prompted restart the computer, to complete the installation before capturing your image.

Once the prepare image is complete and you have configured the virtual machine as per the requirements for your template, capture the Sysprep image and convert the virtual machine to a template.

Now, we are going to deploy the  virtual machine from the template captured in the above steps, in this instance I use a customization wizard to configure a number of settings.

Once the customization wizard has completed, and the deployed virtual machine is available, we now need to complete the configuration of a the prepared imaged instance of SQL Server.

1) Begin the setup process from either the mounted installation media or from the installation folder.

2) Select Advanced.

3) Select Image completion of a prepared stand-alone instance of SQL Server.

4) Enter the product key

5) Select the ID of the prepared instance to complete and review the features in the prepared instance.

6) Specify the instance name for the prepared instance.

7) Specify the service accounts and the collation configuration

8) Specify the authentication mode, administrators and data directories.

9) The SQL Server features will now be ready to be configured and completed, this will create a configuration file at ‘C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\YYYYMMDD_HHMMSS\ConfigurationFile.ini’.

10) The image process will now be completed. If prompted restart the computer, to complete the installation before capturing your image.

Obviously, I do not want to perform this attended completion of a prepared stand-alone instance of SQL Server following each deployment of the template. This is where the configuration file created to complete the configuration file will allow for this to be run once following deployment.

I will run a script to run once on deployment, to complete the configuration of the prepared image instance of SQL Server as below:

Setup.exe /ACTION="CompleteImage" /QS /INSTANCENAME="SQL12" /INSTANCEID="SQL12" /AGTSVCACCOUNT="NT Service\SQLAgent$SQL12" /AGTSVCSTARTUPTYPE="Automatic" /SQLSVCSTARTUPTYPE="Automatic" /SQLCOLLATION="Latin1_General_CI_AS" /SQLSVCACCOUNT="NT Service\MSSQL$SQL12" /SQLSYSADMINACCOUNTS="Domain\Group" /SECURITYMODE="SQL" /SQLBACKUPDIR="D:\Backup" /SQLUSERDBDIR="D:\Databases" /SQLUSERDBLOGDIR="D:\Logs" /SQLTEMPDBDIR="D:\TempDB" /ADDCURRENTUSERASSQLADMIN="False" /TCPENABLED="1" /NPENABLED="0" /BROWSERSVCSTARTUPTYPE="Automatic"

Once the script has been run as above, a configurable instance of SQL Server will be available.

So based on the previous versions of SQL Server and the steps required post and pre deployment of a SQL Server template the above steps are simple to use and reduce the deployment time for new instances of SQL Server.