Development and Production Database

Question

Post reply

Development and Production Database

kurin123

SSC Enthusiast

Points: 180
More actions
November 28, 2008 at 1:54 am

#196652

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

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

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 SSC Enthusiast Points: 180 More actions · Answer 2

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 One Orange Chip Points: 26137 More actions · Answer 3

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 SSCrazy Points: 2226 More actions · Answer 4

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[/url]

kurin123 SSC Enthusiast Points: 180 More actions · Answer 5

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 SSC Guru Points: 96590 More actions · Answer 6

I think you want to probably capture an SQL "trace" and then replay it on the "development" database at will.

* Noel

ChiragNS One Orange Chip Points: 26137 More actions · Answer 7

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 SSC Enthusiast Points: 180 More actions · Answer 8

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 SSChampion Points: 13766 More actions · Answer 9

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.

david.carnley SSC Enthusiast Points: 159 More actions · Answer 10

I think in the long run you might be best off by creating simulators - small apps or scripts that produce transactions similar to production. Over time you could evolve these into a full test suite that could simulate all of the possible types of transactions.

ps. One Orange Chip Points: 29422 More actions · Answer 11

writing triggers on all tables would be little cumbersome and as said earlier would hit production.

You can also think of implementing log shipping. keep log backup interval relatively higher and use this interval to study the data in ur developement database by bringing database in multi_user mode.. Hope u're not planning to enter data into developement simultaneously..

Pradeep Singh

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Periodic "Log Shipping" or on demand "Replication" would do it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

robertm Ten Centuries Points: 1096 More actions · Answer 13

Jeff you beat me to it. I would most certainly look at using Transactional Replication to solve this issue, assuming that's going to be suitable for your environment.

However the idea of capturing trace information is something I hadn't considered before and offers up some nice functionality such as repeatable tests which could be easily added into a dbfit FitNesse test for example.

Gursoy Yerli Valued Member Points: 51 More actions · Answer 14

What we're doing is simple as long as you script your database changes from DEV to PROD. Here how it goes.

1) We backup PROD database every day (week or month. Depends on your dev cycle)

2) We restore PROD database into another database and run clean-up scripts (due to compliance on sensitive data)

3) We backup cleansed database and port backup file(s) to DEV environment

4) We restore cleansed PROD backup on DEV environment and run incremental script to bring PROD "data" to DEV schema.