For example, the same select for different website instances:
SELECT ID, Name, Surname FROM Client1_DatabaseB.dbo.Staff
And
SELECT ID, Name, Surname FROM Client2_DatabaseB.dbo.Staff
With every bug fix or software enhancement update we did, we had to ensure the correct database names are used. This became tedious to maintain. After research, I’ve found 2 possible solutions:
1. The use of T-SQL
2. Or use SQL Synonyms
I will have another blog post where I will discuss T-SQL in detail. I decided to use Synonyms and have the following SQL Select outcome on both Client databases:
SELECT ID, Name, Surname FROM Staff
To create a new Synonym, find below example syntax:
CREATE SYNONYM SynonymName FOR DatabaseName.Shema.TableName;
While I was in a planning stage, we decided that we want to auto create all the Synonyms with one SQL command. This is what I did:
I first created a SQL Table where I list all the database names for the website instance:
CREATE TABLE [dbo].[DatabaseNames]( [ID] [int] IDENTITY(1,1) NOT NULL, [Key] [nvarchar](50) NOT NULL, [Value] [nvarchar](255) NOT NULL, CONSTRAINT [PK_DatabaseNames] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
I then added records to the [dbo].[DatabaseNames] SQL table with all the database names per client website:
Client 1
INSERT INTO [dbo].[DatabaseNames]([Key],[Value])
VALUES ('DatabaseA','Client1_DatabaseA')
INSERT INTO [dbo].[DatabaseNames]([Key],[Value])
VALUES ('DatabaseB','Client1_DatabaseB')
INSERT INTO [dbo].[DatabaseNames]([Key],[Value])
VALUES ('DatabaseC','Client1_DatabaseC')
Client 2
INSERT INTO [dbo].[DatabaseNames]([Key],[Value])
VALUES ('DatabaseA','Client2_DatabaseA')
INSERT INTO [dbo].[DatabaseNames]([Key],[Value])
VALUES ('DatabaseB','Client2_DatabaseB')
INSERT INTO [dbo].[DatabaseNames]([Key],[Value])
VALUES ('DatabaseC','Client2_DatabaseC')
I then created a SQL Table where I listed all Synonyms that we need:
CREATE TABLE [dbo].[Synonyms]( [ID] [int] IDENTITY(1,1) NOT NULL, [DatabaseNameKey_CreateOn] [nvarchar](255) NULL, [DatabaseNameKey_ConnectTo] [nvarchar](255) NULL, [Synonym_Name] [nvarchar](255) NULL, [Synonym_SchemaTable] [nvarchar](255) NULL, CONSTRAINT [PK_ Synonyms] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
We then added records to the table with all the synonym names:
Client 1 and Client 2 (1 example)
IF NOT EXISTS (SELECT [ID]
FROM [dbo].[Synonyms]
WHERE [DatabaseNameKey_CreateOn] = 'DatabaseA'
AND [DatabaseNameKey_ConnectTo] = 'DatabaseB'
AND [Synonym_Name] = '[SYN_Staff]')
BEGIN
INSERT INTO [dbo].[Synonyms] ([DatabaseNameKey_CreateOn],
[DatabaseNameKey_ConnectTo],
[Synonym_Name],
[Synonym_SchemaTable])
VALUES ('DatabaseA','DatabaseB','[SYN_Staff]','[dbo].[Staff]')
END
Now that I have the data, I need to create the Synonyms. I’ve created the following stored procedure to create the Synonyms for each record in the [dbo].[Synonyms] SQL Table:
CREATE PROCEDURE [dbo].[p_CreateSynonyms]
AS
BEGIN
DECLARE @TSQL AS NVARCHAR(MAX) = ''
DECLARE @ID AS INT = 0
DECLARE @DatabaseNameKey_CreateOn AS INT = 0
DECLARE @DatabaseNameKey_ConnectTo AS INT = 0
DECLARE @Synonym_Name AS NVARCHAR(255) = ''
DECLARE @Synonym_SchemaTable AS NVARCHAR(255) = ''
DECLARE @DatabaseNameValue_CreateOn AS NVARCHAR(MAX) = ''
DECLARE @DatabaseNameValue_ConnectTo AS NVARCHAR(MAX) = ''
DECLARE @SQLPattern AS NVARCHAR(MAX) = '
USE %%DATABASE_CREATEON%%
IF EXISTS (SELECT Name
FROM sys.synonyms
WHERE Name = REPLACE(REPLACE(''%%SYNONYM_NAME%%'', ''['', ''''), '']'', ''''))
BEGIN
DROP SYNONYM %%SYNONYM_NAME%%
END
CREATE SYNONYM %%SYNONYM_NAME%% FOR %%DATABASE_CONNECTTO%%.%%SCHEMA_TABLE%%'
DECLARE @Synonyms AS TABLE ([ID] INT,
[DatabaseNameKey_CreateOn] NVARCHAR(255),
[DatabaseNameKey_ConnectTo] NVARCHAR(255),
[Synonym_Name] NVARCHAR(255),
[Synonym_SchemaTable] NVARCHAR(255))
INSERT INTO @temp_RUBiQBI_DW_Synonyms ([ID],
[DatabaseNameKey_CreateOn],
[DatabaseNameKey_ConnectTo],
[Synonym_Name],
[Synonym_SchemaTable])
SELECT [ID],
[DatabaseNameKey_CreateOn],
[DatabaseNameKey_ConnectTo],
[Synonym_Name],
[Synonym_SchemaTable]
FROM [dbo].[Synonyms]
WHILE (SELECT COUNT([ID]) FROM @Synonyms) <> 0
BEGIN
SELECT TOP 1 @ID = ISNULL(ID,0),
@DatabaseNameKey_CreateOn = ISNULL(DatabaseNameKey_CreateOn,''),
@DatabaseNameKey_ConnectTo = ISNULL(DatabaseNameKey_ConnectTo,''),
@Synonym_Name = ISNULL(Synonym_Name,''),
@Synonym_SchemaTable = ISNULL(Synonym_SchemaTable,'')
FROM @Synonyms
SELECT @DatabaseNameValue_CreateOn = '[' + ISNULL([Value],'') + ']'
FROM [dbo].[DatabaseNames]
WHERE [Key] = @DatabaseNameKey_CreateOn
SELECT @DatabaseNameValue_ConnectTo = '[' + ISNULL([Value],'') + ']'
FROM [dbo].[DatabaseNames]
WHERE [Key] = @DatabaseNameKey_ConnectTo
SET @TSQL = REPLACE(REPLACE(REPLACE(REPLACE(@SQLPattern, '%%SYNONYM_NAME%%',
@Synonym_Name), '%%SCHEMA_TABLE%%', @Synonym_SchemaTable),
'%%DATABASE_CREATEON%%', @DatabaseNameValue_CreateOn),
'%%DATABASE_CONNECTTO%%',@DatabaseNameValue_ConnectTo)
EXEC(@TSQL)
PRINT @TSQL
DELETE FROM @Synonyms WHERE ISNULL(ID,0) = @ID
END
END
GO
Then execute the stored procedure to create all the Synonyms:
Client 1 and Client 2
EXEC [dbo].[p_CreateSynonyms] GO
You will find your Synonyms on the SQL Server Instance at:
- Using SQL Server Management Studio
- Expand the ‘Client1_DatabaseA’ and ‘Client2_DatabaseA’ databases
- Expand the Synonyms Folder within each database
- You will find the ‘dbo.Staff’ Synonym
Now you can use the following SQL SELECT statement for both Client 1 and Client 2 on the Client1DatabaseA and Client2DatabaseA databases respectively.
SELECT ID, Name, Surname FROM Staff
This will also help if you need to run Microsoft SQL Server Database Schema comparisons between the 2 Client databases because the compare tool will not report on differences on Stored Procedures, Functions and Views where Synonyms are used instead of database names.
: