Redirect requests to new Database in same instance

Question

Post reply

Redirect requests to new Database in same instance

GonnaCatchIT

SSCrazy

Points: 2797
More actions
May 20, 2016 at 2:30 am

#400188

Hello Team,
I have a sql server 2008 r2 where our DB - say Account is hosted. The DB size is abt 500 gb , so our process is to create a compact DB by deleting the records from the DB.
Now we have another DB - Account_Compact which holds the smaller version of Account DB.
Can we have a setting in SQL server to redirect all the requests on 'Account' Db to be executed on 'Account_Compact' DB ?
Because if we start changing App level connections, it becomes hectic and we have to keep switching frequently.
Please let me know redirection is possible in SQL Server 2008 r2.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Care to explain why you are doing what you are doing?

Lowell SSC Guru Points: 323518 More actions · Answer 2

why can't you rename the databases?

exec sp_renamedb 'Account ','AccountOrig'

exec sp_renamedb 'Account_Compact ','Account'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

GonnaCatchIT SSCrazy Points: 2797 More actions · Answer 3

I know that option, but want to know if SQL Server supports redirection to different database.

Issue is we dont have control over the SQL servers and there are lot of internal restrictions in renaming the DB's.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 4

GonnaCatchIT (5/28/2016)
I know that option, but want to know if SQL Server supports redirection to different database.
Issue is we dont have control over the SQL servers and there are lot of internal restrictions in renaming the DB's.

What kind of "restrictions"?

Shifting gears and I don't mean this in an unkind fashion, it doesn't sound like this was a well thought out evolution. It sounds like some good manual effort was put into removing legacy data but there was no plan as to what to do with the legacy data nor any plan to prevent additional unwanted legacy data from become a problem in the future.

It also sounds like this evolution wasn't actually tested.

Whether or not you find some way other than renaming the database to accomplish your current goal, I strongly recommend that you stop what you (the team at your company) are doing and devise a plan for the automation of archiving legacy data in a separate database or databases. Since you already have code that stripped out the legacy data, a couple of simple tweaks to the code should be all that is necessary to move data out of your main database to other databases. If you do it correctly (for example, one database per month), you can even take a final monthly backup and set the databases to READ_ONLY so that you no longer have to back them up, maintain indexes, etc, etc. It would also allow you to easily drop entire months (just drop the database) when the individual months finally reach the end-of-life.

And, no... if you have the Enterprise Edition and the subject of Partitioned Tables comes up, I do NOT recommend using partitioned tables for legacy data storage because you cannot restore just a month or two for DR or refreshing of data on Dev and Test systems (I just went through that ugly truth with a 500GB table we're forced to keep all the data "forever" by regulations). Partitioned Views across the monthly databases would be the way to go.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Smendle Hall of Fame Points: 3969 More actions · Answer 5

I agree with Jeff a partitioned view with your "table lite" criterion is the way to go.