1

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!

3 Answers 3

1

If you wanted to keep that "slave" SQL Server, I would suggest forgetting about replication just loading a daily backup onto the server, or fiddling with restore transaction log backups. The DBAs should have those handy, you would just have to convince them to help you get an automated restore of the production data onto the "slave" box. BUT, it sounds to me that you would rather that the slave box goes away.

I would look in detail at the "Change Tracking" and "Change Data Capture" features. From what I have read, "Change Tracking" identifies rows that have changed in monitored tables by providing a list of primary key values. "Change Data Capture" provides more information, including capturing actual before and after values, for full auditing. The hand-wavy blurb is that you would read from SQL Server tables that describe what's changed and then update your mysql database. No fancy replication, or agents, or anything. Here is MS's landing page for those features.

It sounds to me that you could just use Change Tracking and overwrite whatever is in mysql with the currently-good value from the SQL Server. I would expect that Change Data Capture would put more load onto the box than Change Tracking.

As far as the daily 3GB extract goes: I have seen "over-provisioned" servers with poorly performing SAN storage. Having lots of cores doesn't fix every problem. Overnight periods often have other stuff going on, like backups, defrags, other extracts, large report runs, etc. To me, "internet retailer" implies that you could have traffic at any time. The DBAs might simply be wary of adding any load that the don't have to.

1

If the research server only contains data based on the production DB, why not run SQL Server on your linux servers instead of MySQL? If your production database is less than 4GB (which it sounds like it is), you could run SQL Server Express edition for free.

You would not need a separate export step. IT guys should already be making full and transaction log backups of the production DB, so they've already paid that I/O cost. SQL Server Express would easily be able to read and restore from those backups onto your Linux server.

I personally have never used SQL Server DB replication, but it sounds like you have that. So you'd also have the option of replicating from production to the research DB as well.

SQL Server 2017 on Linux
And now there's SQL Server 2019, which is bound to be better!

SQL Server 2017 Express capacity limits

0

I know that you mentioned you did not want to modify the tables, but have you thought about adding an update/insert trigger on the table to write a drop file that your linux box can then pick up and update the MySQL server with?

Something like

USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[trig_WriteDropfileOnChange] ON [dbo].[tableName]
FOR INSERT, UPDATE
AS

-- declare vars
DECLARE @RecordId bigint,
    @RetCode int,
    @FileSystem int,
    @FileHandle int,
    @MyDate varchar(255),
    @FileName varchar(255)

-- build filename
SET @MyDate = getDate()
SET @MyDate = REPLACE(@MyDate, ':', '')
SET @MyDate = REPLACE(@MyDate, ' ', '')
SET @MyDate = REPLACE(@MyDate, '-', '')
SET @MyDate = REPLACE(@MyDate, '.', '')

SELECT @RecordID = myfield
FROM INSERTED

SET @FileName = '\\path\to\drop\directory\' + @MyDate + '.txt'

EXECUTE @RetCode = sp_OACreate 'Scripting.FileSystemObject', @FileSystem OUTPUT
    IF (@@ERROR|@RetCode > 0 Or @FileSystem < 0)
        RAISEERROR('could not create FileSystemObject', 16, 1)

EXECUTE @RetCode = sp_OAMethod @FileSystem, 'OpenTextFile', @FileHandle OUTPUT, @FileName, 2, 1
    IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
        RAISEERROR('Could not open file.', 16, 1)

EXECUTE @RetCode = sp_OAMethod @FileHandle, 'WriteLine', NULL, CONVERT(varchar, @RecordId)
    IF (@@ERROR|@RetCode > 0 Or @FileHandle < 0)
        RAISEERROR('Could not write to file.', 16, 1)

EXECUTE @RetCode = sp_OAMethod @FileHandle, 'Close', NULL
    IF (@@ERROR|@RetCode > 0)
        RAISEERROR('Could not close file.', 16, 1)

EXEC sp_OADestroy @FileSystem

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .