- November 19, 2015
- Posted by: Warner Chaves
- Category: Database Administration, SQL Azure
As I sit here typing this blog post in my home, we are 145 days or more accurately about 3480 hours until April 12, 2016. That is the date when Extended Support for SQL Server 2005 will be done. Over. Dunzo. Kaput. Yes, Microsoft can do Custom Support Agreements for large companies but it will cost you and you still will be stuck without all the sweet features that have been released in the last 7 years (since 2008 came out). So let’s face, it’s time to upgrade SQL Server 2005!
Let me just think of 5 new features (off the top of my head) that can dramatically improve your database:
1) Backup compression on all editions.
2) Columnstore Indexes.
3) Delayed Durability.
4) Filestream and Filetable.
5) Availability Groups.
Intentionally I only put 2 out of those 5 that require Enterprise edition so if you’re running SQL 2005 Standard, you still have stuff to look forward to!
Another very interesting proposition is this:
What if I told you that this could be your last major SQL version upgrade?
That’s right, for most clients the most straight-forward upgrade path is to right now go to SQL Server 2014. It’s a more than proven data platform at this point and it has already gone past the (in)famous Service Pack #1 release milestone. So sure, you can just do your homework and go to 2014.
There’s another viable upgrade path that has opened up though and the answer to my question above. A bit over a year ago, Microsoft completely revamped their Azure SQL Database service and ditched the size based Web and Business tiers. Instead, they introduced the new service level based tiers: Basic, Standard, Premium. The platform has matured rapidly ever since where now we have support for jobs, support for database pools, Extended Events, CLR and more. In other words: this is now a PRODUCTION ready platform.
This is not slowing down, if anything more support is coming into it as Microsoft has moved to a Cloud First release cycle. So expect the new features now to come to the cloud even before it hits 2016.
Let me give you an example. You’re still in 2005 and want to upgrade to a 100% supported platform and are really attracted to that new “Dynamic Data Masking” feature? You can be upgraded and playing around with it TODAY, by deploying into Azure SQL database. No need to miss the feature by going to SQL 2014, no need to wait for the GA release of SQL 2016.
Now that I’ve got you thinking of the reasons to upgrade, let’s talk about the HOW to upgrade. There’s different options so let me go over them.
One machine – Upgrade In-Place
Yes this works, believe it or not, as long as you’re in an OS that supports 2005 and 2014, i.e Windows Server 2008.
Pros: straightforward, start the installer, hope for the best and you’re done.
Cons: hard to rollback if for any reason the upgrade screws up the SQL install, downtime happens during the upgrade process. You don’t get a chance to upgrade OS, hardware, VM, etc.
One machine – Two instances side by side
Pros: straightforward, easier rollback than in-place, you can do zero or near-zero downtime cutover.
Cons: you don’t get a chance to upgrade OS, hardware, VM, etc.
Two machines – new install upgrade
Pros: rollback is simple (go back to the original machine), you can do zero or near-zero cutover, you can pick whatever new machine to install the new 2014 instance. Upgrade bare metal or create a new VM or create a new VM in the cloud, you pick!
Cons: more work to setup the new environment.
Upgrade straight to Database as a Service (Azure SQL Db)
Pros: rollback is simple (go back to the original machine), you can do zero or near-zero cutover (replication works now to Azure SQL Db). You get the scalability, easy DR, plus all the new features coming Cloud First.
Cons: you need to test and make sure your application will run fine on Azure SQL Db, it’s not exactly the same as the full in-the-box SQL Server.
I’ve also made a couple of video demos to cover upgrading from 2005 to 2014 and benefits of Columnstore and compression:
And upgrading from 2005 into Azure SQL Database and setting up DR in a couple of clicks:
I hope I’ve convinced you now on the WHY and the HOW to get out of your SQL 2005 black hole! If you’re looking for official resources, Microsoft has put together a handy page that covers the situation RIGHT HERE.