BEFORE Triggers: replication

Showing posts with label replication. Show all posts

Tuesday, March 27, 2012

Best technique for Replication

thx Christian and Paul fro previous answers!
I have a SQL2000 server that hosts our accounting database and I want to
replicate it to another SQL2000 server so I can write reports etc, and do not
want to use the production server for these tasks. I tried to setup a
"snapshot" and was somewhat successful. My question is would it be better to
do a snapshot or transactional replication? The snapshot seemed to take along
time to run. I also do log backups every 15 minutes.
ps Paul I ordered your book...hope it will help also.
Rick Rushing
System Administrator
SQL Newbie
D & J Construction
Rick,
snapshot is sometimes OK but it'll lock the tables and you can't really do
this regularly, and unless all your data is changing it is overkill.
Transactional would be more appropriate. Log shipping is sometimes used, but
you have to be out of the standby server when the log is restored, which in
most cases rules it out.
Rgds,
Paul Ibison, SQL Server MVP
BTW the only book I've written is my address book

- Hilary's the man for
replication books and it's a very useful resource.
|||Paul,
There will be about 20 users on the SQL production server and only myself
using the other SQL server. My goal is to find a way to have a copy of the
database on the production server to the other server for use in writing
reports etc. So you think transaction would be a better approach?
ps you are right..it was Hilary's bok on replication.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick,
> snapshot is sometimes OK but it'll lock the tables and you can't really do
> this regularly, and unless all your data is changing it is overkill.
> Transactional would be more appropriate. Log shipping is sometimes used, but
> you have to be out of the standby server when the log is restored, which in
> most cases rules it out.
> Rgds,
> Paul Ibison, SQL Server MVP
> BTW the only book I've written is my address book

- Hilary's the man for
> replication books and it's a very useful resource.
>
>
|||Rick - yes this is quite a common approach, and gives you the control to
decide exactly which tables are selected. BTW the Database snapshots in SQL
Server 2005 will offer an interesting alternative.
Rgds,
Paul Ibison
|||Thanks Paul
I will pursue using transactional when my book arrives. We just installed
these SQL servers back in October so it will be a while before we upgrade.
thanks again for the input.
Rick Rushing
System Administrator
D & J Construction
"Paul Ibison" wrote:

> Rick - yes this is quite a common approach, and gives you the control to
> decide exactly which tables are selected. BTW the Database snapshots in SQL
> Server 2005 will offer an interesting alternative.
> Rgds,
> Paul Ibison
>
>

best solutions : sql server replication (Maintenance)

we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
--
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
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
>> we are frequently running the maintenance tasks on production during that
>> databases are not accessible.
>> to avoid this we want to switch over to the standby server during
>> maintenace and after maintenance has been completed
>> switch back to primary server.During maintenance all data changes need to
>> be replicated back to production server.
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>> To accomplish this I am planning to setup database replication from
>> producation database server to Standby server.
>> Please advise best solutions for this like microsoft trnsaction
>> replication,Goldengate replication, wansync replication.
>> I think microsoft trnsaction replication,Goldengate replication doesn't
>> support complex shema changes to get replicated.
>> Requirement :
>> 1. 10 databases need to get replicated (with 30GB - 70GB each)
>> 2. these 10 databases will have schema changes monthly.
>> 3. data changes need to moved from standby to production server after
>> maintenance has been completed.
>> Please advise
>> Thanks
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

best solutions : sql server replication (Maintenance)

we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
Thanks
Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
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
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintenace
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.

> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>
|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.
|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/archives/12-Humpty-Dumpty-Sat-On-A-Wall...html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
sql

best solutions : sql server replication (Maintenance)

we are frequently running the maintenance tasks on production during that
databases are not accessible.
to avoid this we want to switch over to the standby server during maintenace
and after maintenance has been completed
switch back to primary server.During maintenance all data changes need to be
replicated back to production server.
To accomplish this I am planning to setup database replication from
producation database server to Standby server.
Please advise best solutions for this like microsoft trnsaction
replication,Goldengate replication, wansync replication.
I think microsoft trnsaction replication,Goldengate replication doesn't
support complex shema changes to get replicated.
Requirement :
1. 10 databases need to get replicated (with 30GB - 70GB each)
2. these 10 databases will have schema changes monthly.
3. data changes need to moved from standby to production server after
maintenance has been completed.
Please advise
ThanksI don't think that replication in the answer to your challenge.
What you probably want is a hot standby. Replication is not recommended
since you will have frequently schema changes.
I think you should investigate these solutions:
- (Transaction) Log shipping
- Cross linked San solution
- Clustering
Michel Gosen (MCDBA)
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||Use bi-directional transactional replication for this. You will need to stop
the distribution agents while running your optimizations.
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
"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||DKRReddy wrote:
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during maintena
ce
> and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
be
> replicated back to production server.
> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>
What sort of maintenance tasks are you performing?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> we are frequently running the maintenance tasks on production during that
> databases are not accessible.
> to avoid this we want to switch over to the standby server during
> maintenace and after maintenance has been completed
> switch back to primary server.During maintenance all data changes need to
> be replicated back to production server.
First I would want to know why your databases aren't available during the
maintenance periods.
Almost all maintenance steps can be done with the databases on-line.

> To accomplish this I am planning to setup database replication from
> producation database server to Standby server.
> Please advise best solutions for this like microsoft trnsaction
> replication,Goldengate replication, wansync replication.
> I think microsoft trnsaction replication,Goldengate replication doesn't
> support complex shema changes to get replicated.
> Requirement :
> 1. 10 databases need to get replicated (with 30GB - 70GB each)
> 2. these 10 databases will have schema changes monthly.
> 3. data changes need to moved from standby to production server after
> maintenance has been completed.
> Please advise
> Thanks
>|||During reindex, tables will be locked exclusively.This is main concern.
We have very huge tables , reindex will take hours.
SQL Server version is SQL Server 2000 SP4 on Windows 2003.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:uF8o$RTMHHA.536@.TK2MSFTNGP02.phx.gbl...
> "DKRReddy" <dkrreddy@.hotmail.com> wrote in message
> news:evVYWZJMHHA.3312@.TK2MSFTNGP03.phx.gbl...
> First I would want to know why your databases aren't available during the
> maintenance periods.
> Almost all maintenance steps can be done with the databases on-line.
>
>|||"DKRReddy" <dkrreddy@.hotmail.com> wrote in message
news:edrmFpaMHHA.5064@.TK2MSFTNGP04.phx.gbl...
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Hmm, have you tried using DBCC INDEXDEFRAG. I find for most cases this is
more than suffecient and it's an online operation which means you can avoid
the whole switchover, etc.|||DKRReddy wrote:
> During reindex, tables will be locked exclusively.This is main concern.
> We have very huge tables , reindex will take hours.
> SQL Server version is SQL Server 2000 SP4 on Windows 2003.
>
Are you just blindly rebuilding all indexes, or are you analyzing them
first to determine which ones actually "need" to be rebuilt? Rebuilding
an index that is minimally fragmented gains you nothing. You should be
checking the degree of fragmentation of each index, and rebuilding only
those that need it. Here's a script to get you started:
http://realsqlguy.com/serendipity/a...realsqlguy.com

Best scenario for SQL Server 7.0 replication in my situation?

Hi All,
What is the best scenario for seting-up database replication in my
situation?
I have two computers, each computer has...
-W2K, IIS5.0 Web server
-Cold Fusion 4.5 Web Application server
-SQL Server 7.0 database server
-Multihomed IP Addresses using Network Load Balancing
...If one computer goes down for any reason, Network Load Balancing
ensures that the other computer gets all the traffic (Network Load
Balancing is also supposed to split-up traffic between the two
computers, although I have not been able to create this behavior - all
the requests within a session seem to always go to computer #2, unless
it is switched-off, only then will the requests go to computer #1). The
"traffic" is Web requests to our Web site over HTTP and HTTPS.
I want to ensure that each database will "instantly" (or as close to
instantly as possible) take over if the other computer goes down. The
database synchronization needs to be concurrent with minimal latency.
For example, we are linked into Paypal's backend for accepting credit
card payments so we don't want a user to be able to "withdraw money
twice" because of a transaction record not being updated to the other
database.
What are some possible ways of acheiving this?
Thank You,
Nate
I would use a cluster to achieve this, as replication never works in both
directions with 'near to zero' latency.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the response. Do you mean clustering as in Windows
clustering in "Add/Remove Windows Components" or do you mean some other
clustering that I can setup through SQL Server 7.0?
Thanks Again,
Nate
|||Nate - this is exactly it. There are documents on the MS website and
sqlservercentral explaining how to set it up, but it's not for the
fainthearted, and depending on your background you might need a networking
guy to help get it set up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Sunday, March 25, 2012

Best Replication Type please

Hello,
We are implementing a high availablity solution to our SQL
Servers, so if our production server goes down we have a
standby server ready to go.
So we would like to know the best type of replication,
bascially we want to update the stand-by server every 5
minutes. We cannot use the log shipping as we backup the
transaction log every 10 minutes.
Personally I think we should be using snapshot
replication, does anyone have any major disagreements with
this ?
Thanks
PeterSnapshot replication would give you a complete copy of the publication ....
Probably some sort of incremental thing might be better... perhaps
transactional...
But be aware-- replication does NOT replicate system tables, so
permissions, etc will not be replicated..
Also, SQL Replication is not very friendly about Schema changes ,
adding/dropping columns, or changing data types...
For these reasons, may people use log shipping.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> Hello,
> We are implementing a high availablity solution to our SQL
> Servers, so if our production server goes down we have a
> standby server ready to go.
> So we would like to know the best type of replication,
> bascially we want to update the stand-by server every 5
> minutes. We cannot use the log shipping as we backup the
> transaction log every 10 minutes.
> Personally I think we should be using snapshot
> replication, does anyone have any major disagreements with
> this ?
> Thanks
> Peter
>|||Thanks Wayne,
I would love to use Log Shipping, but I'm assuming that it
produces the same internal result to the log file as a log
file backup, if that the case then the backup we have in
place will not work. This is somewhat of a 'legacy'
backup, and one I have been specifically told not to touch.
Again thanks for help and I will look into what you have
said.
Peter
>--Original Message--
>Snapshot replication would give you a complete copy of
the publication ....
>Probably some sort of incremental thing might be
better... perhaps
>transactional...
>But be aware-- replication does NOT replicate system
tables, so
>permissions, etc will not be replicated..
>Also, SQL Replication is not very friendly about Schema
changes ,
>adding/dropping columns, or changing data types...
>For these reasons, may people use log shipping.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> Hello,
>> We are implementing a high availablity solution to our
SQL
>> Servers, so if our production server goes down we have a
>> standby server ready to go.
>> So we would like to know the best type of replication,
>> bascially we want to update the stand-by server every 5
>> minutes. We cannot use the log shipping as we backup the
>> transaction log every 10 minutes.
>> Personally I think we should be using snapshot
>> replication, does anyone have any major disagreements
with
>> this ?
>> Thanks
>> Peter
>>
>
>.
>|||I suggest you read the two SQL Server HA books available at MS "Patterns and Practices" web site.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Peter" <anonymous@.discussions.microsoft.com> wrote in message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
> Thanks Wayne,
> I would love to use Log Shipping, but I'm assuming that it
> produces the same internal result to the log file as a log
> file backup, if that the case then the backup we have in
> place will not work. This is somewhat of a 'legacy'
> backup, and one I have been specifically told not to touch.
> Again thanks for help and I will look into what you have
> said.
> Peter
> >--Original Message--
> >Snapshot replication would give you a complete copy of
> the publication ....
> >Probably some sort of incremental thing might be
> better... perhaps
> >transactional...
> >
> >But be aware-- replication does NOT replicate system
> tables, so
> >permissions, etc will not be replicated..
> >
> >Also, SQL Replication is not very friendly about Schema
> changes ,
> >adding/dropping columns, or changing data types...
> >
> >For these reasons, may people use log shipping.
> >
> >--
> >Wayne Snyder, MCDBA, SQL Server MVP
> >Computer Education Services Corporation (CESC),
> Charlotte, NC
> >www.computeredservices.com
> >(Please respond only to the newsgroups.)
> >
> >I support the Professional Association of SQL Server
> (PASS) and it's
> >community of SQL Server professionals.
> >www.sqlpass.org
> >
> >
> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
> >> Hello,
> >>
> >> We are implementing a high availablity solution to our
> SQL
> >> Servers, so if our production server goes down we have a
> >> standby server ready to go.
> >>
> >> So we would like to know the best type of replication,
> >> bascially we want to update the stand-by server every 5
> >> minutes. We cannot use the log shipping as we backup the
> >> transaction log every 10 minutes.
> >>
> >> Personally I think we should be using snapshot
> >> replication, does anyone have any major disagreements
> with
> >> this ?
> >>
> >> Thanks
> >> Peter
> >>
> >>
> >
> >
> >.
> >|||Thanks Tibor
>--Original Message--
>I suggest you read the two SQL Server HA books available
at MS "Patterns and Practices" web site.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message news:81db01c431f4$729d28e0$a601280a@.phx.gbl...
>> Thanks Wayne,
>> I would love to use Log Shipping, but I'm assuming that
it
>> produces the same internal result to the log file as a
log
>> file backup, if that the case then the backup we have in
>> place will not work. This is somewhat of a 'legacy'
>> backup, and one I have been specifically told not to
touch.
>> Again thanks for help and I will look into what you have
>> said.
>> Peter
>> >--Original Message--
>> >Snapshot replication would give you a complete copy of
>> the publication ....
>> >Probably some sort of incremental thing might be
>> better... perhaps
>> >transactional...
>> >
>> >But be aware-- replication does NOT replicate system
>> tables, so
>> >permissions, etc will not be replicated..
>> >
>> >Also, SQL Replication is not very friendly about Schema
>> changes ,
>> >adding/dropping columns, or changing data types...
>> >
>> >For these reasons, may people use log shipping.
>> >
>> >--
>> >Wayne Snyder, MCDBA, SQL Server MVP
>> >Computer Education Services Corporation (CESC),
>> Charlotte, NC
>> >www.computeredservices.com
>> >(Please respond only to the newsgroups.)
>> >
>> >I support the Professional Association of SQL Server
>> (PASS) and it's
>> >community of SQL Server professionals.
>> >www.sqlpass.org
>> >
>> >
>> >"Peter" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:836e01c431f0$72b45730$a301280a@.phx.gbl...
>> >> Hello,
>> >>
>> >> We are implementing a high availablity solution to
our
>> SQL
>> >> Servers, so if our production server goes down we
have a
>> >> standby server ready to go.
>> >>
>> >> So we would like to know the best type of
replication,
>> >> bascially we want to update the stand-by server
every 5
>> >> minutes. We cannot use the log shipping as we backup
the
>> >> transaction log every 10 minutes.
>> >>
>> >> Personally I think we should be using snapshot
>> >> replication, does anyone have any major disagreements
>> with
>> >> this ?
>> >>
>> >> Thanks
>> >> Peter
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||If you are thinking of replication rather than clustering or log shipping, then transactional with queued updating subscribers may be used in this situation. Snapshot could be used but this depends on the size of your system - creating and sending over bcp files of your complete database every 5 mins might not be acceptable. By default, standard transactional will not take defaults and identity columns (although the schema can be scripted), so it's easier to use the queued updating subscribers option which also gives the ability to send back to the publisher any changes subsequently made to the subscriber, if the publisher becomes online in the future
Regards
Paul Ibison

Best Replication Type please

Best replication strategy suggestion?

We are in the process of determining a sql db replication strategy. We're
looking at merge replication or log shipping but are in need of more
informed suggestions. It seems like merge replication would be ideal except
i've read that in the event one of the sql servers is unavailable the other
stops accepting data until some elaborate reconfiguration is made. It also
appears that merge replication requires modifying the app tables - not sure
if the vendor will support. Log shipping seems to allow for an easier
reconfiguration of the secondary in the event of a publisher failure but
it's still not very automatic. I've listed the SLA and specifications. Any
ideas or experiences would be most appreciated.
1.) We have two locations and must have a sql server in each location.
2.) There will be a steady stream of inserts only (no updates or deletes) to
one or both sql servers.
3.) The same data must always reside on both servers (or no more than a
minute off)
4.) The application communicates to a virtual dns name that will point to
one or both sql servers via round robin or a load balancer depending upon
the replication strategy.
5.) We would like to load balance across both sql servers and have them
frequently update each others db (maybe merge repl?) but if one of the sql
servers goes down the other must continue functioning with little or know
manual reconfiguration (maybe not merge - maybe log shipping and just one
getting the inserts?).
Thanks,
Jim
Load balancing with SQL Server is extremely hard to do unless it is totally
read only. You are probably better off just having the inserts all go to
one DB and using something like Log shipping or Clustering (if the locations
are close enough) for redundancy. SQL 2005 offers another option called
Database Mirroring that may work in your situation as well.
Andrew J. Kelly SQL MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>
|||Hi Jim
Only having inserts probably simplifies things a lot (depending on the db
design).
Another option you might consider, assuming you've got your key generation
strategy worked out well, might be to split your tables into partitioned
views & set up two push transactional publications (one each way) to handle
the replication. The benefit from this implemantion would be that either
node could go down without breaking the system. If you ran them both in
continuous mode, you'd probably be able to meet your < 1 minute latency
requirement without much difficulty as well..
Cheers,
Greg Linwood
SQL Server MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>

Best replication strategy suggestion?

We are in the process of determining a sql db replication strategy. We're
looking at merge replication or log shipping but are in need of more
informed suggestions. It seems like merge replication would be ideal except
i've read that in the event one of the sql servers is unavailable the other
stops accepting data until some elaborate reconfiguration is made. It also
appears that merge replication requires modifying the app tables - not sure
if the vendor will support. Log shipping seems to allow for an easier
reconfiguration of the secondary in the event of a publisher failure but
it's still not very automatic. I've listed the SLA and specifications. Any
ideas or experiences would be most appreciated.
1.) We have two locations and must have a sql server in each location.
2.) There will be a steady stream of inserts only (no updates or deletes) to
one or both sql servers.
3.) The same data must always reside on both servers (or no more than a
minute off)
4.) The application communicates to a virtual dns name that will point to
one or both sql servers via round robin or a load balancer depending upon
the replication strategy.
5.) We would like to load balance across both sql servers and have them
frequently update each others db (maybe merge repl?) but if one of the sql
servers goes down the other must continue functioning with little or know
manual reconfiguration (maybe not merge - maybe log shipping and just one
getting the inserts?).
Thanks,
JimLoad balancing with SQL Server is extremely hard to do unless it is totally
read only. You are probably better off just having the inserts all go to
one DB and using something like Log shipping or Clustering (if the locations
are close enough) for redundancy. SQL 2005 offers another option called
Database Mirroring that may work in your situation as well.
Andrew J. Kelly SQL MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>|||Hi Jim
Only having inserts probably simplifies things a lot (depending on the db
design).
Another option you might consider, assuming you've got your key generation
strategy worked out well, might be to split your tables into partitioned
views & set up two push transactional publications (one each way) to handle
the replication. The benefit from this implemantion would be that either
node could go down without breaking the system. If you ran them both in
continuous mode, you'd probably be able to meet your < 1 minute latency
requirement without much difficulty as well..
Cheers,
Greg Linwood
SQL Server MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>

Best replication strategy suggestion?

We are in the process of determining a sql db replication strategy. We're
looking at merge replication or log shipping but are in need of more
informed suggestions. It seems like merge replication would be ideal except
i've read that in the event one of the sql servers is unavailable the other
stops accepting data until some elaborate reconfiguration is made. It also
appears that merge replication requires modifying the app tables - not sure
if the vendor will support. Log shipping seems to allow for an easier
reconfiguration of the secondary in the event of a publisher failure but
it's still not very automatic. I've listed the SLA and specifications. Any
ideas or experiences would be most appreciated.
1.) We have two locations and must have a sql server in each location.
2.) There will be a steady stream of inserts only (no updates or deletes) to
one or both sql servers.
3.) The same data must always reside on both servers (or no more than a
minute off)
4.) The application communicates to a virtual dns name that will point to
one or both sql servers via round robin or a load balancer depending upon
the replication strategy.
5.) We would like to load balance across both sql servers and have them
frequently update each others db (maybe merge repl?) but if one of the sql
servers goes down the other must continue functioning with little or know
manual reconfiguration (maybe not merge - maybe log shipping and just one
getting the inserts?).
Thanks,
JimLoad balancing with SQL Server is extremely hard to do unless it is totally
read only. You are probably better off just having the inserts all go to
one DB and using something like Log shipping or Clustering (if the locations
are close enough) for redundancy. SQL 2005 offers another option called
Database Mirroring that may work in your situation as well.
Andrew J. Kelly SQL MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>|||Hi Jim
Only having inserts probably simplifies things a lot (depending on the db
design).
Another option you might consider, assuming you've got your key generation
strategy worked out well, might be to split your tables into partitioned
views & set up two push transactional publications (one each way) to handle
the replication. The benefit from this implemantion would be that either
node could go down without breaking the system. If you ran them both in
continuous mode, you'd probably be able to meet your < 1 minute latency
requirement without much difficulty as well..
Cheers,
Greg Linwood
SQL Server MVP
"Jims" <biz@.neocasa.net> wrote in message
news:%23vRv7unfFHA.3692@.TK2MSFTNGP09.phx.gbl...
> We are in the process of determining a sql db replication strategy. We're
> looking at merge replication or log shipping but are in need of more
> informed suggestions. It seems like merge replication would be ideal
> except i've read that in the event one of the sql servers is unavailable
> the other stops accepting data until some elaborate reconfiguration is
> made. It also appears that merge replication requires modifying the app
> tables - not sure if the vendor will support. Log shipping seems to allow
> for an easier reconfiguration of the secondary in the event of a publisher
> failure but it's still not very automatic. I've listed the SLA and
> specifications. Any ideas or experiences would be most appreciated.
> 1.) We have two locations and must have a sql server in each location.
> 2.) There will be a steady stream of inserts only (no updates or deletes)
> to one or both sql servers.
> 3.) The same data must always reside on both servers (or no more than a
> minute off)
> 4.) The application communicates to a virtual dns name that will point to
> one or both sql servers via round robin or a load balancer depending upon
> the replication strategy.
> 5.) We would like to load balance across both sql servers and have them
> frequently update each others db (maybe merge repl?) but if one of the sql
> servers goes down the other must continue functioning with little or know
> manual reconfiguration (maybe not merge - maybe log shipping and just one
> getting the inserts?).
> Thanks,
> Jim
>

Best replication model?

I am looking for some advice/comments on what the best replication model
would be in the following situation:
We have a series of complex (slow) stored procedures that do lots of
calculations but produce relatively few changes in the database. These
stored procedures were written such that they process chunks of data
based on passed in parameters, and it is possible to run the same stored
procedure simultaneously with different parameters without them stepping
on each other's toes. The problem is that it is very CPU intensive.
We were thinking of using replication to replicate the data to several
servers and spreading the processing load between them. Then any
updates to the db produced would get replicated back to all the other
databases. All the databases must be in sync, but some latency is ok.
Would replication be appropriate in this situation? What is the best
replication model here? I was thinking Queued Updating Transactional.
Would welcome any thoughts/comments.
Thanks,
Alek
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I think you should look at extended stored procedures or even the CLR
functionality which ships with Yukon.
Queued Updating will probably work for this, as long as you have less than
10 subscribers, and the majority of your updates occur on your publisher.
The reason that most updates should occur on your publisher is to minimize
the possibilities of conflicts. If you can guarantee that you won't get
conflicts queued should work.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alek B" <developersdex.10.alekb@.spamgourmet.com> wrote in message
news:O8LrOkQ7EHA.2192@.TK2MSFTNGP14.phx.gbl...
>I am looking for some advice/comments on what the best replication model
> would be in the following situation:
> We have a series of complex (slow) stored procedures that do lots of
> calculations but produce relatively few changes in the database. These
> stored procedures were written such that they process chunks of data
> based on passed in parameters, and it is possible to run the same stored
> procedure simultaneously with different parameters without them stepping
> on each other's toes. The problem is that it is very CPU intensive.
> We were thinking of using replication to replicate the data to several
> servers and spreading the processing load between them. Then any
> updates to the db produced would get replicated back to all the other
> databases. All the databases must be in sync, but some latency is ok.
> Would replication be appropriate in this situation? What is the best
> replication model here? I was thinking Queued Updating Transactional.
> Would welcome any thoughts/comments.
> Thanks,
> Alek
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Best Replication Method to Use

We want to allow our customer base to be able to access their account
information online. I want to setup a second SQL server so the customers can
use this for looks up. The front end to access this info is web based.
What replication method is the best one to use to update the database say
every 24 hours at night? Thanks!!
if your database is not too large, a snapshot replication maybe best for
you.
else somekind of logshipping will be good too, see the other thread on
simple log shipping.
justin
Scopus69 wrote:
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||I think transactional replication would work for this. However this will
require each table you are replicating to have a primary key.
I am a little confused by the data flow. Are you saying data moves from the
web server SQL Server database to another SQL Server? Or is it moving
internally to the SQL Server supporting the web site.
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
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers
> can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||Sorry for the confusion. The GUI interface to the data is a web interface
that connects to the backend SQL server. What I would like to do is setup
another web & SQL server for our cutomers so they can use it for lookups. I
really don't want them in our prduction DB.
I was wondering what is the best way to get the data off the production SQL
server to the customer SQL server on a nightly basis? I don't think log
shipping will work because it will put the shipped DB in "read only"
So what method would be the best to use? Thanks!
"Hilary Cotter" wrote:

> I think transactional replication would work for this. However this will
> require each table you are replicating to have a primary key.
> I am a little confused by the data flow. Are you saying data moves from the
> web server SQL Server database to another SQL Server? Or is it moving
> internally to the SQL Server supporting the web site.
> --
> 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
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
>
>
|||I think transactional is your best bet.
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
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...[vbcol=seagreen]
> Sorry for the confusion. The GUI interface to the data is a web interface
> that connects to the backend SQL server. What I would like to do is
> setup
> another web & SQL server for our cutomers so they can use it for lookups.
> I
> really don't want them in our prduction DB.
> I was wondering what is the best way to get the data off the production
> SQL
> server to the customer SQL server on a nightly basis? I don't think log
> shipping will work because it will put the shipped DB in "read only"
> So what method would be the best to use? Thanks!
> "Hilary Cotter" wrote:
|||I also like Transactional Replication if the data is dynamic at the source
and the users who will be talking to your target server need updated
information as well for their lookups. If current data is not an issue, that
is they don't mind the data being static, then may be snapshot will work.
But then again it depends on how large the data is. For me one way
Transactional seems to fit the bill here.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:enpFYKCDGHA.1028@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I think transactional is your best bet.
> --
> 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
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...
interface[vbcol=seagreen]
lookups.[vbcol=seagreen]
will[vbcol=seagreen]
based.
>
sql

Best Replication For Me?

To add a column, you can use sp_repladdcolumn and to
remove, sp_repldropcolumn, and have a look at this
article for datatype changes:
http://www.replicationanswers.com/AddColumn.asp.
However, if your changes to the schema are often and
involve datatype changes, you might be better off
reinitializing, in which case this shouldn't affect the
type of replication you'll implement.
BTW, in SQL Server 2005 the Alter Table statement is
allowed on replicated tables within the context of
replication, so things become much easier.
As for changes to the developer side of things, this are
some simple comments off the top of my head: merge
replication and replication with updating subscribers
will generally add a guid column. In the case of merge it
might not if there is one already there with the rowguid
attribute. Apart from that, the trigger firing order can
be important in certain replication types, as again merge
and updating subscribers will add triggers to the
replicated table. Transactional replication will not
itselt change the publisher's tables, but there is a
schema requirement - the published table must have a PK,
so this might change your code. Finally, if your code
expects to work on the subscriber in exactly the same way
as the publisher, this might not work as in some cases
the schema is subtly altered, eg PKs become unique
indexes.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:046a01c53437$7c6b7570$a601280a@.phx.gbl...
> To add a column, you can use sp_repladdcolumn and to
> remove, sp_repldropcolumn, and have a look at this
> article for datatype changes:
> http://www.replicationanswers.com/AddColumn.asp.
> However, if your changes to the schema are often and
> involve datatype changes, you might be better off
> reinitializing, in which case this shouldn't affect the
> type of replication you'll implement.
>
Can you explain this in more detail? I don't follow how reinitializing
allows for the schema and datatype changes.
WB
|||It doesn't

Basically what I'm thinking is that the
process to change a datatype is longwinded, in terms of
the processing requirements, and there comes a point when
it would be less work to simply reinitialize. Perhaps
this point is 2 datatype changes - need to check?
Rgds,
Paul Ibison

Best Replication

There are ten DB Servers in live network. Out of 10 DB servers, one of them
has the database and the contents are replicated throughout. The size of
the database is about 20 GB and it is to be updated on weekly basis. The
time frame for replication is limited to 2 hours and all the data must be
updated from the Update DB server within 2 hours given.
What is the best data replication method on this scenario?
Jason,
transactional would be the fastest. Log shipping might be of alternative
interest. I'm assuming that the subscribers don't need to update the data.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Best Recovery plan for MergeReplication

hi all,
i established merge replication between two servers with no synch option.i
dont have any idea of how to restore it original is it fails.how can i
restore both publisher and subscriber with out any data loss.what is the
recovery plan i have to use.
your help is appreciated
thanks
reddy
thanks paul,
my doubt is the replication is stopped without synchronising the data from
publisher to subscriber.the data is available at publisher and not in
subscriber.in this case which steps i have to follow.just i can take a backup
of publisher and restore it in the original and start connecting to
subscriber.is it works.please help
thanks
reddy
"Paul Ibison" wrote:

> Reddy,
> you can backup your publisher, distributor and
> subscribers. The recovery mode is not important in the
> sense that any one will work for replication, and the
> usual considerations apply - eg if you want a more
> granular backup strategy, full recovery mode and
> transaction log backups are used.
> After you restore a publisher's backup, depending on your
> situation, you might want to reinitialize or you may want
> to synchronize your publication database immediately with
> a subscription database that has the latest data, and
> attempt to recover any changes synchronized with that
> replica but not included in the most recent publication
> database backup of publication database transaction log
> backup.
> Assuming you haven't changed the replication
> configuration, you don't need to restore the distribution
> database to a consistent point in time.
> BTW BOL has a good section on this : Strategies for
> Backing Up and Restoring Merge Replication.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Reddy,
you mention that 'replication is stopped'. What error
message do you receive when you run the merge agent. This
is the crucial bit and it may not be necessary/helpful to
restore a backup.
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Tuesday, March 20, 2012

Best Practices for Changing Merge Replication Server

We are planning on moving some SQL Server databases that are currenlty
running Merge replication as the publisher and distributor. The existing
server will eventually be scrapped and the new server will take over as the
new Publisher/Distributor. The current server is replication to a subscriber
that is also our 24/7 website SQL Server, and cannot be interrupted, and it
is imperative that no transactions on the website server be lost. Can anyone
provide any insight or articles that outline the best approach to take while
doing this migration?
Regards,
With merge replication transactions can be and frequently are rolled back.
So from the start your topology does not make a lot of sense.
If you want to minimize data loss in your situation you would be better off
to make your web server the publisher and configure it with anonymous
subscribers. This way you can drop a subscriber or take it offline and bring
it back on line or redeploy the subscription to a new server with little
impact on the publisher (now the web server). Still you have the possibility
of data loss.
With your current topology there is no way to do this without disruption
your web server/subscriber.
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
"Frank" <Frank@.discussions.microsoft.com> wrote in message
news:075DE1AF-FC0D-4423-8548-E8CCC744558B@.microsoft.com...
> We are planning on moving some SQL Server databases that are currenlty
> running Merge replication as the publisher and distributor. The existing
> server will eventually be scrapped and the new server will take over as
the
> new Publisher/Distributor. The current server is replication to a
subscriber
> that is also our 24/7 website SQL Server, and cannot be interrupted, and
it
> is imperative that no transactions on the website server be lost. Can
anyone
> provide any insight or articles that outline the best approach to take
while
> doing this migration?
> Regards,

Best Practices

In short, I am looking for a step by step best practice for making
database changes to a database that is using merge replication between
multiple locations.
Background information:
Using SQL 2000, I have a publisher and 3 subscribers of a very large
database. The database is in use 24/7/365. The entire database is
replicated.
In the next release of the application I need to update views, add
fields to certain tables, add entirely new tables, constraints, and
indexes.
Is there a way to implement these changes at the publisher and have it
update the subscribers or at least update the publication information
as the new tables will need to be replicated as well and I want to make
sure all associated rowguids, triggers, etc. are created internally for
replication.
Pls have a look at sp_repladdcolumn and sp_repldropcolumn in BOL. Also there
is my article on making changes to an existing column:
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||To replicate schema only objects (like views, functions, stored procedures,
etc) use snapshot replication.
If you use sp_addmergearticle to add new articles (tables) to your
publication a snapshot of all of your tables will be generated. If you can
use a separate publication for these new articles.
Otherwise other schema changes can be performed by using sp_repladdcolumn
and sp_repldropcolumn. These procs are limited in what they can do, so you
might find yourself having to recreate the publications in some cases.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Steve B" <SBaxter.RBS@.gmail.com> wrote in message
news:1162949137.394153.305500@.k70g2000cwa.googlegr oups.com...
> In short, I am looking for a step by step best practice for making
> database changes to a database that is using merge replication between
> multiple locations.
> Background information:
> Using SQL 2000, I have a publisher and 3 subscribers of a very large
> database. The database is in use 24/7/365. The entire database is
> replicated.
> In the next release of the application I need to update views, add
> fields to certain tables, add entirely new tables, constraints, and
> indexes.
> Is there a way to implement these changes at the publisher and have it
> update the subscribers or at least update the publication information
> as the new tables will need to be replicated as well and I want to make
> sure all associated rowguids, triggers, etc. are created internally for
> replication.
>

Thursday, March 8, 2012

Best Practice & Other

Hi All
I have the following replication setup:
Replication Type: Transactional
Database Size: Circa 35gb
Articles: All articles are published and are required to be at the
subscriber.
Server1: Publisher, SQL 2000 Sp3 (W2k3 sp1)
Server2: Distributor, SQL 2000 Sp4 (W2k3 R2 sp1)
Server3: Subscriber SQL 2000 Sp3 (W2k3 sp1)
Server 3 has the pull subscription.
Other Info: Server 2 did have SQL2005 installed. It's since been
uninstalled and resolved some transactional issues.
This is the environment that I have inherited.
Based on reading around, this appears to be an acceptable best
practice method. I might press against throwing SP4 on server 1 and
server 3.
Are there any amazing troubleshooting tips for this process around?
there are times when the transactional replication does not work. I
think it times out so I'm looking at changing the time out to 3000
seconds.
One issue I've found is that there was a duplicated transaction that
managed to get through. This crashed the envrionment and there was no
choice but to create a new snapshot - it takes hours. How could we
best avoid this? If we deleted these transactions from the subscriber
(difficult due to referential integrity) how would the system know to
replicate them again?
Another one that we've had is where the old transactions are held in
the log. I need to flush them out so that the log can be truncated and
then reduced in size. Any further hints and tips?
I'm simply after some good practice methods to help troubleshoot and
plan the replication process since we're investing time and resource
in it rather heavily. We'll move to 2005 once things are stable.
Thanks in advance for the help.
Simon
Hi Paul
Thanks for the advice. Can you think of any really good
troubleshooting methods when things are gone wrong. I don't mind how
generic they are, it's just good to excercise the brain on new ways of
handling problems.
Cheers
Simon

Tuesday, March 27, 2012

Best technique for Replication

best solutions : sql server replication (Maintenance)

best solutions : sql server replication (Maintenance)

best solutions : sql server replication (Maintenance)

best solutions : sql server replication (Maintenance)

Best scenario for SQL Server 7.0 replication in my situation?

Sunday, March 25, 2012

Best Replication Type please

Best Replication Type please

Best Replication Type please

Best replication strategy suggestion?

Best replication strategy suggestion?

Best replication strategy suggestion?

Best replication model?

Best Replication Method to Use

Best Replication For Me?

Best Replication

Best Recovery plan for MergeReplication

Tuesday, March 20, 2012

Best Practices for Changing Merge Replication Server

Best Practices

Thursday, March 8, 2012

Best Practice & Other

BEFORE Triggers

Blog Archive

About Me