Sample Header Ad - 728x90

Keeping schemas in sync with a database?

0 votes
1 answer
198 views
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?
Asked by Brian Karabinchak (187 rep)
Apr 15, 2022, 05:11 PM
Last activity: Jun 20, 2025, 01:02 PM