ShaunBinkley
Enthusiast
Enthusiast

Upgrading AO SQL Database

Upgrading from 1811 to 1903, do I need to run the DB installer on both DB servers or just the primary? Documentation assumes you're not HA
Labels (1)
0 Kudos
6 Replies
ManaenSchlabach
Contributor
Contributor

Shaun,
I don't know what type of HA you have implemented but it sounds like perhaps it's an AG with two servers and a witness? My experience has been that it is better to run the DB installer on both servers. There are lots of .net objects, sometimes additional required SQL packages, etc..., and if you are in an AG vs an FCI it's a pain to replicate all of the SQL Server Agent jobs (many of them change). We usually do the folllowing:


1. Suspend data movement on the secondaries
2. Remove AW (WS1 UEM) DB from AG on secondaries
3. Turn off SQL Server Agent on the primary and secondaries
4. Take a full backup (we do log backups every 15 min so this isn't necessary but it doesn't hurt). The backup is done with the ' copy only'  option to prevent logfile/backups from getting out of order. If your DB is large a multipart backup will help tremendously. I have found a 4 part backup to be ideal for us.
5. Take a log backup.
6. Run the installer.
Once the DB installation is complete work on other servers proceeds and I then run the program on the secondary
On the secondary
1. Drop the DB
2. Create empty blank DB as outlined in onprem installer guide.
3. Run the installer
4. Drop the AW DB again when the installer is complete
5. Restore the full backup from the other server to the local one with the ' no recovery option'
6. Restore the log file backup from the other server with ' no recover option' . I have sometimes had to take another log file backup from the primary and restore on the secondaries
7. Rejoin any/all secondaries to the AG. If you are using TDE this would have to be done with T-SQL there is a bug in SSMS that prevents you from doing this using the GUI once TDE is turned on.
8. Verify the AG/HA solution is working as expected.
9. Restart the SQL Agent services on all servers.


Hope this helps

0 Kudos
chengtmskcc
Expert
Expert

I'm not a DBA guy and trust my DBA team handles the SQL backend well. However, I never had to take any of the steps above. I simply run the DB installer usually from the console server that has a connection to DB server and upgrade it accordingly.
0 Kudos
ShaunBinkley
Enthusiast
Enthusiast

Thanks both, yea 2 AO servers in an AG. I ran the installer on both and the patch just on the database from the active server. Thanks for taking the time to respond. As you say Thomas, my DBA team handle all the 'icky' bits
0 Kudos
chengtmskcc
Expert
Expert

Cool. The DB installer mainly updates the table of the DB I think. So if your SQL servers are talking to each other there should be no need to run the installer on both.
0 Kudos
ManaenSchlabach
Contributor
Contributor

Thomas,  It can involve only schemas, sprocs, etc...however there is *no* guarantee of that.  I have been upgrading since 7.3 and as I mentioned it can also involve additional packages like .NET, Windows components, additional SQL components, etc....  Check with your rep he can verify that this is the case from time to time.  The approach I suggest will work no matter what gets updated however it's possible to make it work without doing that too....however you could be creating a headache for your DBAs since they may have to address a bunch of small errors one at a time on secondaries when a failover occurs and it becomes the primary that would have been corrected at install/upgrade time.
0 Kudos
BDBos
Enthusiast
Enthusiast

Manaen, thanks for sharing your workflow. We have now been running on-prem for more than 3 years with an HA setup. 2 SQL servers, with a witness. Just like Thomas, we run the setup once. This has never caused problems. We do, however, always run an extra backup on the sql server as you describe.
0 Kudos