September 30, 2008 at 7:11 am
I am sure this has been covered somewhere and I just can't find it. I've seen articles about best practices for setting up test, development and production environments and I wish I could find them again as well.
I would like to find the best way to minimize the room for error when moving things between servers. Currently, most of our apps use databases only on one server. With the addition of the new SQL 2005 box we are going to have applications that need to have their database on that machine and still access the database on the other machine until it's brought over.
I realize that linked servers is probably the best way to go but I wanted to open that up for discussion and see what everyone does.
An example would be the following:
Currently ApplicationServer1 might only access DatabaseServer1.
In the near future, ApplicationServer1 might need to access DatabaseServer1 and DatabaseServer2.
When you throw development into the mix you add the following:
ApplicationDevServer1 accesses DatabaseDevServer1.
In the near future, ApplicationDevServer1 needs to access DatabaseDevServer1 and DatabaseDevServer2.
How do you minimize the locations that you change the server name, whether that be in the stored procedures or elsewhere? I hesitate to force the developers to use dynamic SQL populated from a table as well.
I hope all that made a little sense.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
September 30, 2008 at 7:23 am
I'd use linked servers, ensure that the linked server name remains the same and change where it points when necessary.
Bear in mind though, that if you use linked servers to get at a DB on the same server, you'll be incurring a fairly high performance penalty over just running a cross DB query.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2008 at 7:32 am
So how would you work that in practice?
Do you setup a standard where you say the linked server name is the same in dev and prod?
And what about when those databases are finally brought over to the 2005 box. Then you have all the stored procedures that would have to be changed.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
September 30, 2008 at 7:44 am
Jason Crider (9/30/2008)
So how would you work that in practice?Do you setup a standard where you say the linked server name is the same in dev and prod?
Just so. I prefer that my dev environment and prod environment look as similar as possible.
And what about when those databases are finally brought over to the 2005 box. Then you have all the stored procedures that would have to be changed.
Yes. It's work, but it's likely that there's other stuff that will have to be changed for 2005 as well. I'd make it part of the upgrade testing and modification. You're going to have to change them anyway, now or later. Do it now and you'll be hindering the performance until you do the upgrade and split.
What I would probably do, if I was designing such an app from scratch, is to ensure that as far as possible, the front end was capable of picking which DB to connect to (and initially the connection strings for the two would point to the same server) and then I would try to keep to a minimum the stored procedures that had to talk to both DBs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2008 at 7:51 am
Just so. I prefer that my dev environment and prod environment look as similar as possible.
I wish that was more the case but it's not. I've tried expressing the importance of that but sometimes it's a difficult proposition.
Currently, all apps go have to audit and authenticate through a certain database(s). That will be one of the last ones we move to SQL 2005. Unfortunately, most of this won't be new apps from scratch but existing apps. They could have over 50 stored procs who make no reference other than possibly dbo.table. They are assuming they are already in the database they need.
There are plenty of headaches, but I'm trying to plan up front and get this as straightened out as I can so being as dynamic as possible is the goal.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
October 1, 2008 at 2:29 pm
would synonyms work here?
I see some negative press on here about it but maybe it's an option.
MCITP, Database Administrator
A hodgepodge of Information Technology and Life
LinkedIn Profile
My Twitter
October 2, 2008 at 5:47 am
They might. I've never used them.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 3, 2008 at 10:28 am
I've used synonyms for linked servers with great success. I'd recommend that.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply