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 12»»

Development and Production Database Expand / Collapse
Author
Message
Posted Friday, November 28, 2008 1:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:41 AM
Points: 4, Visits: 34
Hi,

I have 2 databases - one Development and one Production.
About 50 applications insert data into Production DB. I would like to test my Development DB and insert the data that go into Production into Development DB as well.

What is the best way how to do it?

Thanks
kurin
Post #610149
Posted Friday, November 28, 2008 2:16 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Are both the databases identical in every way - table structures , constraints etc
How much data are you looking at?

I assume you would want to port the data from production to development. You could use SSIS or BulkImport.



"Keep Trying"
Post #610160
Posted Friday, November 28, 2008 5:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:41 AM
Points: 4, Visits: 34
The structure is identical. What I want to test are new triggers and scheduled tasks.

Data are inserted into Production DB every minute from different applications. For each insert trigger is fired. When new row is inserted into Production DB I want to insert the same row into Development DB as well and that way test new triggers and performance.
Post #610287
Posted Monday, December 1, 2008 12:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Hi

Why do you want to insert the production data in to development. ? And what are the triggers for? Do they have any purpose other than inserting data into devlopment db.

Is it possible for you take a backup of the production db and restore in development. These triggers will slow down your production system.


"Keep Trying"
Post #611059
Posted Monday, December 1, 2008 5:05 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 9:15 AM
Points: 944, Visits: 1,046
This does not sound like a good idea, as the other posters say, you should import the data into development from your production system using SSIS or a bulk method.

But to answer the question better, we need a bit of background information;
What are the triggers you want to test? What do they do?
Why do you want to get the data into development?


-----------------------------------------------
SQL Server posts at my blog, Grapefruitmoon.Net
Post #611193
Posted Wednesday, December 3, 2008 1:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:41 AM
Points: 4, Visits: 34
Background: From few manufacturing lines i'm getting data every minute. There are few triggers those fire on insert and they do some calculation and based on them they insert data into new table, send email etc.

I know it's not the best way to do it. But its the way it has been done... I want to change it to work better, remove most of the triggers and replace them with sheduled tasks and so on.

Because this change is quite big, so I would like to test it for a while. The best way for me is to insert real production data (as they are comming from the manufacturing line) into my development DB and see how it behaves and if the performance is better or not.
Post #613139
Posted Wednesday, December 3, 2008 2:33 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, November 21, 2014 6:34 AM
Points: 6,259, Visits: 2,031
I think you want to probably capture an SQL "trace" and then replay it on the "development" database at will.


* Noel
Post #613202
Posted Wednesday, December 3, 2008 11:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Do you mean to say insert real time production data into development db. Then how will you put the data back to production?

What i would suggest is

Create a development db as close to production db.
Simulate the same kind of activity that happens on production in your development environment.
Make the changes and test it properly
If test goes fine deploy to production.


"Keep Trying"
Post #613426
Posted Thursday, December 4, 2008 6:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 6:41 AM
Points: 4, Visits: 34
noeld (12/3/2008)
I think you want to probably capture an SQL "trace" and then replay it on the "development" database at will.


THX man!! That wont be easy to automaticaly process the trace script, but it is worth a try.
Post #613629
Posted Friday, December 5, 2008 5:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 1,516, Visits: 2,721
Here's what you might do.

Take a backup from production and restore it as a TEST database. Then run Profiler on the production db and save the trace to a new table in the TEST db. There are 100's of events that you can trace, but you'll probably be most interested in the T-SQL events like Statement start / end, Batch start / end and so on.

Then, you can go into your TEST db and review the trace table. You can copy the SQL statements from the trace table and execute them one-by-one in your TEST db.

Profiler will have some impact on the performance of the production db so you'll probably want to run it only for brief periods, but during times of fairly high activity so you can get a complete picture of the workload.
Post #614393
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse