We have a production server running SQL-Server, which holds our backend data, e.g., customer information, sales invoices etc. The nature of this data is that "old" data can change, e.g., a customer updating their address.
Currently we use a Windows Server 2008 instance as a research server, so that we can have an MS-SQL slave running on it, which replicates from the live server in a transactional way. We then do daily flat-file exports from the slave into our research schema, which happens to be a MySQL DB. We do this by reading in slave data into some algorithms, which modify and write the data to the MySQL research db. We drop all the tables in the research db and do clean imports each day to deal with the "old" data that may have changed in the production and slave DBs.
So, essentially, to go from the the live DB to our research schema always involves a complete dump and re-import every day, which is fine for us because it's all done locally.
Unfortunately, we have a lot of issues with both the MS-SQL replication and the Windows server; the source for these issues is actually mostly human-based rather than technical. However, we do have added problems that we have to use other Linux application servers because running some of the platforms/stacks we need in other areas is hard on Windows...
I want to move our research server to a Linux box, which I'm much more familiar with, and capable of managing and configuring correctly, but I don't know the best plan of action, for getting from the live MS-SQL DB to our research MySQL schema, given we now won't have the option of having live replication to a slave.
Basically:
- there are around 5 SQL-Server tables totalling ~3GB
- our IT guys are worried about the IO load on the production server if we do 3GB exports every day (currently we do transaction replication to a slave and then do 3GB exports from the slave).
Our update processes for getting the SQL-Server data into our research schema are expecting to do entire imports each day, but of course it doesn't matter whether they import from flat-files/SQL files or tables.
So, we know, one way or another, we will be doing flat-file imports of the SQL-Server data, but, the question is - is there a smart way to do exports from the MS-SQL tables daily, without having to do an entire dump?
I'm not talking about replication, because there won't be a MS-SQL instance on the receiving end, due to it being a Linux server. But, is it, say, possible to export from the MS-SQL server only rows which have had fields modified? That is, without modifying the SQL-Server tables' schemas of course to mark edited rows... :-)
Can SQL-server keep a log of modified rows or anything?
Failing all that, does anyone have an inkling about whether an under-provision SQL-Server instance would be expected to fall over every time you did a 3GB export???? That bit I don't really buy... We're talking about minimal load on the DB in the early hours of the morning (we are a small B2B internet retailer so wouldn't expect much to be going on in those hours).
Thanks!