Sql-server – Multiple versions of SQL Server on the same development machine

backuprestoresql server

I am consolidating some SQL Servers to simplify administration. I am thinking about a new VM with SQL Server 2005/2008R2/2012 each as a separate named instance on the same machine. This machine will only be used in a 'sandbox' environment primarily to test restores of our backups. No applications will be hitting this.

Am I likely to run into any issues with multiple versions? I remember running into issues years ago with SQL 2000/2005 on the same box. Do you folks use separate machines for restore testing?

Best Answer

No, shouldn't be any issues; I have a VM right now with 2000, 2005, 2008, 2008 R2, 2012 and 2014 and it works just fine.

I do suggest to observe the following:

  • install the oldest version first, and get it completely patched before moving on to the next.
  • use a very consistent and intuitive naming scheme for each instance (e.g. .\SQL2000, .\SQL2005, etc). Also don't install a default instance (because it can't self-document what version it is, its purpose, etc).
  • don't install multiple versions of the client tools, except the case where you need to manage SSIS packages in multiple versions. For just about everything else, you can manage all down-level versions with the 2012 client tools. If you plan to use Visual Studio (any version), don't install it in the VM, use it in the host. Different versions of Visual Studio don't always play well with different versions of SQL Server (particularly client tools).
  • make sure to not allow the default startup behavior, where all instances will start automatically (you probably only want a specific version to be running and using resources at any given time).
  • use the same sa password, SQL Server service account, SQL Agent account etc. on all instances, and make sure you add your own account as an admin on all instances. The last thing you want is to get locked out of one instance because it has a different password or to suffer some issue that you can't tell is account-related or version-related.
  • don't create databases on version x and expect to backup/restore them on x-n. Backups are not backward compatible (regardless of compatibility level), only forward.
  • be mindful of recovery models, log auto-growth settings, and large databases. For example you don't want to install the AdventureWorks DW database on every single instance - this takes up valuable space and you will soon consume your entire drive. Keep those databases handy (like on an external drive) but only attach/restore them when you need them on a specific version.
Related Question