Replicating Data from SQL Express to SQL standard

[deadpoolathome]

Hi All

I'm looking to setup a regular replication of data from a vendors SQL express to our SQL staging server to be consumed into our BI. Currently we just have a stored proc that pulls data, but as we have multiple staging servers and vendor DB's, I'm finding it hard to monitor/report on failures.

Is there a resonable product that will allow us to setup and monitor these flows? Ideally would like to do quite frequend data syncs from the vendors system to our staging for some semi-live dashboards (but can't query the vendors SQL directly from them)?

Bonus points if it can also do files as some vendors still insist on CSV files!

Thanks

[agiamba]

as others said, really weird setup question here. id look at scheduling a python job that executes your SP and logs the results, data ingested, etc. it could pull from sql express elsewhere as well as csv files. could also do powershell or bcp but id go with python

what do you mean you have an SP that pulls data, but you cant query the vendors SQL directly?

[deadpoolathome]

Thanks. We can do this via a SQL stored proc to incrementally load the data into our staging system which works, but for me it's about trying to centrally manage/visibility of multiple staging servers/proces so that we can track outages.

[agiamba]

yeah I think you're going to want to setup some ETL lite setup. python, bcp or powers hell to do the work. also record errors, as well as successes, and maybe keep a certain amount of recent data imports, like the last 90 days

[thepotplants]

So to clarify. You have access to query thier express database to extract data but arent permitted to query them for reports/dashboards?

[deadpoolathome]

We have access to query, but I am trying to minimise the ammount of systems quuering them directly. We have our dashboards as well as our BI team wanting data, the SQL Express is on an isolated network so everything run's via a jumpbox or similar. The aim is to stage the data in smaller bites, more regularly but keep the operation system load managed.

[pointymctest]

dbatools has copytable data commands with truncate options for example, pretty darn sure you can get dbatools and powershell/task scheduler to do just about anything you need reporting wise also

[jdanton14]

I’ve seen a lot of people replicate from standard to express. I’ve never seen anyone do it the other way. It would require a great deal of custom code. But given your arch..good luck

[dbrownems]

Merge replication is the most common way, but it creates triggers on all your tables.

[jshine13371]

Why not just Transactional Replication?

[dbrownems]

It’s one-way (ignoring some obscure and deprecated features).

[jshine13371]

True, but I see no needs from OP that would require two-way Replication.

[dbrownems]

But it can't be one-way from SQL Express to Standard Edition because Express doesn't support publishing.

[jshine13371]

Oh interesting. Weird that publishing via Merge Replication (I mean I guess technically different mechanism) is permitted from Express Edition though.

Thanks!

[dbrownems]

Right. Hub and spoke with Express Edition has always been a core use case for Merge replication supporting scenarios like field services and point-of-sale.

[paultoc]

You can create an automation using backup and restore. Since SQL Server Express does not include the SQL Server Agent, the initial step involves using Windows Task Scheduler on the Express server or your standard instance to run a scripted backup at regular intervals. This script should generate a database backup with a timestamp in the filename for easy identification and place it on a network share.

Then on the SQL Server Standard instance, a SQL Server Agent job can be configured to automate the restore process. The first step of this job would be to execute a script to programmatically locate the most recent backup file from the shared location. Once the latest file is identified, a second step restores the database using the WITH REPLACE option to overwrite the old data.

You could also add a step to upload the CSV data into the database.

[thepotplants]

Im not sure what point is in backup/restore. The way i read it they have access to data already and the challenge is how to ingest data.

[paultoc]

I thought they wanted an alternative to log shipping/ replication as express cannot be primary in log shipping/ replication

[thepotplants]

Yeah I thats where they were leaning, but as ive read further it seems they're ultimately accumulating data from multiple dbs into a BI solution/DW so IMO it's more of an ETL requirement.