Last week I had to move some databases quickly from one SQL server environment to another. Traditionally you would have built your new SQL servers, backup and restored your databases, user logins and SQL agent jobs and then run some VB scripts from your BizTalk Server to reconnect everything up (https://msdn.microsoft.com/en-us/library/aa547833(v=bts.20).aspx). This article describes another way to do this if you have chosen to use availability groups.
I had built a BizTalk 2016 on premise environment using SQL 2016 Availability groups for two talks that I did at Ignite NZ 2016 and on Integration Monday. I built this before Windows Server 2016 went RTM and had used the latest technical preview that was available at the time. I was preparing to put this into cold storage in case I wanted to demo this again but first I wanted to change to a supported version of Windows server. Windows Server 2016 Standard Technical Preview 5 expires this month and I tried upgrade to Windows Server 2016 but this was not allowed. I began to think about the traditional way of moving databases above but my brain began to hurt when I thought about how I was going to handle the availability groups etc but….then the light bulb went on
I decided to use SQL Always ON and failover to move my databases to a new server and then retire the old servers.
- I created two new Windows Server 2016 Standard Servers and joined them to the same Windows Failover Cluster that the Windows 2016 Server TTP5 were part of.
- I installed SQL 2016 Developer Edition on these two new servers and installed the same SQL instances that I had on the TTP5 servers.
- I used a SQL service account for all instances and SQL server agents.
- I scripted all the logins and SQL agent jobs from the TTP5 server and applied these to the same instances on the new SQL servers. So now we had a new SQL servers like this without any BizTalk databases.
- On one of the SQL servers I had to open the registry and explicitly set the FQDN instance names for the SQL agents to run. e.g
- I copied all the firewall exclusions for the SQL instances and the SQL instance endpoints to the new servers.
- I created the SQL Endpoint listeners on each instance with a script like this — =============================================
— On both nodes, create an endpoint called AG1_endpoint that listens on TCP port **7022**
— Run this script once on each node. PLEASE DO THIS OR ELSE YO WILL HAVE PAIN.
— Create endpoint on server instance that hosts the primary replica:
CREATE ENDPOINT AGbts16sqlmgt_endpoint
AS TCP (LISTENER_PORT=7023)
FOR DATABASE_MIRRORING (ROLE=ALL)
- On each instance I added the two new replicas and started the synchronization. GOTCHA here: I had to remove one of my failover replicas before doing this because you can only have a maximum of three failover replicas. If you ignore this then all the steps work except you get an error like when you try to failover.
- Finally I failed over to one of the new servers , removed the old TTP5 servers and evicted them from the windows cluster. It was gratifying to see that BizTalk server kept running through out this process.
- I had to add two linked servers so that the “BizTalk Backup’ SQL agent job would run if a failover occurred. If you don’t do this you get “ Could not find server ‘bts16_listener\runtime’ in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.”
select name from sys.servers
EXEC sp_addlinkedserver @server=’bts16_listener\runtime’
EXEC sp_addlinkedserver @server=’bts16_listener\tracking’
select name from sys.servers
What I have learnt from this experience is that you can use the Availability groups as a way of moving BizTalk database to new servers without much downtime and fuss. This also shows how one might carry out a DR test.