Wednesday, March 28, 2012
Replication error
I have 2BD which is installed on 2 serv #, they have same architecture (the same table, relation diagrams) I need 2rplications:
sens1=> BD1 (table1, table2) to BD2 (table1, table2)
sens2=> BD2 (table3, table4) to BD1 (table3, table4)
problem: when I want to retort one or 2 table of the BD1=>BD2
The diagram that I had on BD2 is crushed in other words the whole of the relation are re-initialized.
I found an explanation: when I notch (yes to initialize diagram and data) in the assistant of extraction of subscription the problem is to announce but the replication that passes but not as it is needed,
if not =>when I notch (Not the subscriber has already of the diagram and the data) another message is announced Impossible to find the procedure stored sp_MSupd_table1. is the replication is blocked,
ideas? thank you in advanceThe procedures mentioned above are generated by the subscription. In order to create them in your database, try this, although I can not guarantee this will work:
1) create a dummy database with no tables.
2) subscribe to the publication with this database.
3) Copy the procedures generated in the dummy database to your real subscriber.
4) Copy all the published data from the publisher to the subscriber.
5) Create the subscription with the "subscriber has already of the diagram and the data" checkbox checked.
This should get you through, but I have never tried it, so I can not be certain.
Wednesday, March 21, 2012
Replication Architecture: Design Issue and Validation
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
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
Replication Architecture question - Pull subscriptions on a SQL cluster...
Greetings:
I am working on a replication setup using transactional replication using with pull subscriptions and a separate distributor. The pull subscriptions are located on a SQL cluster using the virtual SQL Server name as the subscriber; when the box fails over, we get an error of missing replication.dlls. Researching further, we found that replication only works on one node of the subscriber.
Any ideas on what we did wrong in the setup?
Thanks,
Lee Everest
www.texas2oo.com/sqlblog
can you check the c:\program files\Microsoft Sql Server\80\com folder to see if the dll's exist there? If not try to copy and register them on the failing node.|||
We could do that, but i'm afraid that after I try to install one, several (or dozens) of others might show up missing, meaning that we didn't install it properly in the first place. The specific error that we get is:
Cannot load the DLL replsetoriginator extended procedure, or one of the DLLs it references. Reason: 126(The specified module could not be found.). The step failed.
I have no idea which DLL this function is located but could probably find out.
|||This is a missing or corrupt dll. When I have encountered these in SQL FTS clusters I have had to copy the dll's to the failed not and reregister them.|||So you are saying that the setup is correct, and that if the box fails over replication should pick right back up?|||A friend of mine responded, below. Apparently replication shouldn’t miss a beat if failover occurs:
Never had this issue before. We are running 4 node clusters (active/active/active/passive) here and we have never had that issue. I'm going to guess it goes back to the install of SQL. I would just copy any missing dll from one node to the other. It sounds like SQL is looking for them so I think you should be safe just to copy them over.
Any thoughts?
|||That depends on the programming mechanism. Sometimes they need to be registered and sometimes they don't. It these dll's contain com compoents they will be, if not a simple file copy will work.Replication Architecture question - Pull subscriptions on a SQL cluster...
Greetings:
I am working on a replication setup using transactional replication using with pull subscriptions and a separate distributor. The pull subscriptions are located on a SQL cluster using the virtual SQL Server name as the subscriber; when the box fails over, we get an error of missing replication.dlls. Researching further, we found that replication only works on one node of the subscriber.
Any ideas on what we did wrong in the setup?
Thanks,
Lee Everest
www.texas2oo.com/sqlblog
can you check the c:\program files\Microsoft Sql Server\80\com folder to see if the dll's exist there? If not try to copy and register them on the failing node.|||
We could do that, but i'm afraid that after I try to install one, several (or dozens) of others might show up missing, meaning that we didn't install it properly in the first place. The specific error that we get is:
Cannot load the DLL replsetoriginator extended procedure, or one of the DLLs it references. Reason: 126(The specified module could not be found.). The step failed.
I have no idea which DLL this function is located but could probably find out.
|||This is a missing or corrupt dll. When I have encountered these in SQL FTS clusters I have had to copy the dll's to the failed not and reregister them.|||So you are saying that the setup is correct, and that if the box fails over replication should pick right back up?|||A friend of mine responded, below. Apparently replication shouldn’t miss a beat if failover occurs:
Never had this issue before. We are running 4 node clusters (active/active/active/passive) here and we have never had that issue. I'm going to guess it goes back to the install of SQL. I would just copy any missing dll from one node to the other. It sounds like SQL is looking for them so I think you should be safe just to copy them over.
Any thoughts?
|||That depends on the programming mechanism. Sometimes they need to be registered and sometimes they don't. It these dll's contain com compoents they will be, if not a simple file copy will work.