Keeping schemas in sync with a database?



  • 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?



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2