Best practice for moving db to new server?

[jsteph]

Hi all,
I've tried to read up on 'best practices' for this sort of thing, but it seems like MS and others write a massive tome on this to cover their behinds. And I don't blame them. There should be a zenith point for which to strive, but this is the real world, and we're a mid-size company with an extremely small IT staff and--no surprise--tiny budget.

So we have our main sql-server, with many db's used throughout the company. We also have smaller sql-server db which has been used for years for another application, and it is so firmly embedded into probably hundreds of Access front-ends with ODBC DSN's pointing to the smaller server.

Here's the problem--that smaller server is going to stay put with other db's still running on it, but one of the db's on it must be moved to our main server. So it seems we can't just add a DNS entry to redirect connections from that server to the new one because it would break all the other apps that will and must stay on the smaller server.

So, is there a way at a lower level than our DNS server to redirect, ie, at the sql-server db-level? For instance, can an ODBC connection that points to Database1 in server B (the smaller one) be redirected to Database1 now existing in Server A (the larger one)? All while maintaining any ODBC connections to, say Database3 on Server B? And without a huge performance penalty?

I know ODBC changes be done with a .reg file but it's still a lot of machines. Are we stuck with having change the ODBC connections, or is there a sql-server level way to redirect just one db?

Thanks,
--Jim

 

[mrdenny]

In short no. You'll need to edit all the applications which connect to the database and modify there connection strings.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[jsteph]

Thanks, I guess it's time to do a mass remote reg hack...
--Jim