Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to maintain test environment Expand / Collapse
Author
Message
Posted Wednesday, December 4, 2013 2:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
Hi all,
I was asked to create a test environment for one of our apps that is supported by 3 databases.
I can restore latest backup of production version of each, but then I will have to edit every procedure to make sure that joins point to test instance of the database.
I can create database script and edit it in text editor.
I am looking for an optimal solution as number of stored procedures is growing and manual edit will become problematic with time.

Thanks,
Post #1519782
Posted Thursday, December 5, 2013 6:12 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 4,130, Visits: 3,157
rightontarget (12/4/2013)
Hi all,
I was asked to create a test environment for one of our apps that is supported by 3 databases.
I can restore latest backup of production version of each, but then I will have to edit every procedure to make sure that joins point to test instance of the database.
I can create database script and edit it in text editor.
I am looking for an optimal solution as number of stored procedures is growing and manual edit will become problematic with time.

Thanks,

I might be missing something here. You say you have to create a test environment with 3 databases, but then mention editing the instance referenced. Are you specifying the instance in your procedures? If not, then you shouldn't have to edit anything because the procedures will run in their own databases within their own instance. I prefer using the two-part naming convention (schema.object) and the three-part convention (db.schema.object) only where necessary. I would not want to rely on manually editing the procedures since you're going to miss something eventually, especially as the amount of code grows.

As for how to copy the databases from production to test, I use the approach of backing up production, copying the backup files to the test server, then restoring the databases from the backup files into test. The only caveat here is that if you're using a different server then you'll have to check your SQL logins to make sure the SIDs match. If they don't match, you have to rebuild them because the SQL logins won't have the permissions they need. This doesn't apply to Windows logins unless you move from one domain to another.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1520002
Posted Thursday, December 5, 2013 6:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 12,903, Visits: 31,967
rightontarget (12/4/2013)
but then I will have to edit every procedure to make sure that joins point to test instance of the database.



so your procedures are using three part naming conventions to explicitly identify the database? is that the problem? ie SELECT * from Production.dbo.Table 1 Inner Join Production.dbo.table2?

or you also have cross database queries, that join, say the Production database and CommonSecurity database?

i could see how that would be an issue; i'd end up putting something together that did a find-replace-alter on all the stored procs in that case, and make it something reusable.

I have a sample fo that somewhere, using a cursor, if you thought that might help, but it assumes certain conventions, like the definitiosn are "CREATE PROCEDURE" with a single space between them, which you might need to change depending on your style of coding procs/functions


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520011
Posted Thursday, December 5, 2013 6:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 3:01 AM
Points: 69, Visits: 487
rightontarget (12/4/2013)
Hi all,
I was asked to create a test environment for one of our apps that is supported by 3 databases.
I can restore latest backup of production version of each, but then I will have to edit every procedure to make sure that joins point to test instance of the database.
I can create database script and edit it in text editor.
I am looking for an optimal solution as number of stored procedures is growing and manual edit will become problematic with time.

Thanks,


You may have to clarify how you are referencing your objects.
If you are referencing the Server name in your object naming, is there any particular reason why?
Does "test instance" mean the Server or Database here?
Is your test environment on its own instance of sql server?
Either way, wouldn't the idea of changing code for this be unwieldy and prone to error?
Post #1520035
Posted Thursday, December 5, 2013 10:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
Thanks all for replies. Based on our replies I get the idea that I did not explain what I am facing correctly.
Let's say I have a server A on which I have database customers and database orders. Some of my queries will have joins between the two.
I need to create a copy of each database on the same server and call them customers_test and orders_test.
If I simply restore production backup into new databases, I will end up with queries that join prod databases. I need them to join '_test' databases.
I guess the problem here is that I am dealing with a copy of database (named differently) on the same server.
As I see, the only way is to edit every procedures to replace.

select ....
from customers.table a
join orders.table b on a.customer_id = b.customer_id

with

select ....
from customers_test.table a
join orders_test.table b on a.customer_id = b.customer_id

Am I on right path? How would you do it?
Hope it makes more sense now.
Thanks,



Post #1520245
Posted Thursday, December 5, 2013 11:49 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 4:04 AM
Points: 12,903, Visits: 31,967
here's an adapted example of some code i use to check for invalid objects; this will recompile your procs/functions/views to allow you to point to a different database.

at the end, anything that didn't compile correctly(invalid referneces, drop tables/columns, etc) will appear .



DECLARE @BadObjects TABLE (ALLINVALIDOBJECTS nvarchar(4000))
DECLARE @objname NVARCHAR(4000),
@cmd NVARCHAR(max),
@OldDatabase1 varchar(128),
@NewDatabase1 varchar(128),
@OldDatabase2 varchar(128),
@NewDatabase2 varchar(128)

SET @OldDatabase1 = 'PRODUCTION'
SET @NewDatabase1 = 'TESTPROD'
SET @OldDatabase2 = 'CommonSecurity'
SET @NewDatabase2 = 'TESTSecurity'

--#################################################################################################
--Views
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('VIEW')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE VIEW'),convert(varchar(max),N'ALTER VIEW'))
--modify three part naming conventions to point to the test database
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase1 +'.'),convert(varchar(max),@NewDatabase1 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase2 +'.'),convert(varchar(max),@NewDatabase2 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase1 + '].'),convert(varchar(max),N'[' + @NewDatabase1 + '.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase2 + '].'),convert(varchar(max),N'[' + @NewDatabase2 + '].'))
print @cmd
exec (@cmd)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH

FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
--#################################################################################################
--Procs
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('SQL_STORED_PROCEDURE')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE PROCEDURE'),convert(varchar(max),N'ALTER PROCEDURE'))
--modify three part naming conventions to point to the test database
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase1 +'.'),convert(varchar(max),@NewDatabase1 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase2 +'.'),convert(varchar(max),@NewDatabase2 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase1 + '].'),convert(varchar(max),N'[' + @NewDatabase1 + '.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase2 + '].'),convert(varchar(max),N'[' + @NewDatabase2 + '].'))
print @cmd
exec (@cmd)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH

FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1
--#################################################################################################
--Functions
--#################################################################################################
DECLARE c1 CURSOR FOR
SELECT
QUOTENAME(s.name) + '.' + QUOTENAME(obs.name) ,
mods.definition
FROM sys.objects obs
INNER JOIN sys.sql_modules mods ON obs.object_id = mods.object_id
INNER JOIN sys.schemas s ON obs.schema_id = s.schema_id
WHERE obs.is_ms_shipped = 0
AND obs.type_desc IN('AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')
OPEN c1
FETCH NEXT FROM c1 INTO @objname,@cmd
WHILE @@fetch_status <> -1
BEGIN
BEGIN TRANSACTION
BEGIN TRY
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'CREATE FUNCTION'),convert(varchar(max),N'ALTER FUNCTION'))
--modify three part naming conventions to point to the test database
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase1 +'.'),convert(varchar(max),@NewDatabase1 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),@OldDatabase2 +'.'),convert(varchar(max),@NewDatabase2 +'.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase1 + '].'),convert(varchar(max),N'[' + @NewDatabase1 + '.'))
SET @cmd = REPLACE(@cmd,convert(varchar(max),N'[' + @OldDatabase2 + '].'),convert(varchar(max),N'[' + @NewDatabase2 + '].'))

print @cmd
exec (@cmd)
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
INSERT @BadObjects VALUES (@objname)
END CATCH

FETCH NEXT FROM c1 into @objname,@cmd
END --WHILE
CLOSE c1
DEALLOCATE c1

SELECT * FROM @BadObjects






Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1520268
Posted Thursday, December 5, 2013 12:31 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
Thank you Lowell for sharing your code.
Post #1520294
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse