SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Development and Production Database


Development and Production Database

Author
Message
kurin123
kurin123
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 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
ChiragNS
ChiragNS
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8177 Visits: 1865
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"
kurin123
kurin123
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 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.
ChiragNS
ChiragNS
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8177 Visits: 1865
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"
grapefruitmoon
grapefruitmoon
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1782 Visits: 1112
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
kurin123
kurin123
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 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.
noeld
noeld
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28216 Visits: 2051
I think you want to probably capture an SQL "trace" and then replay it on the "development" database at will.


* Noel
ChiragNS
ChiragNS
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8177 Visits: 1865
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"
kurin123
kurin123
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 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.
WILLIAM MITCHELL
WILLIAM MITCHELL
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4590 Visits: 3096
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search