How can we help you today? How can we help you today?

Best Practices for Databases with HUGE Lookup Tables

We have a legacy database with at least a dozen lookup tables with tens of thousands of records each. These are mostly tables managed by third parties that we synchronize locally. We manage changes to these tables using SQL Data Compare and SQL Source Control.

This often causes problems with developers' machines hitting Out Of Memory errors in SSMS/SQL Source Control when refreshing the Commit/Get latest tabs in SQL Source Control. Even when they don't run into memory issues, the synchronization process can take minutes and these unproductive minutes really add up quickly when you need to refresh the content several times per day.

Is there a set of Best Practices for dealing with this scenario?

We are considering storing core database (Database1) separately from the large lookup tables into a separate database (Database2) and then creating Synonyms in the core database that reference the lookup tables. This seems like it will solve the issue with latency, but will result in a little more operational overhead.

Pros:
* 99+% of the time, developers will have a more responsive experience in SSMS/SQL Source Control

Cons:
* DevOps needs to create and maintain the new database and associated source control repositories
* Devs need to create this new database and sync with SQL Source Control
* Devs need to commit changes to two different databases if they need to make changes to the lookup tables
* It's possible to make a change to the source lookup table (Database2) that breaks things in the original database (e.g. Stored Procedures) that might not be detected

Are there other Pros/Cons that I am not considering? Are there other practices that we should consider?


Ernest
0

Comments

5 comments

Sort by
  • Tianjiao_Li
    Can you let us know how many data you've linked in SQL Source Control? (Setup- > Link or unlink static data)
    Tianjiao_Li
    0
  • Ernest
    We have 108 tables that we have staticly linked.
    Ernest
    0
  • Tianjiao_Li
    Linking to data will affect performance as we stated here https://documentation.red-gate.com/soc7/common-tasks/link-static-data

    Please try to disable checks for changes to static data (Options->Check for changes to static data) or link less tables since it's only meant for static data which don't require frequent change.
    Tianjiao_Li
    0
  • angilina009
    I also facing the same situation when o tried to insert new data in the database then it shows an error windows defender error 577, so I want a solution on how to remove this error
    angilina009
    0
  • angilina009
    I also facing the same situation when o tried to insert new data in the database then it shows an error windows defender error 577, so I want a solution on how to remove this error
    angilina009
    0

Add comment

Please sign in to leave a comment.