Sql-server – Creating a test database from Production database (different machines & servers)

backupMySQLpostgresqlsql servertesting

I'd like to set up a test database on a separate server. Production database is MS SQL Server and the test server is a *nix box so I'd like to have the test server running either PostgreSQL or MySQL.

The dataset is pretty large; some of the tables have 30k+ rows and there are a lot of tables.

Some of the tables don't change much at all, and many of the tables can be empty for the purposes of testing, so ideally some kind of solution that can copy some but not all of the records from a database would be ideal.

Barring that, I may just make a test copy of the production database on the same server rather than putting the test db server on the development server. 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?

And if I am copying from production -> test within MS SQL Server, is there some kind of automation script I can set up that selectively copies only some of the records (for example, only the last 1k rows from some tables, none from others, and all rows from a selected few)? Is there a way to set this up as a selective backup, and then use that backup to generate the test database?

Additional info

The thing being tested here is just the code, not the database or even the interface into the database. The code (Django) uses an ORM so it doesn't matter which RDBMS is being used. I'm comfortable with assuming that if I call .save() it will work regardless of the database; my concern is whether I have the forms, utility functions, data imports, etc. set up correctly and all of these are abstracted through Django's relational model system which has been shown to be completely indifferent to RDBMS (indeed, in the production version of one of my sites I am pulling from both a MySQL and a SQL Server database at the same time (one contains legacy data).

Best Answer

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:

Related Question