Using SQLCMD Mode for Always On Availability housekeeping.

One way i tend to query multiple servers in one go is to use Registered servers, but there are times when I might need to run something against a specific set of servers which i do not have a Registered servers group for, or maybe i need to select these servers dynamically.

Maybe i do happen to have the correct set of servers there in my registered servers tab but they are not grouped how i want them so it means having to shift servers around in my folders, run my query then return the folders back to how they were via the ‘Move to’ option.

There is another way, SQLCMD Mode in SSMS.

I use a method from time to time that I would like to share with you which makes what can sometimes be a somewhat long winded task much quicker and easier without the need for any third party tools or Powershell scripts (I have nothing against these of course 🙂 ) I just tend to stick with SQL where possible but that’s just my preference.

 

Here is the Scenario!

 

We are connected to the primary server for the Availability Group ‘AG1’ and we need to replace an existing Training database on the Primary node with a Copy from the Live counterpart.

We COULD do the following:

1. Remove the database from the AG
2. Expand the Replicas for the AG to identify the Server names for the Secondary replicas , in my case SQL02 and SQL03
3. Manually connect to each Secondary replica one at a time and execute a Drop Database command for the Secondary database which will now be in a restoring state.
4. Replace the Database on the Primary, then re sync to the AG

OR

We could simply do things with far less mileage on the mouse 🙂

1. Run a script to output a Remove from AG command and Drop database commands with respective connections to each of the Secondary servers, without the need to expand that really slow at times Availability group folder in the tree! there have been times when i have waited for over a minute just for that folder to expand…
2. We can use the statement produced from the above step and execute it using SQLCMD mode within SQL Server Management Studio.

 

You do not even need to have linked servers set up to connect to each Secondary or the Primary!

SQLCMD mode will use your Window login to connect to each of the nodes within your statement,  so the only prerequisite is that you have access to the servers via Windows Authentication which I assume you would have if you are in a position to be Dropping databases and Syncing to an AG.

 

3.Replace the Database on the Primary, then re sync to the AG

 

So what does such a script look like and how is it used?

 

We can use a script like below (can be executed on the Primary or Secondary’s) :

SET NOCOUNT ON
DECLARE @Databasename NVARCHAR(128) = 'SQLUndercover'

--Check the Database exists on this server and that it is online
IF EXISTS(SELECT Name FROM sys.Databases Where Name = @Databasename)
BEGIN

DECLARE @AGname NVARCHAR(128)
DECLARE @PrimaryServerForAG NVARCHAR(128)
DECLARE @LineFeed VARCHAR(20) = CHAR(13) + CHAR(10)
--Check which server is the Primary server for Availability group of the Database being passed in
SELECT
@PrimaryServerForAG = primary_replica,
@AGname = ag.name
FROM sys.dm_hadr_availability_group_states st
INNER JOIN master.sys.availability_groups ag ON ST.group_id = AG.group_id
INNER JOIN Sys.availability_databases_cluster ADC ON AG.group_id = ADC.group_id
WHERE ADC.database_name = @Databasename

--Primary Server Statement
SELECT
@PrimaryServerForAG + ' (Primary)' as ServerName,
':CONNECT '+@PrimaryServerForAG +@LineFeed+
'GO ' +@LineFeed+
'ALTER AVAILABILITY GROUP ['+@AGname+'] REMOVE DATABASE ['+UPPER(@Databasename)+']'+@LineFeed+
' WAITFOR DELAY ''00:00:05''' +@LineFeed+
'GO' +@LineFeed as Statements

UNION ALL

--Secondary server/s Statements
SELECT DISTINCT
Replica_Server_name + ' (Secondary)' AS ServerName,
':CONNECT '+Replica_Server_name + '
GO ' + '
IF Exists (Select name from sys.databases where name = '''+UPPER(@Databasename)+''' AND State = 1)' +@LineFeed+
' DROP DATABASE ' + '['+UPPER(@Databasename)+']' +@LineFeed+
' GO' +@LineFeed as Statements
FROM master.sys.availability_groups ag
INNER JOIN master.sys.availability_replicas ar on ag.group_id = ar.group_id
INNER JOIN master.sys.dm_hadr_database_replica_states drs on ar.group_id = drs.group_id
Where drs.database_id = DB_ID(''+@Databasename+'')
AND Replica_Server_name != @PrimaryServerForAG

END
ELSE
BEGIN
PRINT 'The Database ['+@Databasename+'] Does not exist on this server or is not ONLINE'
END

I have found that The WAITFOR DELAY is important, it gives SQL the chance to remove the secondary databases from the AG therefore avoiding errors when attempting to Drop the database from the Secondary servers. I have used a two second delay with no issues but for this post I have used five seconds.

The Output should look similar to this:

 

Copy the results from the results grid and paste into a new query window:

 

It is very important at this stage that the formatting resembles the below (Each command needs to be on its own line and batch separated by a ‘GO’):

 

 

If you find that the formatting from the Copy and Paste has not achieved the above format then try switching the results to text option on within SQL Server Management Studio (Query > Results To > Results To Text – OR CTRL-T)  or if you are using SSMS 2016+ there is an option within Query Options > Grid . Check the box next to ‘Retain CR/LF on Copy or Save’ (you will have to open and use a new Query window for the changes to take effect) then execute the statement again to produce the results and paste into the query window , this should preserve the formatting from the line feeds.

 

Now you have the query ready in the Query window its time to switch to SQLCMD Mode, you can enable this by going to the Query Menu > SQLCMD Mode , nothing really happens visually here apart from the icon in the menu becomes highlighted but Management Studio looks the same as it did before but what you will notice is that the ‘:Connect’ commands now have grey highlights as below:

 

 

So now we are ready to execute the command , Hit F5

 

The Messages tab will show some progress of which server is being connected to…

 

 

If all goes to plan then the query will have executed successfully and you would have dropped the databases from the Secondary servers by executing just one query using one connection leaving you to simply replace the Training database with that copy taken from Live and then resync it to the AG.

Don’t forget to disable SQLCMD Mode when you are done if you plan on using the query window you have open.

Once you have the hang of this method I think you will find it a lot quicker than the Manually connecting method but i guess this all depends on how many replicas you have in you AG, I would probably say that you are unlikely to see any gain using this method if you have less that 3 Replicas in your AG.

 

Thanks for reading.