Create Availability Group in SQL Server 2012 Step by Step

The following are the steps needs to create an Always on Availability Group.

The SQL Instance used for this exercise are SQL Server1 and SQLServer2.

I going to use 3 user databases (ContainedDB, ReportServer and AGTest) in my SQLServer1 for creating Availability group

1.       Take a Full Backup followed by T-Log backup of all the databases on the primary server (SQLServer1) which needs to be included to availability group.

BACKUP DATABASE [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_full.bak' WITH INIT,STATS = 1

GO
BACKUP DATABASE [ReportServer] TO DISK = 'T:\Backup\ReportServer_full.bak' WITH INIT,STATS = 1
GO
BACKUP DATABASE [AGTest] TO DISK = 'T:\Backup\AGTest_full.bak' WITH INIT,STATS = 1
GO

BACKUP LOG [ContainedDB] TO DISK = 'T:\Backup\ContainedDB_trn.bak' WITH INIT,STATS = 1

GO
BACKUP LOG [ReportServer] TO DISK = 'T:\Backup\ReportServer_trn.bak' WITH INIT,STATS = 1
GO
BACKUP LOG [AGTest] TO DISK = 'T:\EREISQLUS01_TEST2012\Backup\AGTest_trn.bak' WITH INIT,STATS = 1
GO
 
2.       Restore the Full backup and Log backup taken in step1 in NORECOVERY mode on the secondary server(SQLServer2), You can ignore Step 1 and 2 and choose Availability group wizard to take the backup and restore but I find this way quicker and efficient.

RESTORE DATABASE [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_full.bak'

WITH MOVE 'ContainedDB' TO 'G:\Data\ContainedDB.mdf',
MOVE 'ContainedDB_log' TO 'P:\TLog\ContainedDB_log.ldf',NORECOVERY,STATS = 1
GO
RESTORE LOG [ContainedDB] FROM DISK = 'H:\Backup\ContainedDB_trn.bak' WITH NORECOVERY,STATS = 1
GO

RESTORE DATABASE [AGTest] FROM DISK = 'H:\Backup\AGTest_full.bak'

WITH MOVE 'AGTest' TO 'G:\Data\AGTest.mdf',
MOVE 'AGTest_log' TO 'P:\TLog\AGTest_log.ldf',NORECOVERY,STATS = 1
GO

RESTORE LOG [AGTest] FROM DISK = 'H:\Backup\AGTest_trn.bak' WITH NORECOVERY,STATS = 1

GO

RESTORE DATABASE [ReportServer] FROM DISK = 'H:\Backup\ReportServer_full.bak'

WITH MOVE 'ReportServer' TO 'G:\Data\ReportServer.mdf',
MOVE 'ReportServer_log' TO 'P:\TLog\ReportServer_log.ldf',NORECOVERY,STATS = 1
GO

RESTORE LOG [ReportServer] FROM DISK = 'H:\Backup\ReportServer_trn.bak' WITH NORECOVERY,STATS = 1

 GO

3.   Connect  to SSMS of SQLServer1 expand AlwaysOn High Availability-> Right Click on the folder ->select New Availability Group Wizard

4.  Give a name to the Availability Group you want to create and Click Next, 

5.  Select the databases which need to be a part of Availability group and click next

6. Click Add Replica and connect to the secondary server, repeat the step if you want to add more than one replica.

7. I always keep the secondary databases read-only, select appropriately in Readable secondary column.

8. Select the Backup preferences menu on the page and select the appropriate backup preference, I leave it as default(Prefer Secondary)

9. Listeners can be created after the AG is set up.

10. Since we have restored the databases in secondary earlier I select Join option.

11. Click Next

12. Click Next

Availability group has been created now; let’s go back to the server to check it,
 

As shown in the picture above Availability group AGTest can be seen in both Primary and secondary server with corresponding Replica Details.
 

Now let’s see how to create a listener for the Availability group, Go to AlwaysOn High Availability -> AGtest->Availability Group Listeners; Right Click on the folder and select Add Listener.

 

Now fill the port number and Listener group Name(Note:- This Listener Group name and Port number are the details which will be used to connect to SQL Server by the users)

 

I usually chose Static IP Network mode to avoid any firewall and network issues, you can use DHCP if you like.

 

Click OK

Now that the Listener AGTestInstance has been created let’s see how users need to connect to AGTest.

 

In SSMS connect to the <ListenerName,Port Number> as the server name and connect to the AG.

 

PowerShell Script to Assign Static TCP Port to SQL Server Instance

I am designing a new SQL server Farm with more than 100 Instances for one of my clients and it would have been huge effort if I had to assign static TCP to each and every instance manually, but Thanks to Allen Whites Blog on PowerShell Managed Computer Objects in SMO http://sqlblog.com/blogs/allen_white/default.aspx ,This namespace pretty much gives us ability to do all the tasks which are done through Configuration Manager using PowerShell. I have written a wrapper over Allen Whites core script to assign Static port to SQL instances.

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

TSQL to Attach a database with Rebuild New T-Log option

DBA’s often need to attach a database from SQL data file most of the cases provided by Vendor or during DR where T-Log does not have any significance, So we may well create a new log file instead of copying the log file over the network.

The TSQL used to attach a database with rebuild log option is given below,

CREATE DATABASE <dbname> ON (FILENAME = ‘<FilePath>) FOR ATTACH_REBUILD_LOG

Eg:-

CREATE DATABASE Testdb ON (Filename = ‘D:\Data\Testdb_data.mdf) FOR ATTACH_REBUILD_LOG

Managing Cluster from Command Line move to Powershell cmdlets in Windows 2012

Recently I was having issues with installation of one of our SQL server 2012 on a Windows 2012 cluster, in order to trouble shoot the issue I tried to query the cluster from

Command line using “cluster res”  which error out as below,

C:\Users\ManoharV>cluster res

'cluster' is not recognized as an internal or external command,
operable program or batch file.

On further analysis in MSDN I found out that Managing Cluster from Command Line has been moved to PowerShell from Windows 2012 and can be done only through Powershell cmdlets,

So I used “get-clusterresouce” cmdlet to get the result, similarly all the actions done using cluster.exe can be done using PS cmdlet refer http://technet.microsoft.com/en-us/library/ee461009.aspx for all cmdlets related to cluster.

PowerShell Script to Install SQL Server Service Pack(SQL2012/SQL2008)

The below Script helps to Install SQL Server Service Pack (SQL 2012/SQL2008) unattended using powershell, The same script can be modifed easily for earlier versions of SQL Servers,The only version specific information is that the script reads the install log in the bootstrap folder and prints it to the host after service pack is applied/failed and the bootstrap log path changes with each version and I have included only SQL 2012 and SQl 2008 path to the script.

 # Script to Install SQL Server Service Pack  
 # Created by - Vinoth N Manoharan  
 # Version 1.0  
 # Date - 24/04/2013  
 # Script Help :-  
 #---------------  
 # Parameter 1 :- "-e" to specify the path of Setup file for the SP Install  
 # Parameter 2 :- "-i" to Specify the Instance name to be updated or "ALL" to update all the instances on a Server  
 # Parameter 3 :- "-v" to Specify the version of SQL Server; This can take only values SQL2008 or SQL2012   
 # Example1:- SQLSPInstall.ps1 -e <Setup File Full UNC Path> -i <Intance_Name> -v <SQL2008/SQL2012>  
 # Example2:- SQLSPInstall.ps1 -e U:\SQLServer2008R2SP2-KB2630458-x64-ENU.exe -i All -v SQL2008  
 # Example3:- SQLSPInstall.ps1 -e U:\SQLServer2008R2SP2-KB2630458-x64-ENU.exe -i MSSQLSERVER -v SQL2012  
 # Example4:- SQLSPInstall.ps1 -e U:\SQLServer2008R2SP2-KB2630458-x64-ENU.exe -i INST1 -v SQL2012  


 Clear-Host   
 if($args.Length -ne 6)  
 {  
 Write-Host "Incorrect Paramenter Count use -e to the path of Setup file for the SP Install, use -a to specify the Instance Name or All Instances and use -v to specify SQL Version" -ForegroundColor Red  
 $uParameterHelp = "  
 Help:-  
 ******  
      # Parameter 1 :- -e to specify the path of Setup file for the SP Install  
      # Parameter 2 :- -i to Specify the Instance name to be updated or ALL to update all the instances on a Server   
      # Parameter 3 :- -v to Specify the version of SQL Server; This can take only values SQL2008 or SQL2012   
      # Example1:- SQLSPInstall.ps1 -e <Setup File Full UNC Path> -i <Intance_Name> -v <SQL2008/SQL2012>  
      # Example2:- SQLSPInstall.ps1 -e U:\SQLServer2008R2SP2-KB2630458-x64-ENU.exe -i All -v SQL2008  
      # Example3:- SQLSPInstall.ps1 -e U:\SQLServer2008R2SP2-KB2630458-x64-ENU.exe -i MSSQLSERVER -v SQL2012  
      # Example4:- SQLSPInstall.ps1 -e U:\SQLServer2008R2SP2-KB2630458-x64-ENU.exe -i INST1 -v SQL2012"  
 Write-Host $uParameterHelp -ForegroundColor Blue  
 }  
 elseif((($args[0] -eq "-e") -or ($args[0] -eq "-E")) -and (($args[2] -eq "-i") -or ($args[2] -eq "-I")) -and (($args[4] -eq "-v") -or ($args[4] -eq "-V")) -and (($args[5].ToUpper() -eq "SQL2008") -or ($args[5].ToUpper() -eq "SQL2012")) )  
 {  
 Write-Host "START: SQL SERVICE PACK INSTALL PROGRAM MAIN"  
 $uSQLSetupEXE = $args[1]  
 $instname = $args[3]  
 $inststr = $null  
 $uSQLVERSION = $args[5]  
 if(($instname -eq "ALL") -or ($instname -eq "all"))  
 {$inststr = "/allinstances"}else{$inststr = "/instancename="+$instname}  
 $uStartProcessArg = "/q /IAcceptSQLServerLicenseTerms /Action=Patch "+$inststr  
 [datetime]$unow = Get-Date  
 #SQL BootStrap Folder SQL Install Log File  
           if($uSQLVERSION -eq "SQL2012"){$uSQLBootStrapLog = "C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\Log\Summary.txt"}  
           elseif($uSQLVERSION -eq "SQL2008"){$uSQLBootStrapLog = "C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt"}  
                Try  
                {  
                <#***************Execute SQL Server Install**********************#>  
                Write-Host "START[Exec SP]:$uSQLSetupEXE"  
                Start-Process $uSQLSetupEXE -ArgumentList $uStartProcessArg -Wait -NoNewWindow -ErrorAction Stop  
                #Print the console output  
                }  
                Catch  
                {  
                 Write-host -ForegroundColor Red "ERROR[Exec SP]:"$_.Exception.Message  
                 Write-Host "END: SQL SERVICE PACK INSTALL PROGRAM MAIN"  
                 EXIT;  
                }  
                Try  
                {  
                     #Print the SQL BootStrap Folder Log if there is new Log  
                     foreach ($uSQLBootStrapLog_readline in get-content $uSQLBootStrapLog -ErrorAction Stop)  
                     {  
                                                   if($uSQLBootStrapLog_readline -match "End time:*")  
                                                   {  
                                                         $uenddateVal = $uSQLBootStrapLog_readline.Split(":")  
                                                          [datetime]$ubootstrapdate = $uenddateVal[1].Trim()+":"+$uenddateVal[2].Trim()+":"+$uenddateVal[3].Trim()  
                                                   }  
                                                   if($uSQLBootStrapLog_readline -match "Requested action:*")  
                                                   {  
                                                        $ubootstrapstring = $ubootstrapstring + $uSQLBootStrapLog_readline+"`n`r"  
                                                        $ubootstrapstring_errorlog = $ubootstrapstring_errorlog + $uSQLBootStrapLog_readline+"`n`r`n`r"  
                                                        break  
                                                   }  
                                                   else  
                                                   {  
                                                        $ubootstrapstring = $ubootstrapstring + $uSQLBootStrapLog_readline+"`n`r"  
                                                        $ubootstrapstring_errorlog = $ubootstrapstring_errorlog + $uSQLBootStrapLog_readline+"`n`r`n`r"  
                                                   }  
                     }  
                     if($unow -lt $ubootstrapdate){Write-Host $ubootstrapstring}  
                }  
                catch  
                {  
                     Write-host -ForegroundColor Red "ERROR [Read Bootstrap Log]:"$_.Exception.Message  
                }  
                write-host -ForegroundColor DarkGreen "STATUS [Exec SP]:SQL Setup started Successfully, Check Module 4 Comments for Install Status!"  
                write-host -ForegroundColor DarkGreen "STATUS [Exec SP]:SQL Server Install Summary Log located @$uSQLBootStrapLog"  
                Write-Host "END: SQL SERVICE PACK INSTALL PROGRAM MAIN"  
 }else  
 {  
 Write-Host "Incorrect Paramenter use -e to the path of Setup file for the SP Install, use -a to specify the Instance Name or All Instances and use -v to specify SQL Version" -ForegroundColor Red  
 $uParameterHelp = "  
 Help:-  
 ******  
      # Parameter 1 :- -e to specify the path of Setup file for the SP Install  
      # Parameter 2 :- -i to Specify the Instance name to be updated or ALL to update all the instances on a Server   
      # Parameter 3 :- -v to Specify the version of SQL Server; This can take only values SQL2008 or SQL2012   
      # Example1:- SQLSPInstall.ps1 -e <Setup File Full UNC Path> -i <Intance_Name> -v <SQL2008/SQL2012>  
      # Example2:- SQLSPInstall.ps1 -e U:\SQLServer2008R2SP2-KB2630458-x64-ENU.exe -i All -v SQL2008  
      # Example3:- SQLSPInstall.ps1 -e U:\SQLServer2008R2SP2-KB2630458-x64-ENU.exe -i MSSQLSERVER -v SQL2012  
      # Example4:- SQLSPInstall.ps1 -e U:\SQLServer2008R2SP2-KB2630458-x64-ENU.exe -i INST1 -v SQL2012"  
 Write-Host $uParameterHelp -ForegroundColor Blue  
 }  

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

PowerShell Script to find SQL Agent Job details on a SQL server instance

The below script helps to find SQL Agent Job details on a SQL server instance
 

Copyright © 2013 Vinoth N Manoharan.The information provided in this post is provided "as is" with no implied warranties or guarantees.

SQL Server Cluster Installation AD Permission error SQL Server 2012 and SQL Server 2008R2

Recently when I was Installing SQL Server 2012 and SQL Server 2008 R2 Cluster, the installation completed successfully but the SQL Resources failed with the below error,

Cluster network name resource 'SQL Network Name (<SQLNetworkName>)' failed to create its associated computer object in domain 'DomainName' during: Resource online.

The text for the associated error code is: Access is denied.

Please work with your domain administrator to ensure that:

- The cluster identity '<Clustername>$' has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘<Clustername>$'.

- The quota for computer objects has not been reached.

- If there is an existing computer object, verify the Cluster Identity ‘<Clustername>$' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.

This error is due to the permission issue within the OU of the Cluster identity in the AD Server.The install creates computer objects in a staging area of AD and tries to move it to the Cluster OU when the resources are brought online. 

To resolve this either you might have to ask the Windows Admin to delegate access within Cluster OU or ask them to move the Objects from Staging Area to Cluster OU manually. Remember the SQL Server install was successful so once the permission is grated all you need to do is bring the SQL Services online.

The below MSDN article has an detailed explanation of the architecture based on the above problem(http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx#BKMK_steps_installer)