T-SQL: Make a Read Copy of Database by Using Synonym, Trigger and SQL Agent Job
DOWNLOAD |
All Codes used in this article is downloadable from this URL. |
** **
Introduction
This article shows the power of the SQL Server Database engine to create an isolated read copy of a database to use as a report database. We didn't use any SQL Server feature that does not belong to the Database engine. So, this solution is an SQL Express ready solution as well as it is suitable for other upper editions.** **
Problem
A Read-Copy of a relational database is a copy of the database just for reading usage. We don't want to write on such database, but also we need to make it up to date. The main idea beyond is creating an isolated report database on a separate disk to achieve better performance. There are solutions like replication, HAG and SSIS to do this. But, we want to make it possible by using T-SQL and whatever we have in SQL Server Database Engine. So, this solution is applicable to all SQL Server editions except the compact edition which has its own limits.** **
Solution
Assuming that we have a single Database Server and an OLTP database on it to serve our application(s). To achieve some complicated or long time running reports we want to create a separate reporting database. We can create this database on a separate disk to achieve the best reporting performance.
Algorithm
The most challenging problem is how to separate read and write on this database to achieve a read copy database solution. In other words, we have to update this database, so we need to write on this database. On the other hand, we do not want to write on this database for having a fresh database that is dedicated for reading purposes. It means that we need at least two databases to perform our solution; when one is using for reporting, other can be updated and vice versa.
The next step is how to sync these two reporting databases:
- We need to create these four databases:
-
- Main (OLTP Database, on disk 0 )
- Search1 (first report database, on disk 1)
- Search2 (second report database, on disk 2)
- Switch (a database to determine active report database, on Disk 0)
- The steps to implement this solution are:
-
- Creating two equal search databases
- Creating a switch database which has the synonyms of the main objects on these two search databases (Now, our development team members just deal with one switch database and call its objects which are the synonym of the main objects in the search databases.)
- Creating a synchronized method to update one of the search databases when the other database is serving our reporting application.
- Automating the whole solution (sync & switch) by using one SQL Server Agent job and a trigger on one table hosted on the switch database to change the synonym(s) from one database to another one.
Implementation
Well, the whole solution will be more clear with a little example. We can see the above steps in the following sample. We will keep it as simple as possible to show the whole solution here.
The following code creates these four databases. For this example, we don’t change the location of the databases, but in production server, they have to be located on the separate disks to achieve the best performance.
Code 1 – create databases
--OLTP Database
create database Main
go
--first report database
create database Search1
go
--second report database
create database Search2
go
--a database to determine active report database
create database Switch
go
We can create a sample Book table on the Main database. For simplicity, we create just one table and deal with it to show what the whole solution is. We also create a copy of the Book table on Search1 and Search2 databases. The following code will do it:
Code 2 – create Book tables and fill it
--create table Books on Main
use Main
go
create table Books
( BookId int identity
primary key,
Title nvarchar(500)
);
go
--insert sample data
use Main
go
insert Books
values ( N'Title 1'),
( N'Title 2'),
( N'Title 3'),
( N'Title 4');
go
--create table Books on Search1
use Search1
go
create table Books
( BookId int --identity
primary key,
Title nvarchar(500)
);
go
--create table Books on Search2
use Search2
go
create table Books
( BookId int --identity
primary key,
Title nvarchar(500)
);
go
We have to create a stored procedure to sync the Book table on the search databases. Again, for keeping it simple, we create the following procedure that just inserts the new rows. We make it on both search databases.
Code 3 – create procSyncBook(s)
--create proc procSyncBook on Search1
use Search1
go
create proc procSyncBook
as
set nocount on;
insert Books
select BookId, Title
from Main.dbo.Books
where BookId > (select isnull(Max(BookId),0)
from Books );
go
--create proc procSyncBook on Search2
use Search2
go
create proc procSyncBook
as
set nocount on;
insert Books
select BookId, Title
from Main.dbo.Books
where BookId > (select isnull(Max(BookId),0)
from Books );
go
For implementing a reporting scenario, the following code creates a stored procedure for querying on the Book table. We create this procedure on both Search databases:
Code 4 – create (s)
-- create procBookSearch on Search1
use Search1
go
create proc procBookSearch
( @Title nvarchar(500) )
as
set nocount on;
select BookId, Title
from Books
where Title like N'%' + @Title + N'%';
go
-- create procBookSearch on Search2
use Search2
go
create proc procBookSearch
( @Title nvarchar(500) )
as
set nocount on;
select BookId, Title
from Books
where Title like N'%' + @Title + N'%';
go
We have to create another stored procedure on the Switch database. This procedure dynamically creates and changes the SYNONYM of the search procedure (procBookSearch) on the active search database. The following code creates this procedure:
Code 5 – create SetActiveDatabase proc
-- create proc SetActiveDatabase on Switch
use Switch
go
/*
created by Saeid Hasani
date: 2015-05-31
sample:
exec dbo.SetActiveDatabase @DatabaseName = N'Search1'
exec dbo.SetActiveDatabase @DatabaseName = N'Search2'
*/
create proc SetActiveDatabase
( @DatabaseName sysname)
as
begin
set nocount on;
declare @sql nvarchar(max);
if exists (
select *
from sys.synonyms
where name = N'procBookSearch') begin
set @sql = N'DROP SYNONYM [dbo].[procBookSearch]'
exec sp_executesql @sql;
end;
set @sql = N'CREATE SYNONYM [dbo].[procBookSearch] FOR '+ quotename(@databaseName) +N'.[dbo].[procBookSearch]'
exec sp_executesql @sql;
end;
Next step is creating a table to store the active database name. By updating this table one trigger will call the SetActiveDatabase procedure to change the synonym. The next code creates this table and its trigger:
Code 6 – create ActiveDatabase table and trigger
--create table ActiveDatabase on Switch
use Switch
go
create table ActiveDatabase
( DatabaseName sysname NOT NULL
) ;
go
/*
created by Saeid Hasani
date: 2015-05-31
*/
create trigger trgSetActiveDatabase on ActiveDatabase
for insert, update, delete
as
set nocount on;
declare @DatabaseName sysname = ( select DatabaseName from inserted );
exec dbo.SetActiveDatabase @DatabaseName;
declare @err varchar(4000) ;
if error_message() is not null begin
set @err = error_message()
rollback tran;
raiserror (@err, 16, 1);
end;
go
The final step is creating a SQL Server Agent job that will do these steps,
- Call the *procSyncBook *
- Update the ActiveDatabase table
Updating that table will fire the trigger on it and will change the active database. Using the following code, we can create this job. The next images show how to create this job, by using SQL Server Management Studio.
Code 7 – create job
declare @DatabaseName sysname =
( select DatabaseName
from [Switch].[dbo].[ActiveDatabase]);
if ( @DatabaseName = N'Search1' )
begin
set xact_abort on;
begin try
begin tran
exec Search2.dbo.procSyncBook;
update [Switch].[dbo].[ActiveDatabase]
set DatabaseName = N'Search2';
commit tran
end try
begin catch
if @@trancount > 0
rollback tran;
throw;
end catch
end
else if @DatabaseName = N'Search2'
begin
set xact_abort on;
begin try
begin tran
exec Search1.dbo.procSyncBook;
update [Switch].[dbo].[ActiveDatabase]
set DatabaseName = N'Search1';
commit tran
end try
begin catch
if @@trancount > 0
rollback tran;
throw;
end catch
end
else if ( @DatabaseName is null )
begin
set xact_abort on;
begin try
begin tran
exec Search2.dbo.procSyncBook;
insert [Switch].[dbo].[ActiveDatabase]
values ( N'Search2' );
commit tran
end try
begin catch
if @@trancount > 0
rollback tran;
throw;
end catch
end
Visual Steps to Create the SQL Server Job
Pic 1
Pic 2
Pic 3
Test the Solution
It is the time to test our solution. As illustrated in the next screenshot, all the developers just know the Switch database. They can feel free to write their code against the Switch database even their embedded codes. They can build and deliver to the application without worrying about the data tier which is logically and physically managed now.
Pic 4
Conclusion
This article showed the power of the SQL Server Database engine to create an isolated read-copy of a database to use as a report database. There are other solutions to do this. But, we wanted to make this possible just by using the T-SQL and whatever we have in SQL Server Database Engine.
DOWNLOAD |
All Codes used in this article is downloadable from this URL. |
** **
See Also
Other Resources