How-to : Setup AlwaysOn Availability Group on SQL Server 2012


SQL Server 2012 has very nice HADR technology built-in, AlwaysOn Availability Groups. It allows to have multiple replicas of selected databases across multiple servers, which can be even located across multiple sites. AlwaysOn itself provides mechanism to keep all replicas of database synchronized and up-to-date, as some of them might serve data for read-only purposes (for example reporting).

MSDN article: AlwaysOn Availability Groups (SQL Server) describes AlwaysOn technology in details. In this article I will focus on practical side of this HADR technology and how to get this up and running fast without any issues.

To test and demonstrate SQL Server 2012 AlwaysOn capabilities I built simple Lab environment on VMware Workstation as shown below. Lab contains 2 servers with Windows Server 2012 R2 Datacenter and SQL Server 2012 Enterprise.

Lab environment also contains Active Directory domain and Domain Controller, which is not shown on the diagram.

In order to get SQL Server AlwaysOn up and running we will complete following activities:

  • Install Failover Clustering Role
  • Configure Windows Server Failover Clustering
  • SQL Server installation
  • Demo database preparation
  • Enable Availability Groups on SQL Server
  • Creating AlwaysOn Availability Group

So, let’s get started…



Tips & Tricks : Missing SQL Server assmeblies in PowerShell


When you try to connect to SQL Server from PowerShell script and you get following message:

Unable to find type [Microsoft.SqlServer.Management.Smo.Server]

then you have to download and install following components:

  • Microsoft System CLR Types for SQL Server 2008 R2
  • Microsoft SQL Server 2008 R2 Shared Management Objects
  • Microsoft Windows PowerShell Extensions for SQL Server 2008 R2

From here.


Tips & Tricks : SQL Server “Cannot connect to WMI provider”


Recently I had issue which occured without any particular reason on SQL Server 2012. All of the sudden I start getting error message:

Cannot connect to WMI provider. You do not have permission or the server is
Note that you can only manage SQL Server 2005 servers with SQL
Server Configuration Manager.
Invalid Namespace.

when tried to run SQL Server Configuration Tools.

What resolved situation is command:

mofcomp C:\Program Files\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof

Tips & Tricks : Microsoft SQL Server 2008 & 2012 Feature Packs


Some of applications require additional SQL Server 2008 or 2012 fetures installed. Some of these features might not be available in standard setup of SQL Server. In that case you have to refer to Microsoft Dowloads web page in order to download additional components required for installation:


Code snippets : SQL : Backup and FTP Database


When browsing repository of all scripts I did use in past, I found one which might be interesting. What this script does is:

  • Backups database to disk (name of database stored in @dbname, patch for backup stored in @archive_path, name of backup fine stored in @fname)
  • Compresses database backup using WinRAR (full command where to find WinRAR and how ot use it stored in @archive_cmd)
  • Generates set of commands for FTP client (series of xp_cmdshell commands redirecting output to F:\Backup\ftpcmds)
  • Uploads compressed backup to FTP server using command prompt FTP client command

I had that as small procedure executed automatically with SQL Server Agent. Script is for Microsoft SQL Server and I was running it on SQL Server 2000 and 2005. Here is code for that small procedure:


DECLARE @fname varchar(255)
DECLARE @fname_backup varchar(255)
DECLARE @fname_archive varchar(255)
DECLARE @archive_path varchar(255)
DECLARE @dbname varchar(255)
DECLARE @archive_cmd varchar(255)
DECLARE @put_cmd varchar(255)

SET @dbname = 'dbApplication'
SET @fname = @dbname + '_' + LTRIM(STR(DAY(GETDATE())))  + '.' + LTRIM(STR(MONTH(GETDATE()))) + '.' + LTRIM(STR(YEAR(GETDATE())))
SET @archive_path = 'F:\Backup\'
SET @fname_backup = @archive_path + @fname + '.BAK'
SET @fname_archive = @archive_path + @fname + '.RAR'
SET @archive_cmd = '"C:\Program Files\WinRAR\WinRAR.EXE" a ' + @fname_archive + ' ' + @fname_backup
SET @put_cmd = 'echo put ' + @fname_archive + '>> F:\Backup\ftpcmds'

PRINT @fname_backup
PRINT @fname_archive

BACKUP DATABASE @dbname TO DISK = @fname_backup WITH INIT

EXEC xp_cmdshell @archive_cmd
EXEC xp_cmdshell 'echo open> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo user dbupload>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo password123>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo cd DB_Backup>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo bin>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'echo ha>> F:\Backup\ftpcmds'
EXEC xp_cmdshell @put_cmd
EXEC xp_cmdshell 'echo bye>> F:\Backup\ftpcmds'
EXEC xp_cmdshell 'ftp -d -i -n -s:F:\BACKUP\ftpcmds'

I hope that will be handy for someone to automate databsae backups and transfers.

Page 1 of 212