I currently have 2 SQL Server 2000 database instances (each running on a different physical server), each containing around 12 databases (not all are active). We are planning an upgrade to SQL Server 2008 R2, and are investigating the possibility of condensing these 2 instances into 1 for several reasons (ease of use, fewer client DSNs, cheaper licensing). The two servers are referenced by each other as linked servers (and that link is used a lot), and there are other servers involved which are also linked servers.

I understand I'll need to refactor all the stored procedures and views which reference the linked servers, but what other potential problems should I look out for? What should I check/do to determine if this is a good idea or not?

  • Aside from the architectural problems associated with outside programs (which should be as simple as changing a connection string), what problems do you think you'll encounter? I know you listed several above (refactoring the sprocs and views), but that sounds like the bulk of where your time will be spent. – jcolebrand Nov 28 '11 at 15:47
  • @jcolebrand -I don't know what problems I might encounter because I've never done this before. Hence the question. I was hoping someone may have done this before and can offer some advice based on their experience. – MAW74656 Nov 28 '11 at 16:08
  • Yeah, sorry, I was kinda going in a different direction and kept backtracking. Now that comment makes less sense. – jcolebrand Nov 28 '11 at 17:04

I believe consolidation like you are planning to do is a good thing. Depending on how the linked servers are queried, you may find a huge jump in performance (cross server joins are usually quite horrible when compared to cross database joins on the same server).

The downside is that your entire system is now sitting on one machine. When this machine is down, all your apps are down.

Here is what I suggest you do to prepare:

Run the SQL Server Upgrade Advisor against each database. It will tell you of any incompatible code that may exist (e.g. "*=" join syntax, no longer supported).

Identify DTS packages running against the old systems. DTS is no longer supported. You will have to rewrite them using SSIS.

Make sure the new system is powerful enough to handle the workoad.

|improve this answer|||||
  • 2
    In my case its not any greater danger having all the data on 1 server, because they are so closely coupled anyway, if 1 is down the other is all but crippled. – MAW74656 Nov 28 '11 at 19:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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