I have a client that is having plans to upgrade/migrate the SQL Server instances from 2008 R2 to 2012. I have provided the details as below. The environments are all Virtual running OS version Windows 2008 r2 enterprise SP1 and uses VMWare.
SQL003 – SQL Server 2008 r2 instance on a WSFC. This SQL Server instance is a publisher with local distributor and subscriber to 2 places SQL007 and SQLDR003. It also has some databases which are both in replication and also part of Mirroring to the DR site (SQLDR003).
SQL007 – SQL Server 2008 R2 instance on a WSFC. This SQL Server instance is a publisher for SQLDR007 and SQL008 and is also a subscriber to SQL003 as noted above as well.
SQL008 – SQL Server 2012 SP1 is a standalone SQL Server instance. I am not too concerned about this instance.
I know that in order for me to move forward with upgrading to SQL Server 2016 the 0S needs to be at least Windows 2012 OS OR later. I am thinking down my head how to achieve this. Is it possible if I can directly upgrade the OS to Windows 2012 or 2016 and then patch SQL Server to 2016 but then how about replication and stuff will this break anything etc or do I need to rebuild a cluster completely and then migrate the objects. It would not be an ideal spot to migrate the databases and all the associated objects based on how the environment is configured. I am in need of some methodology that can save me time and with no changes to the existing hostname etc.
Best Answer
It sounds like your plan is to do an in place upgrade of the OS and the SQL Server. You will experience much less pain and have greater chance for rollback in the event things go awry if you do a side by side upgrade.
Build up the new servers and then migrate to them. This keeps the existing servers working and allows you to migrate and test without affecting the users and data.
Keep in mind too, that there could be many changes that could adversely affect the applications supported by this SQL Server. Having the extra servers will provide the ability to test those applications and find out what mitigation tasks you may need to take.
Once you have the tasks and migration process detailed out, you can utilize log shipping from the main server to the new server to facilitate a low downtime migration.
Also consider using Always On Availability Groups instead of clustering as it may provide you a better HA/DR.
From a host name not changing perspective, utilize CNAME DNS entries. This way you can use new servers, and then when ready cut the DNS entry over to the new server name. To the users, they think nothing has changed.