|
|
You're in the
sql server replication
group:Replication Architecture/Design advise
sql server replication:
I have been asked to set-up replication in the current two server
enviornment. My understanding is that Log-Shipping requires the Enterprise version of Sql Server 2000 (Which they do not have the budget for). They have 70 Databases that have a total space of aprox 30gb. They want to replicate the data to the standby server so that in the case of a failure they can redirect their Web Sites to the standby Server. Both servers will be in a co-lo facility so bandwidth between the servers should be adequate. Do I do central publisher with the distributor on the standby server? I am not sure what kind of load this will have. I understand that people recommend to put it on the publisher server and only move it if load becomes an issue. I understand that security (ie, users and permissions) will not replicate. Other than having to set things up twice is their other solutions to minimize this? Sincerely, Dave
you do not need the Enterprise Edition of SQL Server for log shipping. This
is a mistake in BOL. Ideally what you want is automatic failover which means hardware or software data mirroring - however it can be accomplished using log shipping, but you will have to redirect your clients to the failed over server. You can then log ship back to the original primary. If you choose to use replication - I would use bi-directional transactional replication keeping in mind 1) that every table you are replicating needs a pk 2) system objects and logins are not replicated 3) bi-directional transactional replication is not resilient to schema changes (i.e. you have to drop your subscriptions and publication when you want to make schema changes). Other replication types support partial schema changes using sp_repladdcolumn and sp_repldropcolumn. For log shipping you will find that 1) its not really scalable - I think you will find the logistical burden log shipping 70 databases to be a bit overwhelming 2) your standby servers' databases will be read only For replication you would be using a single publisher and subscriber. It is a central publisher with one subscriber. [quoted text, click to view] "David Gresham" <gresham@panix.com> wrote in message news:d50tct$5je$1@reader1.panix.com... >I have been asked to set-up replication in the current two server > > enviornment. My understanding is that Log-Shipping requires > > the Enterprise version of Sql Server 2000 (Which they do not > > have the budget for). > > > > They have 70 Databases that have a total space of aprox 30gb. > > They want to replicate the data to the standby server so that > > in the case of a failure they can redirect their Web Sites > > to the standby Server. > > > > Both servers will be in a co-lo facility so bandwidth between > > the servers should be adequate. > > > > Do I do central publisher with the distributor on the standby > > server? I am not sure what kind of load this will have. I > > understand that people recommend to put it on the publisher > > server and only move it if load becomes an issue. > > > > I understand that security (ie, users and permissions) will > > not replicate. Other than having to set things up twice is > > their other solutions to minimize this? > > > > Sincerely, > > > > > > Dave > >
In article <umaTbQdTFHA.2172@tk2msftngp13.phx.gbl>,
[quoted text, click to view] Hilary Cotter <hilary.cotter@gmail.com> wrote: Can you expand on what replication model this is? Is this Merge?>you do not need the Enterprise Edition of SQL Server for log shipping. This >is a mistake in BOL. > >Ideally what you want is automatic failover which means hardware or software >data mirroring - however it can be accomplished using log shipping, but you >will have to redirect your clients to the failed over server. You can then >log ship back to the original primary. > >If you choose to use replication - I would use bi-directional transactional >replication keeping in mind >1) that every table you are replicating needs a pk >2) system objects and logins are not replicated >3) bi-directional transactional replication is not resilient to schema >changes (i.e. you have to drop your subscriptions and publication when you >want to make schema changes). Other replication types support partial schema >changes using sp_repladdcolumn and sp_repldropcolumn. > Does this mean that the standby server can update the primary? My situation for this project is that the primary will update the standby server and will only be used when the primary fails. My other question is: Lets say we need to rebuild the primary server. We re-build it and need to bring the databases back from the standby server. Do we have to start over from scratch? Or do we need to some-how rebuild everything? I am going to be ordering your book today. I have worked with replication before when all we were doing was creating a reporting database which was read-only. The standby concept seemed to follow this except for when we needed to switch over to it. The client hosts several web sites. Thanks Dave [quoted text, click to view] >For log shipping you will find that >1) its not really scalable - I think you will find the logistical burden log >shipping 70 databases to be a bit overwhelming >2) your standby servers' databases will be read only > >For replication you would be using a single publisher and subscriber. It is >a central publisher with one subscriber. > >"David Gresham" <gresham@panix.com> wrote in message >news:d50tct$5je$1@reader1.panix.com... >>I have been asked to set-up replication in the current two server >> >> enviornment. My understanding is that Log-Shipping requires >> >> the Enterprise version of Sql Server 2000 (Which they do not >> >> have the budget for). >> >> >> >> They have 70 Databases that have a total space of aprox 30gb. >> >> They want to replicate the data to the standby server so that >> >> in the case of a failure they can redirect their Web Sites >> >> to the standby Server. >> >> >> >> Both servers will be in a co-lo facility so bandwidth between >> >> the servers should be adequate. >> >> >> >> Do I do central publisher with the distributor on the standby >> >> server? I am not sure what kind of load this will have. I >> >> understand that people recommend to put it on the publisher >> >> server and only move it if load becomes an issue. >> >> >> >> I understand that security (ie, users and permissions) will >> >> not replicate. Other than having to set things up twice is >> >> their other solutions to minimize this? >> >> >> >> Sincerely, >> >> >> >> >> >> Dave >> >> > >
In article <eOqh7CmTFHA.544@TK2MSFTNGP15.phx.gbl>,
[quoted text, click to view] Hilary Cotter <hilary.cotter@gmail.com> wrote: >This is transactional, bi-directional transactional replication to be >specific, not to be confused with transactional replication with immediate >updating, transactional replication with queued updating, transactional >replication with immediate updating with queued failover, or merge >replication. However merge replication might work for you, although it will >add a guid column to each column you are replicating. > >It does mean that your standby could update the primary, and it is an >excellent choice when the primary goes offline and all database activity >occurs on the standby server then. > >Ideally you would use bi-directional replication in a case where your >publisher goes offline for an extended time period. When it comes back >online you should backup the standby by restore it on the publisher, >recreate the publications and subscriptions on the publisher and then do a >no sync, > >Review this link for more info on these options. > >http://support.microsoft.com/default.aspx?scid=kb;en-us;820675 > In reading this document I got a 'little' intimidated by the need for all these custom stored procedures. Since the server will only be used in such a way that there would never be any conflicts do I need to worry about this? I also wanted to clarify where the distributor is in this set-up, the Subscriber? Thanks Dave
This is transactional, bi-directional transactional replication to be
specific, not to be confused with transactional replication with immediate updating, transactional replication with queued updating, transactional replication with immediate updating with queued failover, or merge replication. However merge replication might work for you, although it will add a guid column to each column you are replicating. It does mean that your standby could update the primary, and it is an excellent choice when the primary goes offline and all database activity occurs on the standby server then. Ideally you would use bi-directional replication in a case where your publisher goes offline for an extended time period. When it comes back online you should backup the standby by restore it on the publisher, recreate the publications and subscriptions on the publisher and then do a no sync, Review this link for more info on these options. http://support.microsoft.com/default.aspx?scid=kb;en-us;820675 [quoted text, click to view] "David Gresham" <gresham@panix.com> wrote in message news:d52qja$4j4$1@reader1.panix.com... > In article <umaTbQdTFHA.2172@tk2msftngp13.phx.gbl>, > Hilary Cotter <hilary.cotter@gmail.com> wrote: >>you do not need the Enterprise Edition of SQL Server for log shipping. >>This >>is a mistake in BOL. >> >>Ideally what you want is automatic failover which means hardware or >>software >>data mirroring - however it can be accomplished using log shipping, but >>you >>will have to redirect your clients to the failed over server. You can then >>log ship back to the original primary. >> >>If you choose to use replication - I would use bi-directional >>transactional >>replication keeping in mind >>1) that every table you are replicating needs a pk >>2) system objects and logins are not replicated >>3) bi-directional transactional replication is not resilient to schema >>changes (i.e. you have to drop your subscriptions and publication when you >>want to make schema changes). Other replication types support partial >>schema >>changes using sp_repladdcolumn and sp_repldropcolumn. >> > Can you expand on what replication model this is? Is this Merge? > Does this mean that the standby server can update the primary? > My situation for this project is that the primary will update > the standby server and will only be used when the primary fails. > > My other question is: Lets say we need to rebuild the primary > server. We re-build it and need to bring the databases back > from the standby server. Do we have to start over from scratch? > > Or do we need to some-how rebuild everything? I am going to > be ordering your book today. I have worked with replication > before when all we were doing was creating a reporting database > which was read-only. > > The standby concept seemed to follow this except for when we > needed to switch over to it. The client hosts several > web sites. > > Thanks > > > Dave > >>For log shipping you will find that >>1) its not really scalable - I think you will find the logistical burden >>log >>shipping 70 databases to be a bit overwhelming >>2) your standby servers' databases will be read only >> >>For replication you would be using a single publisher and subscriber. It >>is >>a central publisher with one subscriber. >> >>"David Gresham" <gresham@panix.com> wrote in message >>news:d50tct$5je$1@reader1.panix.com... >>>I have been asked to set-up replication in the current two server >>> >>> enviornment. My understanding is that Log-Shipping requires >>> >>> the Enterprise version of Sql Server 2000 (Which they do not >>> >>> have the budget for). >>> >>> >>> >>> They have 70 Databases that have a total space of aprox 30gb. >>> >>> They want to replicate the data to the standby server so that >>> >>> in the case of a failure they can redirect their Web Sites >>> >>> to the standby Server. >>> >>> >>> >>> Both servers will be in a co-lo facility so bandwidth between >>> >>> the servers should be adequate. >>> >>> >>> >>> Do I do central publisher with the distributor on the standby >>> >>> server? I am not sure what kind of load this will have. I >>> >>> understand that people recommend to put it on the publisher >>> >>> server and only move it if load becomes an issue. >>> >>> >>> >>> I understand that security (ie, users and permissions) will >>> >>> not replicate. Other than having to set things up twice is >>> >>> their other solutions to minimize this? >>> >>> >>> >>> Sincerely, >>> >>> >>> >>> >>> >>> Dave >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |