November 9, 2009 at 4:10 pm
Hi
My stored procedures and sql jobs for a certain application come from two databases 1 and 2. Now, I hear that one of these databases will be moved to a separate instance. I am wondering what change I need to do in my SPs and jobs to access the other database.Though I googled, since my knowledge of instances is limited, am unable to get an exact answer. Any help would be appreciated. Thanks
November 9, 2009 at 4:43 pm
Hi,
If you have code that references two databases on single instance, moving one of them to another instance will likely cause troubles. You will have to look at linked servers and rewrite code to use linked servers in places where one of the databases is referenced. There may be issues related to distributed transactions, depending on what your code is doing. You will probably have to configure MSDTC.
Moving a database to different instance is a serious decision - perhaps you will have to redesign your application or part of it.
I am curious, what is the reason of moving the database to a different instance?
Regards
Piotr
...and your only reply is slàinte mhath
November 9, 2009 at 4:48 pm
Let's Assume this, Now you have Instance#1 and in that both DB#1 and DB#2 coexist and are fine as of now.
And as mentioned by you, one of the DB's (DB#2) is going to be moved to Instance#2.
Couple of questions I have, are the Jobs going to be moved to Instance#2? If so what kind of jobs and how many. and the Instance#2 is going to be on the same server or a new Server.
You can script the Jobs and move them to the Instance#2 and either Disable or delete on the Instance#1.
Regarding SProcs, you need to change them with 4 Part Notation and use LinkedServer.DatabaseName.SchemaName.ObjectName. (you need to create Linked Servers between them and while creating the Linked Servers, you need to ensure RPC IN and RPC out is enabled and many more options related to security between the Instances etc.)
And Finally, Test ,Test and Test to ensure they are working (Sprocs and Jobs)
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 9, 2009 at 4:54 pm
You are going to have to look at setting up and using a linked server to access the database on another instance. What I would recommend you look at is Synonyms. If you setup the synonyms now, so that the synonyms access the objects in DB#2 - when the database is moved you only have to drop and recreate the synonyms.
You could then modify all of your procedures to use the synonyms. Then, you don't have any code changes to make when the other database is moved. Here is an example:
CREATE SYNONYM dbo.MyObject FOR db2.dbo.MyObject;
Use it in your procedure as:
SELECT ... FROM dbo.MyObject;
Now, modify the synonym to:
DROP SYNONYM dbo.MyObject;
CREATE SYNONYM dbo.MyObject FOR linkedserver.db2.dbo.MyObject;
No changes to the procedure - because you are still access the same synonym.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 9, 2009 at 5:07 pm
Thanks guys, for the prompt response. Here are the answers to Bru's questions:
- No, the jobs will not move to #instance2. We do not know how many jobs are affected..yet to do the impact analyses..however it could be around 5.
- I don't think we'll delete the jobs from #instance1
- will have to read about RPC IN and RPC out
Thank you!
November 9, 2009 at 5:23 pm
Friends,
From your responses, I understand two things:
- Linked servers is the widely used technique for accessing DBs from another instance
- It's not as easy as just prefixing the object's name with the linked server.Dbs.instance.object name. It involves other complications.
I am trying to understand what are those other / security related complications. Is there any link that has info on what steps need to be taken when a DB moves to another instance ? If you can point me to the right direction, it would be awesome. thanks guys.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply