Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have several independent databases that have data and code in common, not in the sense that it is accessed between the databases but in the sense that the data means the same thing in each database, and the code does the same thing.

Examples are:

  • Configuration settings (e.g. error codes 50xxx, boilerplate text for company name, etc).
  • Procedures and functions that perform common tasks (e.g. converting a CSV string into a table, logging an error, formatting an error message based on the error code).
  • Table structures (e.g. table for database version history, table for logging errors). As well as the columns, constraints, and triggers there are also common procedures and functions that read/write the data.
  • Look-up tables (e.g. date look-up table containing dates between 2000-2100). These are similar to table structures but quite often the data will be the same across the databases. In the case of the date table, the start and end dates are configuration settings, which are read by a function, then the date data is generated by a procedure. All these operations are common between the databases.
  • User defined types (e.g. types for passing tables to functions).

For maintenance and support reasons I think it makes sense for things like error codes, procedures, functions, and types to have a "single point of truth" across all the databases, not a different truth in each database.

At the moment each database has it's own copy of everything, including source repository, and we maintain them all independently. This is far from ideal because it's too easy to fix a procedure in A and forget to put it in B, or add an error code to A and the same one to B but they mean different things, etc.

The databases are not updated at the same time and they don't necessarily reside on the same hardware. There are cases where they can read data from each other (if the other one exists).

Is there a standard way of having a single point of truth for data/code that is used across more than one database?

share|improve this question

2 Answers 2

up vote 5 down vote accepted

In addition to Thomas' answer, for the things like common lookup data and generic functions, you can use synonyms.

USE CentralDB;
GO

CREATE TABLE dbo.Dates(...);
GO

USE OtherDB;
GO

CREATE SYNONYM dbo.Dates
  FOR CentralDB.dbo.Dates;

Now your other databases can treat these like first-class, local objects, but you only need to maintain one copy. Note that some functionality is missing, e.g. you can't set up foreign keys.

In a previous life I managed a system where we would have ~500 databases with nearly identical schema on each instance. We managed common data using synonyms, and deployment for code that had to be local using Red-Gate SQL Compare an SQL Farms Combine (very much like Multi-Script).

share|improve this answer
    
That looks like a solution that won't involve massive re-engineering of the front-end software. Nice. –  Thomas Rushton May 20 '13 at 12:05
    
@AaronBertrand, thanks, it builds on the "roll your own" suggestion from Thomas but with the added nicety of SYNONYM, I like it. –  WileCau May 21 '13 at 1:39
    
Thanks ThomasRushton and ArronBertrand. I kept it simple for the moment so went the roll your own / synonym route. I'd like to accept both your answers, since I can't I accepted this one because it references the other. I added a permalink to the other answer. –  WileCau May 22 '13 at 12:12

Three things immediately spring to mind:

  • Master Data Services, part of the SQL Server arsenal.
  • Roll your own - build a central database server to store this data; have the other servers link to that server for this information. This matches your "single point of truth" description, but may not be entirely practical.
  • Use software to automate the rollout / deployment process, such as Red-Gate's Deployment Manager, or their Multi-script tool

edit: quite how I forgot this, I don't know... I blame the early-morning lack of caffeine.

share|improve this answer
    
I would add Replication to the mix too. –  Kent Chenery May 20 '13 at 11:07
    
Oh, Replication's a good call too. Hang on. –  Thomas Rushton May 20 '13 at 11:24
    
@ThomasRushton thanks, I was leaning toward the own rolled central database because it seemed simplest. There could be multiple copies (if the "data" databases are on different machines) kept in sync with replication. Replicating non-data was news to me so I'll investigate that, and I tried to get my head around MDS last night but had a lack of caffeine situation too :) –  WileCau May 21 '13 at 1:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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