August 13, 2013 at 6:20 am
Hi ,
Im looking for suggestions that would enable me to create a TEST environment.
AT this point in time in my PRODUCTION system , I have stored procedures that use Linked Servers to point to the Production Environment.
I am looking for ways to easily create a test environemnt from Prod.
At this point in time I have 22 stored procedures that have hardcoded the database name, so when I want to create a TEST environment, I would manually need to change these 22 occurrences.
I can think of using dynamic SQL to overcome this problem, however any other suggestions would be appreciated
Thanks
August 13, 2013 at 6:52 am
Script out all the stored procedures to .sql files, and then use a decent text editor to replace the database names.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 7:20 am
Gerard Silveira (8/13/2013)
Hi ,Im looking for suggestions that would enable me to create a TEST environment.
AT this point in time in my PRODUCTION system , I have stored procedures that use Linked Servers to point to the Production Environment.
I am looking for ways to easily create a test environemnt from Prod.
At this point in time I have 22 stored procedures that have hardcoded the database name, so when I want to create a TEST environment, I would manually need to change these 22 occurrences.
I can think of using dynamic SQL to overcome this problem, however any other suggestions would be appreciated
Thanks
It seems like something is missing here. How can you test code and then change it before you go live? When you say the database is hardcoded in these procs I am guessing you have multiple databases so you need to use 3 part naming? By far the easiest way to deal with this is to put your test system on a different server. That way you have an exact copy of production. You can create your procs, test them and move them to prod with NO changes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 8:28 am
Hey Sean ,
The issue is I have a 4 part naming here, so Im looking for a method to move from PROD - TEST and TEST - PROD without making changes within the stored proc. To clarify matters my stored proc on SERVER1 needs to access a database on SERVER2, This is currently achieved via Linked Servers
The idea I was toying with was to introduce an IF within the stored proc,
So If Im connected to my production instance, I would use dynamic SQL to connect to the Production servers and If Im in my test instance
I would use dynamic SQL to connect to my test servers
So that way it would be relatively easy to move between environments, without significant changes
I would still need to test that theory out, I was wondering if there was a better way to do that
Thanks for your input
August 13, 2013 at 8:52 am
Gerard Silveira (8/13/2013)
Hey Sean ,The issue is I have a 4 part naming here, so Im looking for a method to move from PROD - TEST and TEST - PROD without making changes within the stored proc. To clarify matters my stored proc on SERVER1 needs to access a database on SERVER2, This is currently achieved via Linked Servers
The idea I was toying with was to introduce an IF within the stored proc,
So If Im connected to my production instance, I would use dynamic SQL to connect to the Production servers and If Im in my test instance
I would use dynamic SQL to connect to my test servers
So that way it would be relatively easy to move between environments, without significant changes
I would still need to test that theory out, I was wondering if there was a better way to do that
Thanks for your input
4 part naming shouldn't be an issue. On your test box create a linked server with the same name and have it point to your test instance of the "other" server. If you have one set of code running in test and another set of code for production your tests are not valid because the same code isn't being executed.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 13, 2013 at 3:54 pm
Gerard Silveira (8/13/2013)
Hey Sean ,The issue is I have a 4 part naming here, so Im looking for a method to move from PROD - TEST and TEST - PROD without making changes within the stored proc. To clarify matters my stored proc on SERVER1 needs to access a database on SERVER2, This is currently achieved via Linked Servers
The idea I was toying with was to introduce an IF within the stored proc,
So If Im connected to my production instance, I would use dynamic SQL to connect to the Production servers and If Im in my test instance
I would use dynamic SQL to connect to my test servers
So that way it would be relatively easy to move between environments, without significant changes
I would still need to test that theory out, I was wondering if there was a better way to do that
Thanks for your input
My recommenation would be to (almost) never use anything more than 2 part naming. Then, use Synonyms on each server to point to the correct place. That way, you don't need to change even a lick of code when promoting from one environment to another.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply