Replication advice for reporting database

SQL Server Administration
1

Hi Folks,

Just looking for some advice on replication options.

At the moment we have two SQL Servers (on separate VMs). Each evening (out-of-hours) there is a job that transfers data from one database to another (reporting) database on the other SQL Server using
Drop table.... select * into


It does this for multiple tables from the source database. The issue we have now is that it's taking so long that our 'time' window is decreasing and we're running into issues with server backups at night.

We're currently looking at other options and they include
Transactional or Snapshot Replication
Mirror with Snap shots

All tables have primary keys so transactional replication could be an option. However, because we don't want live data and want the reporting database to only show data up to the previous day, we may need to run a similar process each night that we have currently using the transactional replication database on the same server once the data has been copied over using replication; this may be faster than the current process which uses linked servers.

Just wanted to ask anyones experience with other methods that will help me decide.

Thanks

2

Sounds like a classic OLTP to OLAP scenario to me. Even if you don't do a full DW design. Integration Services is built for just this scenario. It's the de facto standard for building and updating a reporting server in SQL Server

3

I would first consider SSIS as the most direct and well-established pattern for doing this.

4

Thanks for the response. Just to clarify, are we saying that SSIS can insert quicker than doing a drop table select * into within a stored proc?
Thanks

5

This wouldn't be an OLAP database. It's purely a copy of the source database

6

Thanks for the response. Just to clarify, are we saying that SSIS can
insert quicker than doing a drop table select * into within a stored
proc?

SSIS can do it at least as fast. But performance is only a part of the picture. Control, logging and auditing are also important. Possible to do in procs to be sure. Much easier to develop and maintain in SSIS.

are you sure that

It's purely a copy of the source database

Earlier you said:

don't want live data and want the reporting database to only show data up to the previous day,

which doesn't sound like a simple copy to me.

7

Well, I guess I should clarify, that it's true it's not an exact copy. We copy several tables (not all) from one database to another using the Drop table... create table... select * into.
(drop and create in case there are any schema changes that are made). And yes, these tables are only a snapshot in time of the source tables.

The destination database has reporting tables and stored procs for reporting that are processed on its own SQL instance that are not available in the source.

Our support company take backups and our table processing times are overlapping these backups that are causing Link server errors. Apart from that, we just need to find a quicker way of transferring data whichever way that is as the database is growing quite quickly.

We have audit tables that store times, processed tables etc which are good enough for what we're trying to achieve at the moment so we aren't so concerned about extra logging/auditing for the moment. When we start to develop a data warehouse in future, this will change.

Cheers,

8

You should be using Integration Services. Also, I'd replace your DROP, CREATE, SELECT INTO with TRUNCATE, INSERT INTO ...SELECT even in your current setup

9

OK, I'll have a look into using SSIS. Thanks gbritton. In regards to using Truncate....I'm not a fan of the current setup. However, the amount of schema changes we have, we simply don't have the resources to maintain table truncation.

Many thanks