Redirect DB requests?

Question

Redirect DB requests?

C# Gnu

SSCertifiable

Points: 5973
More actions
January 18, 2012 at 6:20 am

#263039

Is it possible to redirect database requests from one database to a database on another server?
For example :
Server A currently holds database named "myDatabase"
rename/delete the database then redirect all SQL queries(edit: for this database only) to :
Server B new database with same name "myDatabase"
I want to migrate databases to a new server (2005 -> 2008) one database at a time (ideally).
I haven’t seen any way to redirect database requests; but I am open to the best practice suggestion for achieving this migration.
If it is not possible to redirect one at a time I suppose I will have to commit to moving all the databases in one swoop and then renaming the instance to that of the old instance, but that seems rather old school?
Please can you advice.
Many thanks
C# Screw
C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Most applications (web or thick) have a connection string stored somewhere in them that points them to the correct server and database, and usually includes something about the credentials to use too. That's the usual place to handle that kind of thing.

Otherwise, I think you're probably looking into some sort of load ballancing solution. Not something I've worked with, but in theory it should be possible.

Usually, I just get an admin to change a shared connection on a web server, and am done with it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

C# Gnu SSCertifiable Points: 5973 More actions · Answer 2

I hoped to avoid having to change all the connection strings from the many applications that connect to the database.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

There's no quick fix for this unfortunately. You'll have to edit the connection strings no matter what.

You may want to get ahead of the game and create a DNS entry for each database and update your connections strings now for each one. That way as you move the database you just have to update DNS to point to the new server.

Shamless self promotion - read my blog http://sirsql.net

savethytrees SSCommitted Points: 1968 More actions · Answer 4

It is difficult to move one database at a time and redirect the requests, without having to change the connection strings in the application code.

But if you moved all databases at once, then you can create a DNS mapping to redirect all requests to the current hostname to the new IP Address. This is assuming that the connection strings use hostnames instead of IP addresses.

If you talk to the network admins they should be to change the mapping between a given hostname and it's IP Address.

http://saveadba.blogspot.com/

Blog
http://saveadba.blogspot.com/

C# Gnu SSCertifiable Points: 5973 More actions · Answer 5

Nicholas Cain (1/18/2012)
You may want to get ahead of the game and create a DNS entry for each database and update your connections strings now for each one. That way as you move the database you just have to update DNS to point to the new server.

Hi

some ideas here I am not sure about, could you explain 'create a DNS entry for each database and update your connections strings now for each one. '.

This sounds very interesting -could I ask if you could give an example maybe how this would work for each database.

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 6

So let's say you have DatabaseB running on ServerA.

Create a DNS entry called DatabaseB.db.yourdomain. Repoint your applications so that they use that DNS entry.

When you move the database to ServerC change the DNS so that it points to ServerC. That way you aren't changing the connection string again.

Create a DNS entry for each one of the database. This allows for a more seamless migration path (this is something that I actively do now).

Shamless self promotion - read my blog http://sirsql.net

C# Gnu SSCertifiable Points: 5973 More actions · Answer 7

Nic

I may call you that now as you are now my friend!

The pennny dropped !

That is the most brilliant idea - so having a DNS Alias for each database - of course that is excellent.

So I will have :

DB_Database1.MyFirm.co.uk pointing to some server

DB_Database2.MyFirm.co.uk pointing to some server

DB_Database3.MyFirm.co.uk pointing to some server

Then I modify all applications (or get the developers to do it) so the connection strings point to DB_Database1.MyFirm.co.uk or whatever.

Then when thats all done I just change the DNS when I have the database all ok on the other server.

Thats got to be the best aproach for disaster recovery too right?

Of course the DB name has to stay the same - but still thats ok for me.

Thanks A LOT!

#Screw

C# Gnu
____________________________________________________

Excel Database Tasks : efficient business data processing
Version 3.3 released February 2015

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 8

Actually, the DB name can change, but you would have to update your connection string to support that.

The only thing I'd recommend is ensuring that your server listen on 1433.

btw nice Roobarb avatar

Shamless self promotion - read my blog http://sirsql.net