One of our clients had been running on SQL Server 2008 for so many years without issue that when the time came for them to upgrade, they were reluctant. Why change a good thing? The simple answer is that if you want to be up to date with the latest and greatest of technologies then it is a no brainer. Just do it!
That said, the upgrade isn’t as simple as just getting “it” done. You can’t just double click an upgrade exe and hope for the best. The goal should be a happy and confident client with minimal (if any) issues after go-live. Here are my steps to success for a smooth upgrade:
First thing to do is to know what you have. Our client had Sql Server 2008 Standard running on a Windows 2008 server. They had one ERP database and one custom database. We contacted the ERP provider to see if the application was certified for 2014 and for any documentation on upgrade procedures. As for the custom in-house database….well, that is what the testing is for.
Second, is to research best practices. No need to reinvent the wheel here. I found some great documents by Microsoft MSDN , Thomas LaRock and Dallas Snider that were used as starting points and guidelines. The SQL Server 2014 Upgrade Advisor is also great starting tool that shows you what you are up against. Luckily for us, our clients hadn’t done any crazy custom coding so our only issue was DTS packages and SQL Mirroring. DTS packages are obsolete in SQL Server and we were luckily enough that we deprecated the tool that was using them. SQL Mirroring was another issue altogether. However, when we analyzed the whole Disaster Recovery Plan, the SQL Mirroring strategy used was overkill. We simplified the backups and transfers to their DR site in such a way that eliminated the need for SQL Mirroring altogether. The minutes of data loss at the time of a disaster was acceptable to the management team and we moved forward with our new strategy. This significantly cut our clients costs by allowing us to stay within the standard edition of SQL Server 2014.
Third, is to build your test environment (and document every step). The first time is always the hardest (and longest), but you will find most of your bugs and get them resolved ahead of time. Now, just to clarify, there are two types of “test” environments. One is your VM Server and SQL Server “Test”. The other is the ERP and Custom Application “Test”. The first is for you and your team to test test test the setup and upgrade of the server and software. The second, is for the end users to test test test their applications to ensure that any issues that can affect their business are resolved before go-live. I will touch on these two in more detail in the next two steps below.
2. Practice, Practice, Practice and then Document it!
Virtual Machines are a beautiful thing! I can’t stress enough how practicing the upgrade steps help in eliminating most of the go-live problems usually faced. I would fire up a new VM, install Windows Server 2012, install SQL Server 2014, restore the databases, migrated the sql jobs, and viola! Testing can begin! Wash, rinse, and repeat (as my colleague likes to say) over and over until not only have you resolved all the issues, but you also have a warm fuzzy feeling inside. Most issues are usually a step missed along the way, or some updates that need to be downloaded. Sometimes though, there are problems that arise that you simply don’t have the answer for. This is where you have to take the time to find the experts in the field to lend a helping hand.
The first time building the environment, I would take screenshots of each step and note any issues. The second time was much faster having known and prepared for said issues. I would simply update the documentation with anything missed or outstanding. The third time, was easy peasy. I was now confident in what I was doing and new what to expect. It was smooth sailing and the Documentation was now complete. Now, I know that documenting is difficult for many IT peeps out there. “Who cares how I set up the server/system(s) as long as it is working?”. Sound familiar? Unfortunately, as a software company that is hired to implement software, we need to know what we are doing. We need to have our own tried and true best practices. Otherwise, how will our clients trust us and be confident in our abilities? “Winging it” just doesn’t work in my world. Check out my whitepaper on installing and upgrading to Sql Server 2014. Get your checklist in place for go-live cutover and practice it!
3. Test, Test, and more Test
The second test environment you need to set up is for the end users. Now this may not just include the Test SQL Server, but also may be a separate test application server. Regardless, you should take the time to get a completely separate environment ready for the end users so they can test all the applications they use with the new sql server. Getting the buy-in from business management is always a struggle, but it is better to convince them to invest the time before hand so that there isn’t a nightmare at go-live and all fingers are pointing at you.
Take the time to document every application using sql server and then each department procedure that touches the database. A great starting point is to get the process and procedures documents from every department. Then trim them down to only the steps that use any application accessing the database. Be sure to include times to complete the steps. Review and tweak with business management and then create the schedule. We usually spread out user testing to a two-week window. We have had users test either at their own desk or in a group testing room. This year, we used the latter and it was a great success. Users could actually focus on the testing and some great discussions resulted from it! We also found that the end users took more ownership of their work and we felt it added to the strength of the departments and teamwork as a whole. If any issues arise with testing, ensure you re-test after the problem is solved. Again, document these steps and the process! 4-6 years down the road when your client is upgrading again, you will have a template already built and with a few tweaks only, you will be ready to go!
4. Ready, Set, Go!
After all your prep work and your testing, your documentation should be a stellar piece of art, right? I think so! But that said, it is the day of go-live that is the tell-all. How are you feeling on the day of go-live? Are you nervous and sweaty palmed? Not sure what is going to happen? Or are you confident, excited and ready to go? I have to say, the day our clients went live was truly a lot of fun. I had my checklist in place, I had my team ready and we had one of the smoothest cut-overs ever!
Some of you may think my method for upgrading SQL Server is overkill, but I have to say, the day after we went live, we didn’t hear one peep from the end users. And just in case you didn’t know, that is a good thing! It was the best compliment we could have gotten. A true sign of success in my mind. Be prepared. Practice and Test till you are sick of it, and you will have a go-live day to be proud of!