Tell me more ×
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.
up vote 4 down vote favorite
1

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?

share|improve this question
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

1 Answer

up vote 3 down vote

I believe consolidation like you are planning to do is a good thing. Depending on how the linked servers are qeuried, 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.

share|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

 
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.