3

I've a situation where I have two databases:

  • ABCData
  • ABCUsers

One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded:

INSERT ABCUsers.dbo.Table1 VALUES('a');

But the next problem we run into is that we want to backup and redeploy these database pairs to similarly named other databases for testing and dev purposes:

  • XYZData
  • XYZUsers

Which means the XYZData database will contain a sproc that doesn't work - because the insert has a hard coded ABCUsers.dbo.Table1, and we don't want to get into changing it

We could find out the current database name in the sproc, adjust it and run the sql dynamically:

DECLARE @DataDbName sysname = (DB_NAME());
DECLARE @UserDBName NVARCHAR(20) = (SELECT REPLACE(@DataDbName, 'Data', 'Users'));
EXEC CONCAT('INSERT ', @UserDbName, '.dbo.Table1 VALUES(''a'');')

But I think the prospect of doing so actually makes me feel physically ill ;) (not least because this is a massively simplified example of tens of sprocs each making tens of calls to other dbs)

Is there a way to alias a database name during its creation, so the sproc can always say something like:

INSERT UserDbAlias.dbo.Table1 VALUES('a');

And when the ABCUsers database is created, the UserDbAlias -> ABCUsers but when XYZUsers db is created UserDbAlias -> XYZUsers


Alternatively, this is a git/tfs source control - is there a way to write the source control version of the sproc so that it is modified dynamically when it's deployed, and the original source can contain some kind of placeholder for the db names, but they're replaced with actual hard coded varying db names upon deploy (automatically)


Footnotes: I looked at A way to reference other DB without hardcoding its name which does seem to describe my problem, but doesn't have an answer. That question mentioned "I've looked at synonyms but they wouldn't work out with EF6.." (paraphrase)

Would synonyms work out for me? We use EF6, but I'm not sure what the actual problem is in terms of why it wouldn't work out. Our sproc name in the db never changes, and we don't need to alias the sproc itself, it's the insert table name we need to alias. If the sproc is always called Sproc1 regardless of whether it's in ABCData or XYZData, and the synonym resolving happens inside the sproc, would EF6 even have a problem with it?

  • why do you need to change the DB names? You mention "testing and dev" but you don't mention having multiple copies in prod for different customers (or whatever the difference would be). Why not just keep the same name on the dev and testing servers? – Solomon Rutzky Mar 29 '18 at 14:14
  • The test server might have 20+ database pairs on it – Caius Jard Mar 29 '18 at 14:20
  • how big are these DBs?...and how big do you see them getting over the next year or two? – Solomon Rutzky Mar 29 '18 at 14:21
  • The dev ones are typically small; contain a pared down set of info from prod. Prod is huge, hundreds of gb – Caius Jard Mar 29 '18 at 14:21
  • 2
    Why waste an enterprise license on a testing instance when dev edition is free? Unattended install of instance should be fairly easy, else it is a good scenario for a virtual box VMs (which can be scripted to copy and rename) or containers. Even if synonyms appear to work, it's not a good test as you are changing the environment and there could be anomalies introduced or unintended consequences that will require additional time to work around. – Solomon Rutzky Mar 30 '18 at 19:36
0

Apart from design considerations raised in the comments, ...

Consider one level of indirection prior to creation of the stored procedure:

  • write a template for the stored procedure, with token placeholders for the schema names

INSERT DBNAMETOKEN.dbo.Table1 VALUES('a');

  • write a pre-deployment script (sed?) that creates a new file specifically for each schema, substituting the actual schema name for the token.
  • place resulting scripts in per-schema deployment directories, from which they may be installed as usual.

Benefits

  • Scales well
  • Both template and custom versions may be under version control
  • Updates/Fixes may still be deployed through a single change to the template file and propagated through a build step
  • Possibly easier to schedule/track deployment of updates to each schema depending on situation
  • Reduces the stored procedure to a simple(r) set of SQL focussed on the task at hand, rather than the dynamics of making it work across different schema.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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