Copying DDL and DML changes 2008 R2

[wbodger]

I have a production and a test server and an application in production where changes are made. I need to mirror those changes onto our test server. We had tried going the replication route, but it proved to be unreliable. I then set-up an SSIS package to copy the data over, but if the schema changes at all (new field or, more likely, a modified field length), then the package fails, as it did last night. Does anybody have a good solution for this kind of situation?

wb

 

[wbodger]

Perhaps a bit lengthier explanation would be in order. The application in production is used to create forms definitions for study protocols, which can be a lengthy process, so we would rather not try to do the same process on two different systems, but we have to have everything on the test system (I know, seems quite backward that they set it up so you make changes in production and then copy it over to the test environment to test it), in order to do complete testing. This db is pretty well set, but we are still coming upon some DDL changes that need to be made (like increasing a field from 2000 to 4000 characters yesterday), which causes the simple SSIS package to fail because it is just copying data changes over. So, what solution is there that works reliably to keep both DDL and DML changes synced between two servers for a specific db?

Thanks!
wb

 

[SQLBill]

We have a rollout process where any changes we make to a production server are also made to the test server, but we do the work ourselves...not through an application. I'm not sure how your application works, but you should see if it can apply the changes to both servers or alert you that a change has been made and what the change is, then you can put the change on the test server.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[philhege]

I'd focus on the DDL, then the data copy should follow seamlessly.
Question is, how do you ensure that DDL updates are synched?

Here's some blue-sky stuff:

SQL Audit does a really good job of tracking access, DDL, and DML. Were you to capture the DDL changes from an audit specification, and roll those into a script that you can apply to the test instance, you might have a solution.

Here's a snapshot from my Audit table. Notice the entry for an ALTER action:

action_desc	succeeded	target_object	type_desc	statement
SELECT	1	AIGRET.CFMData.dbo.Athfile	NULL	select top  1000 * from vw_Athfile
SELECT	1	AIGRET.CFMData.dbo.vw_Athfile	NULL	select top  1000 * from vw_Athfile
CREATE	1	AIGRET.CFMData.dbo.vw_Athfile	NULL	  CREATE VIEW [dbo].[vw_Athfile]  AS  SELECT     dbo.Athfile.*  FROM         dbo.Athfile  UNION ALL  select top 0 dbo.Athfile.*  FROM dbo.Athfile  where 1=2    
[COLOR=#EF2929]ALTER	1	AIGRET.CFMData..dbo	NULL	  CREATE VIEW [dbo].[vw_Athfile]  AS  SELECT     dbo.Athfile.*[/color]  FROM         dbo.Athfile  UNION ALL  select top 0 dbo.Athfile.*  FROM dbo.Athfile  where 1=2    
INSERT	0	AIGRET.CFMData.dbo.Athfile	NULL	INSERT INTO Athfile (OriginOfLogRecord)  VALUES ('BLAAH')
SELECT	1	AIGRET.CFMData.dbo.Athfile	NULL	SELECT top 10 * from Athfile  
DROP	0	AIGRET.CFMData.dbo.Athfile	NULL	DROP TABLE Athfile  
INSERT	0	AIGRET.CFMData.dbo.Athfile	NULL	INSERT INTO Athfile (OriginOfLogRecord)  VALUES ('BLAAH')
DROP	0	AIGRET.CFMData.dbo.Athfile	NULL	DROP TABLE Athfile
SELECT	1	AIGRET.CFMData.dbo.Athfile	NULL	SELECT top 10 * from Athfile
LOGIN FAILED	0	AIGRET	NULL	Login failed for user 'IReallyDontKnow'. Reason: Could not find a login matching the name provided. [CLIENT: 10.20.25.42]
LOGIN FAILED	0	AIGRET	NULL	Login failed for user 'IDontKnow'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
INSERT	0	AIGRET.CFMData.dbo.Athfile	NULL	INSERT INTO Athfile (OriginOfLogRecord)  VALUES ('BLAAH')
SELECT	1	AIGRET.CFMData.dbo.Athfile	NULL	SELECT top 10 * from Athfile
DROP	0	AIGRET.CFMData.dbo.Athfile	NULL	DROP TABLE Athfile
DROP	0	AIGRET.CFMData.dbo.Athfile	NULL	DROP TABLE Athfile
INSERT	0	AIGRET.CFMData.dbo.Athfile	NULL	INSERT INTO Athfile (OriginOfLogRecord)  VALUES ('BLAAH')
SELECT	1	AIGRET.CFMData.dbo.Athfile	NULL	SELECT top 10 * from Athfile
AUDIT SESSION CHANGED	1	AIGRET	NULL	
AUDIT SESSION CHANGED	1	AIGRET	NULL

A simple procedure that cherry-picks the DDL statements and sends them off to your test server could be a solution.

-----------
With business clients like mine, you'd be better off herding cats.

 

[philhege]

<OPINION>
I'm glad I don't support this app.
If I made changes to a production application without first applying the changes in a test environment and THOROUGHLY testing said changes, that would trigger a resume' generating event (RGE).
</OPINION>

-----------
With business clients like mine, you'd be better off herding cats.

 

[SQLBill]

Philhege,

It all depends on how they are using the test server. I worked for a company that did what the OP might be doing...however, our process (and maybe wbodger's) was like this:

Development server - developers have full control to develop their databases, processes
Test server - locked down like production so new code can be fully tested before promoting to prod
Prod server - locked down...developers have very little access
Test Prod server - copy of prod but used to troubleshoot production issues.

We used the Test prod server for troubleshooting any production issues that came up since the Test server usually already had new code promoted to it that was being tested and no longer matched production. Having the Test Prod server matching Prod let us quickly troubleshoot issues without having to copy the database(s) or processes back down to the Test server. We could run things like Profiler on the Test Prod server and not worry about badly impacting the Prod server itself.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[philhege]

Yeah, makes sense. Our environments are dev, test, cert, (stage - for Web apps), DR, and prod.

It's a one-way promotional path. If we don't have production code available in cert (and of course we can copy data backups from prod) then someone goofed. The only thing that passes in the other direction is data from prod to troubleshoot with.
IOW our production code base is available in a non-production environment for test/etc. If something's running in prod that didn't pass through the other environments, that's a Bozo no-no.

-----------
With business clients like mine, you'd be better off herding cats.

 

[wbodger]

Gentlemen, I completely agree with you and it drives me crazy, but for now it is what I have inherited... It truly is Dev, Test and Prod and one application makes changes directly to production databases. I think they are finally starting to see some of the problems with that, but we will see...

wb