Sql-server – How to capture workload from production to replay on test

Here is what I would recommend:

1. If you do not already do this, create a regular backup process for your production database. To make life simple, make it a full backup and include all database objects (tables/schema/data/users). The Microsoft website has a lot of information on how to achieve this.

2. When you need to update your development environment, restore the production backup into development, then implement the migration (tables, procedures, data) you need to test using the method that is documented for the production change.

3. Test, knowing that you are not just testing your code changes, but also testing your migration strategy for when you roll out your change to the production environment. You are also testing your database backup and restore (DR) strategy as well.

Note, this may mean you have 2 development databases, one for actual development and one for integration/QA/UAT testing.

Further to my comment this is, putting it as politely as possible, a moderately crazy approach to getting the job done.

• Regardless of how mighty the ORM is, you are inherently changing the behaviour of the system by changing the RDMS.
• That your ORM can talk to two different database systems is a different proposition to the same database (i.e. schema) on two platforms. You can certainly create a RDBMS agnostic application but you'd never release it having tested against only one, would you?
• To accommodate the change of RDBMS, you create a stack of unnecessary ETL work to re-create the database objects, manage the type changes and then finally shift the data around.

Are there potential problems with hosting the testing database on the same server as the production server, assuming that the load on the testing database will be relatively minor?

You could mitigate the inherent risk in mixing development and production with appropriate segregation of security rights but accidents happen, people make mistakes, the wrong button get's clicked. If the server is comfortably spec'd and you are 100% convinced your testing won't place an undue load on the server, you could consider installing a second SQL instance side-by-side with the production instance but make sure all interested parties accept the risks first.

Alternatives:

• VirtualBox or similar. Better isolation if you have to run this on your production server and you keep the database in SQL Server.
• If you can't buy, rent. Rackspace Cloud, Amazon EC2, Microsoft Azure.