2

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

Improve this question
2
  • 3
    How would it be a "test" environment if the database is recreated in an entirely different RDMS?
    – Mark Storey-Smith
    May 29, 2012 at 20:57
  • It's not strictly a test environment. Basically, the test server is where I test newly-written code that will eventually be staged to the production server, and where I test out that code. I don't want to mess up production data when testing but I do need a fairly large representative set of data and I want it to be "real" (i.e. no "John Doe"s). So I want to be able to test new code on "real" data without posing danger to the real data. Not sure what the correct term for this would be. So the database is not being "tested" here, just the code; the database is assumed to be working.
    – Jordan Reiter
    May 29, 2012 at 21:55

2 Answers 2

Reset to default
6

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:

Improve this answer
1
  • Looks like it will be easier to use another SQL Server instance, so that is the way we're going, but I genuinely think that the different RDBMS systems don't make a difference. What I'm testing is whether the web application I'm writing is working, not whether the underlying framework is correctly able to perform relatively rudimentary SQL operations. I'm more likely to run into problems on the client-side (someone using an old version of IE, say) than on the database end, I think.
    – Jordan Reiter
    May 30, 2012 at 14:21
3

The usual problem with selective copying of many tables (but not all tables, and not all rows) is that people run into foreign key violations because they forget something somewhere. If django isn't declaring foreign keys (and I don't know anything about django, but I am old enough to remember when ORM mean "run away quickly"), some functionality might just wind up broken, and you will have to troubleshoot those problems because it might be missing data or it might be code. In short, I suspect that this is more trouble than it's worth.

30KR doesn't sound like a lot of data, unless you have thousands of tables. I'd evaluate the size of the tables and see if I could just restore the database to a seperate machine running the dev edition. You could also restore a copy of the database back to the produciton server (assuming that you have enough space) but then you'd have to be extra careful to use the test database for testing and that whatever test load you have doesn't impede production.

Improve this answer
1
  • In order to avoid messing up data integrity, in a demo database of ours we use a 'delete-script'. Recreating the demo, we first restore from an applicable dump and then delete unnecessary (or sensitive) data (well, actually truncate and not delete). This way we see what we screwed up directly from the error messages prouced by the delete script. For much bigger amount of data, we surely would have to invent a different approach.
    – dezso
    May 30, 2012 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.