MS SQL Server - What type of replication should i use?

Author

What type of replication should i use?

stuckish@gmail.com

2006-12-05, 7:12 pm

We need to provide a external company with a copy of our database on a
regular basis, probably about once every night. Our database is a SQL
Server 2005 Ent edition and we assume the external company will use the
same ..

I have looked around and realized that there is a lot of alternatives.
I can state some facts and some requirements ..

1. The data do not need to be up-to-date, once per night is ok.
2. The database is rather big ~5-10 (depend on log size) gb and i think
it will grow ..
3. Performance on the server is not a big issue since the system is
usally down during nights.
4. I think we have ok (~10mbit) bandwidth between us and the external
company ..

As i can see it it seems like Snapshot replication is the best way to
go in our scenario where we need a as fast system as possible during
work hours but do not care at all during night.

Anyone have any suggestions?

Hilary Cotter

2006-12-05, 7:12 pm

I would use transactional. Snapshot requires the entire database sent to the
subscriber each night. Transactional replication will only require all
changes to move.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

<stuckish@gmail.com> wrote in message
news:1165327696.060262.219930@79g2000cws.googlegroups.com...
> We need to provide a external company with a copy of our database on a
> regular basis, probably about once every night. Our database is a SQL
> Server 2005 Ent edition and we assume the external company will use the
> same ..
>
> I have looked around and realized that there is a lot of alternatives.
> I can state some facts and some requirements ..
>
> 1. The data do not need to be up-to-date, once per night is ok.
> 2. The database is rather big ~5-10 (depend on log size) gb and i think
> it will grow ..
> 3. Performance on the server is not a big issue since the system is
> usally down during nights.
> 4. I think we have ok (~10mbit) bandwidth between us and the external
> company ..
>
> As i can see it it seems like Snapshot replication is the best way to
> go in our scenario where we need a as fast system as possible during
> work hours but do not care at all during night.
>
> Anyone have any suggestions?
>

Paul Ibison

2006-12-05, 7:12 pm

I'm wondering if you have connectivity between the client's site and your
SQL Server? This is often an issue and I have worked in places where ports
couldn't be opened on firewalls due to internal policy. If there is
connectivity, then you'll have to gauge the pros and cons of the 2 offered
solutions. I'd personally ship the database backups in compressed format as
a first effort then see how long this process takes - if it's acceptable,
then you don't need to explore further. There is also another option - that
of shipping the logs (restore with STANDBY) to the customer - it'll save
unnecessarily sending over many GB which probably doesn't change each day.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

stuckish@gmail.com

2006-12-05, 7:12 pm

Thanks everyone .. i probably will go for the compressed .bak file. I
guess that replication only add a level of complexity that is not
necessary.

Br, Ola

On Dec 5, 4:01 pm, "Paul Ibison" <Paul.Ibi...@Pygmalion.Com> wrote:
> I'm wondering if you have connectivity between the client's site and your
> SQL Server? This is often an issue and I have worked in places where ports
> couldn't be opened on firewalls due to internal policy. If there is
> connectivity, then you'll have to gauge the pros and cons of the 2 offered
> solutions. I'd personally ship the database backups in compressed format as
> a first effort then see how long this process takes - if it's acceptable,
> then you don't need to explore further. There is also another option - that
> of shipping the logs (restore with STANDBY) to the customer - it'll save
> unnecessarily sending over many GB which probably doesn't change each day.
> Cheers,
> Paul Ibison SQL Server MVP,www.replicationanswers.com.

Sponsored Links