4

My customer uses pair of databases (SQL Server) where one references the other. Let's say DataDb and ConfigDb. ConfigDb contains hundreds of SPs that references DataDb like [DataDb].[dbo].[the_object].

This pair of databases is deployed to their customers together with some applications.

For development and testing I need to have locally pairs for several customers at once and run the applications against the right pair just by changing the connection string.

When I restore the customer's databases I use suffix of the customer, like DataDb_Cust1, ConfigDb_Cust1. The problem is that stored procs from ConfigDb have hardcoded the name of the referenced database (there's always [DataDb].[dbo].[the_object] which I need to change to [DataDb_Cust1].[dbo].[the_object]).

I could go through all the object (SPs, functions, views) and rename the referenced DB name. I already have some scripts to automate that. But is there a better way (e.g. just use some alias defined on SQL server level or something like that) so that the referenced DB name does not have to be hardcoded in SPs?

I looked at synonyms, but it turns out they are not supported by Microsoft Entity Framework - ORM used by the application. I'd need a solution that would work with EF6. However I can verify it by myself. Any other solution?

The size of DB varies, but typically the DataDb is between 1 and 8GB, the ConfigDb is quite small. No Enterprise features, but it uses SQLCLR assemblies, for some customers even linked servers to access other DBs in distributed transactions (using MSDTC) but it is rare case. I have now 6 sets and expect at most 20 in near future.

The DataDb uses FILESTREAM, which is not supported by LocalDb.

0

2 Answers 2

3

If the databases are under 10 GB, then it might be easier to use SQL Server Express Edition and create a separate Instance for each customer. This would allow for full separation between the paired Databases (a more accurate testing environment) and means that you wouldn't need to change any of the DB names. If the app and SQL Server are on the same server for dev and testing, then you might get away with using SQL Server Express LocalDB (a.k.a. just "LocalDB") which is really quick and easy to create new Instances and to start and stop them. But LocalDB does not allow for TCP/IP connections, or any remote connections; it is local only.

Even if you need Standard or Enterprise Edition features, it might still be easier to create an Instance per customer rather than modifying objects to reference a different Database name, especially since any modifications introduces the risk of changing some incorrectly and then breaking something that you aren't even working on.

And, if you have a standard naming convention, such as DEV_{customer_code}, then it would be fairly easy to write a command-shell script (.cmd file) to start and stop the Instances simply by passing in the {customer_code}, and then you could create desktop short-cuts for those and/or call the script via automated testing, etc.

Don't forget, SQL Server 2016 Developer Edition is now free. You don't need the Enterprise features, but you can't beat that price :-).

0
0

If you must keep all the databases on one server, then you could do dynamic SQL to determine the name of the database. Here is an example of code that goes through all the Databases on the Server.
ex:

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(1000)
DECLARE database_cursor CURSOR FOR 
SELECT name FROM MASTER.sys.sysdatabases 
OPEN database_cursor

FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
     SELECT @Command = 'USE [' +  @DB_Name + ']; SELECT * FROM ' + @DB_Name+'.[dbo].[the_object] '
     EXEC sp_executesql @Command

     FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor
3
  • 1
    In general when delimiting a name, in addition to adding brackets around the name you should double any closing bracket in the name, e.g. a name like DB[1] would need to be delimited as [DB[1]]] (the closing bracket after the 1 is doubled and a third closing bracket is added as a name delimiter). So instead of 'USE [' + @DB_Name + ']; ...' it should be 'USE [' + REPLACE(@DB_Name, ']', ']]') + ']; ...'. However, there's actually no need to do that because T-SQL has a dedicated function for delimiting names – QUOTENAME, so you can do just 'USE ' + QUOTENAME(@DB_Name) + '; ...'.
    – Andriy M
    Commented Apr 3, 2018 at 11:55
  • 1
    Also, you don't need to qualify a table with a database name if you've already issued a USE instruction with that database name. That is, in USE MyDB; SELECT * FROM MyDB.dbo.MyTable; the second MyDB is unnecessary, MyDB is already the context. Alternatively, you could keep the DB name in the SELECT and skip the USE command.
    – Andriy M
    Commented Apr 3, 2018 at 11:56
  • Yes you are right. I didn't even notice I made my script that way. It's an old script I use once in a while ;-) Commented Apr 3, 2018 at 19:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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