Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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.
share|improve this answer
    
Great suggestions! Seeing as how you've got so many versions running successfully, perhaps I'll go ahead and add SQL Server 2014 to my new server as well. Thanks! –  SomeGuy Sep 30 '13 at 17:52
    
Aaron - what version of Windows are you using for that bad boy? –  SomeGuy Sep 30 '13 at 18:03
    
@SomeGuy Windows Server 2008 R2. It was build just before Win8/2012 were released, and I'm not sure if 2000 will install there (or how realistic it is that I'd need it again on any future VMs I build). –  Aaron Bertrand Sep 30 '13 at 18:05
    
@SomeGuy also I wouldn't bother with 2014 just yet, unless you are really interested in testing the few new Enterprise features and aren't bothered by having to completely uninstall the beta within 180 days. –  Aaron Bertrand Sep 30 '13 at 18:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.