I am in process of upgrading a 50+ SQL Servers from SQL 2008 to SQL 2012.
It looks like it will be closer to 100 SQL Servers.
If I had to click something 100 times – I’d automate or I may call in sick that day. SQL Upgrade requires quite a bit more steps than just one click. Times 51 number of servers. Plus I can’t call in sick for three months. And even if I did, no one would do the upgrade for me, so the only logical option left is to automate the upgrade. So here we go.
(To clarify to ones with no sense of humor – the last time I called in sick was probably 2y ago.)
Prep (one time) steps:
– Created scripted SQL Server installation
– Get latest Cumulative Update(CU). I used to not install CUs and wait for Service Packs(SP) to come out, but this post got me thinking and I may install CUs this time. Not 100% decided on this yet.
Here is my master plan – which I will updated as I go. No promises though:
– keep 2008 instance running
– verify win firewall. Keep this on or off?
– install SQL 2012 using scripted install (so all of my servers are EXACTLY same)
– on SQL2012 instance: update instance settings
– on SQL2008: setup Ola’s backup scripts on SQL2008
– get a go ahead from server owner (this is specific to this client)
– on SQL2008: backup system and user dbs
– on SQL2008: run detach/attach script generator. This will generate me a script that will detach all dbs. Then I am going to take part2 of that script and run on SQL2012 to attach all dbs
– on SQL2008: generate TSQL for adding security accounts
– on SQL2012: run security script
– on SQL2008: generate TSQL for linked servers
– on SQL2012: run linked servers script
– on SQL2012: enable SQL Mail
This is the order I am doing this in. Above items, some can be moved up or down. The below steps need dbs to be attached to work. Let’s keep going:
– on SQL2012: run attach script I generated above
– on SQL2008: generate replication TSQL
– on SQL2012: run replication generating TSQL
– on SQL2008: generate SQLAgent job script
– on SQL2012: run SQLAgent script
– on SQL2012: Setup SQL Audit. Got a that just need to execute
– on SQL2012: backup sys & user dbs
Now let’s switch over the instances so whole upgrade is invisible to developers:
– on SQL2008: switch SQL Service port (to say 5000)
– on SQL2012: switch SQL Service port to port we currently use (to say 6000)
– on SQL2008: disable SQL2008 services
– test
– talk to developers if they are seeing any issues
There is a lot of flipping back and forth from SQL2008 to SQL2012 instance. I am hoping that by the time I am upgrading third server I will have the whole process automated (PowerShell?) and all the scripts figured out.
For now I am generating SQL scripts on SQL2008 and then running on SQL2012. I am keeping those two steps in a pair as my quality control to make sure each step does what it needs to. Later on, I will just generate all and run all scripts.
By the time I am on the third server I want to have the whole process automated. So the remaining server can be upgraded by my teammates so I can kick back and enjoy other people working 🙂
And as always, if you know how to make this process better, don’t be shy and use comments below!