0

I have been asked to work on a database that has a regretful design and cannot be changed so I would ask that we work within these parameters.

This is all in SQL Server 2019.

I have SQL Server that has many databases, one for each field operation, typically named after the city and state. So imagine databases named like

New York City, NY
Princeton, NJ
...

and so for about 80 databases.

Now, I am being asked to keep these in line with a SQL Server that is hosted on Azure except there is only one database there and I am not allowed to create more to make a 1-1 relationship here.

I am allowed to create schemas under that database. So I can have schemas with names that match the database exactly. That is what my cowoker started doing so, so the Azure database looks like

Main Database:
    Schemas:
    -New York City, NY
    -Princeton, NJ
    ....

What would be the easiest way to keep the schema versions of the databases on the Azure server in sync with the database? Right now my coworker wrote a stored procedure that runs every X minutes that does a pushing of data as a proof of concept on the first database/schema, but I wanted to know if there was any built in ways to do this?

2
  • Let me ask you this, why do you want to synchronize the data down to a single database from all of the databases in Azure?
    – J.D.
    Apr 15, 2022 at 23:25
  • 1
    The schema compare tool in Visual Studio is what is usually used as the standard way of checking schema's AFAIK, but this will be little help in your case as you can't target a particular schema to a whole database. It will only compare DB to DB. Seems like custom might be the only way...
    – blobbles
    Apr 25, 2022 at 21:20

1 Answer 1

0

Are you trying to keep the objects in sync between Azure and your local SQL instance, or are you just trying to move the data? If it's the later, I'd use an SSIS package for that.

Your Answer

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

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