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?
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).