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.

Paul White
  • 94,921
  • 30
  • 437
  • 687
eXavier
  • 141
  • 4

2 Answers2

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 :-).

Andriy M
  • 23,261
  • 6
  • 60
  • 103
Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306
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
Danielle Paquette-Harvey
  • 2,099
  • 1
  • 17
  • 31