Replication Design, Comments?

Ok, I think I have a working solution. I've worked the past
few days finding a lot of issues (not replication related) which
caused my headaches with replication, ie the @.@.servername was
null, and when creating a stored procedure snapshot it
would complain about unqualified inserts. Learned a neat
trick from Paul on how to do verbose logging. The developers
are looking at their stored procedures.
Merge Replication will be used.
I found that they had used identity columns as primary Key values.
Changed the columns to be 'not for replication'.
Tommorow I will be finding out if they have any DRI (Foreign Key
constraints that need to be marked 'not for replication'.
So far, I've created a Merge publication for the data, and it
appears to be functional.
I have also setup a snapshot publication for the stored procedures.
In addition to having the unqualifed insert issue I made the
mistake of checking 'all sp' instead of just selecting the
stored procedures they created (i was adding the replication
added routines by mistake).
If down the road they change a stored procedure, can I just
reinitialize this subscription to get the up-to-date routines?
I am replicating these because the standby server will be
the live server in case of the primary going offline.
I set the schedule on this subscription(sp) to only run
on demand.
Both machines are on the same backbone, so Bandwidth isn't
an issue.
Since I just found out about the possbible Foreign Key
issue (already created a snapshot, but not the subscription)
I am assuming that I will need to re-do the publication once
the foreign key constraints are marked 'not for replication'?
Thanks for the help. It has been an educational week.
Dave
You will have to drop the pub in order to change it to "not for replication"
(at least if using EM).
"David Gresham" <gresham@.panix.com> wrote in message
news:d5ruoa$6ms$1@.reader1.panix.com...
.....
> Since I just found out about the possbible Foreign Key
> issue (already created a snapshot, but not the subscription)
> I am assuming that I will need to re-do the publication once
> the foreign key constraints are marked 'not for replication'?
|||David,
on the changes to stored procedure point, I'd recommend using transactional
replication, which'll then give you the posibility of using sp_addscriptexec
for a change to a specific SP - and will avoid the need to reinitialize all
procedures.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication design issue.

Hi, I need opinion for my system that I consider replication.
Our company has about 300 stores and each store has Sql-Server DB, called TimeDB.
Each store TimeDB contains their own data only.
And main office has Sql-server DB called TimeDBHQ which contains all stores' data that are posted from all the stores.
Most of time, store post their data to TimeDBHQ in daily base by application. And TimeDBHQ distribute policy data and modified data to stores corresponding the data.

All data transfer are handled by application.
But, I consider Replication for the periodical transfer and consider application as preserved way.

The issue I considered first time when I designed the system without using replication is that there are too many subscriber/distributros.
I use replication for several servers, about 3-5, which is very small number comparing to 300 or more.

Here is my question,
Is it good that I use replication for my system?
because replication made by MS is more stable and bug free than my application.
and My first hasitation is on whether replication works stable among more than 300 servers..

If anyone has experiance with this issue please post any idea,
It will be big help!!!

thank you..What compamy?

Even at 10k a licence, that's huge...

You sure we're talking sql server?|||we use sql server personal editon on store side.
anyway..
please consern the issue of the design.

Replication design

Hi,
I have the following scenario, and need to configure
replication for this. Pls advice.
We have 3 SQL server databases, at 3 locations on SQL
server 2000 servers. They are connected over WAN by 2
MBPS link (triangle). I have to configure replication on
this. Database has around 200 tables, and size of
database would be around 200-500 MB each. Changes would
be minor on daily basis.
I have these servers at following locations.
Loc1 (HeadOffice)
Loc2 (branch office)
Loc3 (branch office)
Data has to replicate from Loc2 and Loc3 to Loc1 at short
intervals during the day.
Somewhere in midnight (off peak hours), data has to
replicate from Loc1 to Loc2 and Loc3 (for changes made at
Loc1 to be reflected in Loc2 and Loc3).
These database are modified only during the day, and
number of users at each location will not exceed 30.
I have following queries
- Can I configure Transactional replication for Loc2 ->
Loc1 and Loc3 -> Loc1 to replicate at short intervals.
And additionally Snapshot to happen at midnight from
Loc1 -> Loc2 and Loc3.
Or do I require Merge replication for this scenario. (or
any other solution you think would suit this requirement)
Pls suggest.
Thanx,
Shrikant,
the easiest scenario would be mege replication. Snapshot will copy over the
whole tables and unless the tables are small or all rows are always updated,
this is not recommended, and you'd have to set up replication each time
after the snapshot - possible but not so nice. Also, if the data is
partitioned then great, but if not then merge will take care of that with
the conflict resolvers. The only thing is that you won't be able to easily
prevent the download of data from Loc1 until the evening, as these changes
will propagate during the frequent synchronizations. If this is not at all
desirable, than you might have to consider a more radical solution - you
could have Loc1 as the publisher but use unidirectional merge with the
subscribers and reinitialize each evening.
HTH,
Paul Ibison
|||Hi Paul,
Thanx for your reply.
But would not be using the bi-directional transactional
replication solve this ?
Loc1 acts as Publisher with Loc2 and Loc3 as subscribers.
And then Loc2 and Loc3 as Publishers, and Loc1 as
Subscriber ?
regds,
Shrikant

>--Original Message--
>Shrikant,
>the easiest scenario would be mege replication. Snapshot
will copy over the
>whole tables and unless the tables are small or all rows
are always updated,
>this is not recommended, and you'd have to set up
replication each time
>after the snapshot - possible but not so nice. Also, if
the data is
>partitioned then great, but if not then merge will take
care of that with
>the conflict resolvers. The only thing is that you won't
be able to easily
>prevent the download of data from Loc1 until the
evening, as these changes
>will propagate during the frequent synchronizations. If
this is not at all
>desirable, than you might have to consider a more
radical solution - you
>could have Loc1 as the publisher but use unidirectional
merge with the
>subscribers and reinitialize each evening.
>HTH,
>Paul Ibison
>
>.
>
|||Shrikant,
this would be an option. If you expect conflicts then it can become terribly complicated, and the initial setup is not straightforward, but you're right - it is in some ways a simpler solution. To You might want to have a look at the most recent issue of
SQL Server magazine if you're thinking of going down this route - there is a nice article on setting it up.
Regards,
Paul Ibison

Replication design

Hi,
I'm new to MSSQL replication, and was hoping for some advice as to
recommended practices for my scenario.
I will have a 4 SQL server scenario at four locations connected through a
TCP/IP wan (i.e. each site is connected to the internet).
One of the sites is the headquarters site. Some tables are only updated
at the headquarter, and these changes should replicate to the other sites.
For example the table that contains the list of all products is only updated
at the main site.
Each site (including the headquarter) inserts many transactions within the
day. I wish for all the transactions to replicate to all sites. For
example, if I insert a transaction at site A, I want this transaction to
replicate to site B, site C, and headquarters as well. The same goes for
all sites. Ideally this should happen within an hour from when the
transaction was entered.
The databases will be about 10-15GB each I think.
Any comments or suggestions appreciated,
Edgard
Edgard,
for those tables which are only updated at HO, I'd recommend transactional
replication.
For all other tables I'd recommend merge.
One hour's latency should not be a problem, provided you have the necessary
bandwidth and the amount of changes is not too great.
Regards,
Paul Ibison

Replication Best Practice Question: Split Publications

I am working on a replication design and getting closer to implementation. One of my major concerns is maintaining and updating stored procs and/or user defined functions.

The current design is a single publication, including tables, procs and functions (no views yet). All told there are about 686 articles in the publication. The tables are horizontally partitioned using dynamic filters based on the hostname of the subscriber. There are around 50 subscribers. Most will have small databases (< 250 MB). A few (2-3) will have much larger databases. I am mostly concerned that whenever I have to update a proc or function, I have to re-initialize the subscriptions and that pushes a TON of data out over the network and may interrupt service at the subscriber locations.

I see three options for the procs and functions:
1. Include them in the publication with the data tables
2. Place them in a separate (snapshot only) publication
3. Exclude them entirely from replication and maintain them manually

I am starting to lean towards option #2; but I am a bit concerned about maintaining a duplicate set of replication agents for each subscriber.

Any thoughts and/or comments?

Regards,

hmscott

Am I overlooking something? Is there an option that I have not considered?I maintain them manually. All replication does is replace procs, and I can do that without involving replication.|||I maintain them manually. All replication does is replace procs, and I can do that without involving replication.

How many subscribers?|||Ok - I don't really feel qualified on this subject and don't have that much related experience (I use replication to get disparate data sources into one single, read only point rather than the other way round) however you aren't getting much joy so I might as well wade in as not.

I have read a couple of articles that advocate an approach very like point 2. One, as I recall, suggested one publication per article type which does seem a bit over the top. Anway - although this would require administration of more agents it sounds preferable to administering everything manually.

Can I find my sources? Can I buggery - you will just have to take my word for it that I have read at least two articles that suggest this very set up. I will continue hunting through my favourites.|||Pootle -

Thanks for your comments. I'm going to experiment a bit with this approach in the next fiew days.

Is it me, or does it seem that there's a dearth of information on the net regarding SQL replication? I have found a few sites (http://www.replicationanswers.com/Default.asp, http://www.dyessconsulting.com, and a few articles in http://www.sqlservercentral.com), but it just seems that there's not a whole lot of "hard" information (ie, mostly it's a lot of generic stuff with few practical examples).

Thanks again, I'll try to remember to post back with an update as things progress.

Regards,

hmscott|||That is my experience too. I believe Hilary Cotter is extremely knowledgeable and also active on some forum(s) if you really get no joy here.

Replication Architecture: Design Issue and Validation

I would like some validation on a replication architecture.
Business Context:
We are deploying a custom .NET 2 application for an retail/distribution
operation with 10 locations. The remote locations must have the ability to
continue to operate even if the central site is unavailable (these are mostly
rural locations).
Replication Requirements:
1. Corporate objects must be available to the remote locations as soon as
possible (e.g., purchase orders so that remote can receive against PO and
match line items).
2. Remote objects must be available to the corporate office and then made
available to the other remote locations as soon as possible (e.g., inventory
transfer "out").
3. All objects will be periodically processed by corporate with objects
produced via process sent to the remote locations (e.g., inventory count
checkpoints).
4. As much as possible the model should allow for a) schema changes given
that this is a v1; and b) minimal maintenance given that of 5 I.T. staff only
1 is a DBA.
Replication Architecture
A Corporate Objects shall be published to remote locations with a merge push
subscription
B Remote location Objects shall be published to the corporate office with a
merge pull subscription and then re-published to the other remote locations
via a push merge
I have a reasonable amount of SQL experience but am new to replication. I
am looking for the following feedback: a) does this design meet the business
context and application requirements; b) how do I make sure that the remote
location transactions which come in to corporate go out to the other
locations.
Thanks
Philip Neufeld, MCSE
I think you can get away with a single merge publication. If your remote
locations are well connected use push. What will happen is if branch 1 makes
a change it will go to the central location and from there be sent to all
other branches who need that copy. You can filter the data using horizontal
partitioning or row filters if you need to.
If you are using SQL 2005 schema changes can be replicated from the central
office to the branch offices.
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
"Philip Neufeld" <PhilipNeufeld@.discussions.microsoft.com> wrote in message
news:D255F0EE-E958-4350-937F-4562AABA3C5C@.microsoft.com...
>I would like some validation on a replication architecture.
> Business Context:
> We are deploying a custom .NET 2 application for an retail/distribution
> operation with 10 locations. The remote locations must have the ability
> to
> continue to operate even if the central site is unavailable (these are
> mostly
> rural locations).
> Replication Requirements:
> 1. Corporate objects must be available to the remote locations as soon as
> possible (e.g., purchase orders so that remote can receive against PO and
> match line items).
> 2. Remote objects must be available to the corporate office and then made
> available to the other remote locations as soon as possible (e.g.,
> inventory
> transfer "out").
> 3. All objects will be periodically processed by corporate with objects
> produced via process sent to the remote locations (e.g., inventory count
> checkpoints).
> 4. As much as possible the model should allow for a) schema changes given
> that this is a v1; and b) minimal maintenance given that of 5 I.T. staff
> only
> 1 is a DBA.
> Replication Architecture
> A Corporate Objects shall be published to remote locations with a merge
> push
> subscription
> B Remote location Objects shall be published to the corporate office with
> a
> merge pull subscription and then re-published to the other remote
> locations
> via a push merge
> I have a reasonable amount of SQL experience but am new to replication. I
> am looking for the following feedback: a) does this design meet the
> business
> context and application requirements; b) how do I make sure that the
> remote
> location transactions which come in to corporate go out to the other
> locations.
> Thanks
> Philip Neufeld, MCSE

Replication Architecture/Design advise

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.
"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>,
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...
>
|||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...b;en-us;820675
"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:
> 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
>
>
|||In article <eOqh7CmTFHA.544@.TK2MSFTNGP15.phx.gbl>,
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...b;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
sql

Replicating to over 70 databases...why??

Is this reasonable or is this poor design...why not just a few power servers
and all users have high speed internet connections or at least DSL'
Thanks
Ken S.Think you will have to explain your situation a little more to get any good
feedback
Ray Higdon MCSE, MCDBA, CCNA
--
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:O1ct4n9AEHA.2404@.TK2MSFTNGP11.phx.gbl...
> Is this reasonable or is this poor design...why not just a few power
servers
> and all users have high speed internet connections or at least DSL'
> Thanks
> Ken S.
>

Replicating to over 70 databases...why??

Is this reasonable or is this poor design...why not just a few power servers
and all users have high speed internet connections or at least DSL'
Thanks
Ken S.Think you will have to explain your situation a little more to get any good
feedback
--
Ray Higdon MCSE, MCDBA, CCNA
--
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:O1ct4n9AEHA.2404@.TK2MSFTNGP11.phx.gbl...
> Is this reasonable or is this poor design...why not just a few power
servers
> and all users have high speed internet connections or at least DSL'
> Thanks
> Ken S.
>