I have three locations each with multiple SQL 2000 databases that need to
stay in constant sync with the other locations. Which is the best type of
replication? Merge Replication or 2-way Transactional Replication?
TIA,
Kevin
Probably peer to peer in SQL 2005. However bi-directional transactional
would probably be best. Keep in mind you are locked into a schema with this.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>I have three locations each with multiple SQL 2000 databases that need to
>stay in constant sync with the other locations. Which is the best type of
>replication? Merge Replication or 2-way Transactional Replication?
> TIA,
> Kevin
>
|||Schema issues are the reason I posted this question in the first place. In
my test scenario, I have 3 servers doing merge replication back and forth to
each other. Each server subscribes to the other two. Everything was working
fine until I tried to add a column for testing. I got the following error
message:
Schema replication failed because database X on server X is not the original
Publisher of table
Any ideas?
Kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
> Probably peer to peer in SQL 2005. However bi-directional transactional
> would probably be best. Keep in mind you are locked into a schema with
> this.
> --
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:e4PRqHGTHHA.3428@.TK2MSFTNGP04.phx.gbl...
>
|||Do you have a hierarchy? IE Server A publishes to Server B which publishes
to Server C? Is so you must make the schema change at the top of the
hierarchy.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Schema issues are the reason I posted this question in the first place. In
> my test scenario, I have 3 servers doing merge replication back and forth
> to each other. Each server subscribes to the other two. Everything was
> working fine until I tried to add a column for testing. I got the
> following error message:
> Schema replication failed because database X on server X is not the
> original Publisher of table
> Any ideas?
> Kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uMWCuhGTHHA.5016@.TK2MSFTNGP05.phx.gbl...
>
|||I do not have a heirarchy. The topology I would like to use is a sort of
triangle, with no one server being the master and all transactions shared.
Thanks,
kevin
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
> Do you have a hierarchy? IE Server A publishes to Server B which publishes
> to Server C? Is so you must make the schema change at the top of the
> hierarchy.
> --
> 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
>
> "Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
> news:emdknlGTHHA.2256@.TK2MSFTNGP02.phx.gbl...
>
|||You are talking about a multi-master topology which merge replication does
not support. You will need to use peer-to-peer in SQL Server 2005 for this.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:eemppbHTHHA.1036@.TK2MSFTNGP03.phx.gbl...
>I do not have a heirarchy. The topology I would like to use is a sort of
>triangle, with no one server being the master and all transactions shared.
> Thanks,
> kevin
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OXSz4XHTHHA.1200@.TK2MSFTNGP02.phx.gbl...
>
|||Kevin - just checking why you don't set up one merge publisher and 2 merge
subscribers. Is it because we don't have alternative sync partners anymore
and you can't guarantee connectivity with the publisher?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Yes Paul, I need sort of a triangle setup with each node working
independently and then sharing information with the other two.
Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
quick look at the replication options on our 2005 boxes on Friday and didn't
see anything about Peer-to-Peer.
Kevin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
> Kevin - just checking why you don't set up one merge publisher and 2 merge
> subscribers. Is it because we don't have alternative sync partners anymore
> and you can't guarantee connectivity with the publisher?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
|||It is only available on the Enterprise Editions of SQL Server 2005.
You will be able to get it working using pure bi-directional transactional
replication but will probably have to do some filtering to get it to work
correctly.
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
"Kevin Eckart" <eckart_612@.hotmail.com> wrote in message
news:e08ed4qTHHA.600@.TK2MSFTNGP05.phx.gbl...
> Yes Paul, I need sort of a triangle setup with each node working
> independently and then sharing information with the other two.
> Hilary, is Peer-to-Peer replication available in 2005 Standard? I took a
> quick look at the replication options on our 2005 boxes on Friday and
> didn't see anything about Peer-to-Peer.
> Kevin
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:e3pPvcITHHA.4756@.TK2MSFTNGP06.phx.gbl...
>
|||Kevin,
what you are proposing is not in conflict with the merge setup, as you
mention the data is exchanges with the other 2 nodes. If this is a
prerequisite, then the connectivity must exist and the merge setup remains
an option. It doesn't perform any where near as well as peer to peer (only
enterpriose as you mentioned
http://msdn2.microsoft.com/en-us/library/ms143761.aspx) , but has the
advantage of easily adapting to schema changes and conflicts.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
Showing posts with label databases. Show all posts
Showing posts with label databases. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
replication strategy
Hi all,
First of all, I'm a newbi to databases.
Anyway, I am about to start writing a program that is handling
customers, invoices and so on. A decision has been taken to use Sql
Server 2000 (MSDE at customers)as RDBMS. It has also been decided that
the development should be devided into several iterations. Adding more
functionality at each iteration.
My problem is as follows: How do I distribute the design (tables,
stored proc, columns, keys etc) of the database when releasing new
versions of the program?
Let's assume that a customer table has the following columns "Id",
"Name" and "Phone". In the next version of my program I have added
funcionality to send e-mails to a customer so I have now added a
column called "Email" to the customer table.
This new column, some how, has to be inserted into the database before
the new version of the program can be used. I want to add this column
to the table without loosing any existing data in the table. Which is
the best strategy to do this? I have played around with SQLDMO,
looking at my own development database to create some sort of
definition file that could be used to alter table's. It feel's like
there is a better solution than to manualy go through all objects
(table, column, keys, Stored proc. etc) that I want to replicate.
Is there any good websites on this subject?
The program will be used at over 100 customers. Each customer should
run a installation program that will take care of updating the
database so that they can run the version of my program.
Br
JonasYou are really talking about distributing database
upgrade, not SQL Server replicaiton.
I'd just perform all (or most) database updates in some T-
SQL scripts, include the scripts in the install package,
have the install program control teir execution, and make
sure that the install program know how to check for
success/failure conditions and act accordingly.
I don't mean to sugges that you include all database
changes in a single T-SQL script. In many cases, that
wouldn't lead to robust error checking.
Linchi
handling
to use Sql
decided that
iterations. Adding more
(tables,
releasing new
columns "Id",
have added
added a
database before
this column
table. Which is
SQLDMO,
sort of
feel's like
objects
replicate.
customer should
updating the
First of all, I'm a newbi to databases.
Anyway, I am about to start writing a program that is handling
customers, invoices and so on. A decision has been taken to use Sql
Server 2000 (MSDE at customers)as RDBMS. It has also been decided that
the development should be devided into several iterations. Adding more
functionality at each iteration.
My problem is as follows: How do I distribute the design (tables,
stored proc, columns, keys etc) of the database when releasing new
versions of the program?
Let's assume that a customer table has the following columns "Id",
"Name" and "Phone". In the next version of my program I have added
funcionality to send e-mails to a customer so I have now added a
column called "Email" to the customer table.
This new column, some how, has to be inserted into the database before
the new version of the program can be used. I want to add this column
to the table without loosing any existing data in the table. Which is
the best strategy to do this? I have played around with SQLDMO,
looking at my own development database to create some sort of
definition file that could be used to alter table's. It feel's like
there is a better solution than to manualy go through all objects
(table, column, keys, Stored proc. etc) that I want to replicate.
Is there any good websites on this subject?
The program will be used at over 100 customers. Each customer should
run a installation program that will take care of updating the
database so that they can run the version of my program.
Br
JonasYou are really talking about distributing database
upgrade, not SQL Server replicaiton.
I'd just perform all (or most) database updates in some T-
SQL scripts, include the scripts in the install package,
have the install program control teir execution, and make
sure that the install program know how to check for
success/failure conditions and act accordingly.
I don't mean to sugges that you include all database
changes in a single T-SQL script. In many cases, that
wouldn't lead to robust error checking.
Linchi
quote:
>--Original Message--
>Hi all,
>First of all, I'm a newbi to databases.
>Anyway, I am about to start writing a program that is
handling
quote:
>customers, invoices and so on. A decision has been taken
to use Sql
quote:
>Server 2000 (MSDE at customers)as RDBMS. It has also been
decided that
quote:
>the development should be devided into several
iterations. Adding more
quote:
>functionality at each iteration.
>My problem is as follows: How do I distribute the design
(tables,
quote:
>stored proc, columns, keys etc) of the database when
releasing new
quote:
>versions of the program?
>Let's assume that a customer table has the following
columns "Id",
quote:
>"Name" and "Phone". In the next version of my program I
have added
quote:
>funcionality to send e-mails to a customer so I have now
added a
quote:
>column called "Email" to the customer table.
>This new column, some how, has to be inserted into the
database before
quote:
>the new version of the program can be used. I want to add
this column
quote:
>to the table without loosing any existing data in the
table. Which is
quote:
>the best strategy to do this? I have played around with
SQLDMO,
quote:
>looking at my own development database to create some
sort of
quote:
>definition file that could be used to alter table's. It
feel's like
quote:
>there is a better solution than to manualy go through all
objects
quote:
>(table, column, keys, Stored proc. etc) that I want to
replicate.
quote:
>Is there any good websites on this subject?
>The program will be used at over 100 customers. Each
customer should
quote:
>run a installation program that will take care of
updating the
quote:
>database so that they can run the version of my program.
>Br
>Jonas
>.
>
replication strategy
Hi all,
First of all, I'm a newbi to databases.
Anyway, I am about to start writing a program that is handling
customers, invoices and so on. A decision has been taken to use Sql
Server 2000 (MSDE at customers)as RDBMS. It has also been decided that
the development should be devided into several iterations. Adding more
functionality at each iteration.
My problem is as follows: How do I distribute the design (tables,
stored proc, columns, keys etc) of the database when releasing new
versions of the program?
Let's assume that a customer table has the following columns "Id",
"Name" and "Phone". In the next version of my program I have added
funcionality to send e-mails to a customer so I have now added a
column called "Email" to the customer table.
This new column, some how, has to be inserted into the database before
the new version of the program can be used. I want to add this column
to the table without loosing any existing data in the table. Which is
the best strategy to do this? I have played around with SQLDMO,
looking at my own development database to create some sort of
definition file that could be used to alter table's. It feel's like
there is a better solution than to manualy go through all objects
(table, column, keys, Stored proc. etc) that I want to replicate.
Is there any good websites on this subject?
The program will be used at over 100 customers. Each customer should
run a installation program that will take care of updating the
database so that they can run the version of my program.
Br
JonasYou are really talking about distributing database
upgrade, not SQL Server replicaiton.
I'd just perform all (or most) database updates in some T-
SQL scripts, include the scripts in the install package,
have the install program control teir execution, and make
sure that the install program know how to check for
success/failure conditions and act accordingly.
I don't mean to sugges that you include all database
changes in a single T-SQL script. In many cases, that
wouldn't lead to robust error checking.
Linchi
>--Original Message--
>Hi all,
>First of all, I'm a newbi to databases.
>Anyway, I am about to start writing a program that is
handling
>customers, invoices and so on. A decision has been taken
to use Sql
>Server 2000 (MSDE at customers)as RDBMS. It has also been
decided that
>the development should be devided into several
iterations. Adding more
>functionality at each iteration.
>My problem is as follows: How do I distribute the design
(tables,
>stored proc, columns, keys etc) of the database when
releasing new
>versions of the program?
>Let's assume that a customer table has the following
columns "Id",
>"Name" and "Phone". In the next version of my program I
have added
>funcionality to send e-mails to a customer so I have now
added a
>column called "Email" to the customer table.
>This new column, some how, has to be inserted into the
database before
>the new version of the program can be used. I want to add
this column
>to the table without loosing any existing data in the
table. Which is
>the best strategy to do this? I have played around with
SQLDMO,
>looking at my own development database to create some
sort of
>definition file that could be used to alter table's. It
feel's like
>there is a better solution than to manualy go through all
objects
>(table, column, keys, Stored proc. etc) that I want to
replicate.
>Is there any good websites on this subject?
>The program will be used at over 100 customers. Each
customer should
>run a installation program that will take care of
updating the
>database so that they can run the version of my program.
>Br
>Jonas
>.
>
First of all, I'm a newbi to databases.
Anyway, I am about to start writing a program that is handling
customers, invoices and so on. A decision has been taken to use Sql
Server 2000 (MSDE at customers)as RDBMS. It has also been decided that
the development should be devided into several iterations. Adding more
functionality at each iteration.
My problem is as follows: How do I distribute the design (tables,
stored proc, columns, keys etc) of the database when releasing new
versions of the program?
Let's assume that a customer table has the following columns "Id",
"Name" and "Phone". In the next version of my program I have added
funcionality to send e-mails to a customer so I have now added a
column called "Email" to the customer table.
This new column, some how, has to be inserted into the database before
the new version of the program can be used. I want to add this column
to the table without loosing any existing data in the table. Which is
the best strategy to do this? I have played around with SQLDMO,
looking at my own development database to create some sort of
definition file that could be used to alter table's. It feel's like
there is a better solution than to manualy go through all objects
(table, column, keys, Stored proc. etc) that I want to replicate.
Is there any good websites on this subject?
The program will be used at over 100 customers. Each customer should
run a installation program that will take care of updating the
database so that they can run the version of my program.
Br
JonasYou are really talking about distributing database
upgrade, not SQL Server replicaiton.
I'd just perform all (or most) database updates in some T-
SQL scripts, include the scripts in the install package,
have the install program control teir execution, and make
sure that the install program know how to check for
success/failure conditions and act accordingly.
I don't mean to sugges that you include all database
changes in a single T-SQL script. In many cases, that
wouldn't lead to robust error checking.
Linchi
>--Original Message--
>Hi all,
>First of all, I'm a newbi to databases.
>Anyway, I am about to start writing a program that is
handling
>customers, invoices and so on. A decision has been taken
to use Sql
>Server 2000 (MSDE at customers)as RDBMS. It has also been
decided that
>the development should be devided into several
iterations. Adding more
>functionality at each iteration.
>My problem is as follows: How do I distribute the design
(tables,
>stored proc, columns, keys etc) of the database when
releasing new
>versions of the program?
>Let's assume that a customer table has the following
columns "Id",
>"Name" and "Phone". In the next version of my program I
have added
>funcionality to send e-mails to a customer so I have now
added a
>column called "Email" to the customer table.
>This new column, some how, has to be inserted into the
database before
>the new version of the program can be used. I want to add
this column
>to the table without loosing any existing data in the
table. Which is
>the best strategy to do this? I have played around with
SQLDMO,
>looking at my own development database to create some
sort of
>definition file that could be used to alter table's. It
feel's like
>there is a better solution than to manualy go through all
objects
>(table, column, keys, Stored proc. etc) that I want to
replicate.
>Is there any good websites on this subject?
>The program will be used at over 100 customers. Each
customer should
>run a installation program that will take care of
updating the
>database so that they can run the version of my program.
>Br
>Jonas
>.
>
Replication Standard or Enterprise
In setting up replication between databases do both sql installations have to be enterprise or can it be done with both sql server standard edition.
thanks,Replication is included in Standard Edition.|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...
Tell the other ...admins ... to take a hike:
SQL Server 2000 Standard Edition
This is a more affordable option for small-sized and medium-sized organizations that do not require the advanced scalability, availability, performance, or analysis features of SQL Server 2000 Enterprise Edition. Standard Edition can be used on symmetric multiprocessing systems with up to 4 CPUs and 2 GB of RAM.
Standard Edition includes the core functionality needed for non-mission-critical e-commerce, data warehousing, and line-of-business solutions. For instance, all of the XML features present in Enterprise Edition are also included in Standard Edition. And while a handful of advanced OLAP features are reserved for Enterprise Edition, all data mining features and the core OLAP functionality are included in SQL Server 2000 Analysis Services in Standard Edition. Similarly, components that other database vendors charge for as separate add-on products for their highest-end editions are included in Standard Edition:
Data Transformation Services (DTS)
Replication (snapshot, transactional, and merge)
Full-Text Search
English Query
Stored procedure development and debugging tools
SQL Profiling and performance analysis tools
Before choosing Standard Edition, make sure you review "Features Supported by the Editions of SQL Server 2000" in SQL Server Books Online.|||Kindly ask the "other admins" for proof of this. Perhaps, if you build the replication into the QA system (a good idea, anyway), you can prove to them it is possible.|||I appreciate the feedback... and will do a lot more research... any experienced suggestions or tips that a tutorial or book might not mention .... thanks again guys...|||Or maybe they would like to read the 2005 features for themselves:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx|||I have a solution that not only replicates the data (standard or enterprise, 2000 or 2005) but also load balances read-only statements and provides effective means of resynchronization. I work for a company, PCTI (Parallel Computers Technology Inc), out of the Philadelphia area. We have a product, DBx, that does Parallel Synchronous Real Time Replication (up to 16 servers, local or remote) guaranteeing zero-loss of transactions. Anyone looking for more information should visit http://www.pcticorp.com or send me a private message via dbforums.com.
thanks,Replication is included in Standard Edition.|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...|||Is there a difference in using enterprise... or standard in regards to replication... I can not find much information stating the difference in choosing an edition...
here @. work ... the prod server they want to replicate has already been built using standard... but other... admins... say it would have to be enterprise...
Tell the other ...admins ... to take a hike:
SQL Server 2000 Standard Edition
This is a more affordable option for small-sized and medium-sized organizations that do not require the advanced scalability, availability, performance, or analysis features of SQL Server 2000 Enterprise Edition. Standard Edition can be used on symmetric multiprocessing systems with up to 4 CPUs and 2 GB of RAM.
Standard Edition includes the core functionality needed for non-mission-critical e-commerce, data warehousing, and line-of-business solutions. For instance, all of the XML features present in Enterprise Edition are also included in Standard Edition. And while a handful of advanced OLAP features are reserved for Enterprise Edition, all data mining features and the core OLAP functionality are included in SQL Server 2000 Analysis Services in Standard Edition. Similarly, components that other database vendors charge for as separate add-on products for their highest-end editions are included in Standard Edition:
Data Transformation Services (DTS)
Replication (snapshot, transactional, and merge)
Full-Text Search
English Query
Stored procedure development and debugging tools
SQL Profiling and performance analysis tools
Before choosing Standard Edition, make sure you review "Features Supported by the Editions of SQL Server 2000" in SQL Server Books Online.|||Kindly ask the "other admins" for proof of this. Perhaps, if you build the replication into the QA system (a good idea, anyway), you can prove to them it is possible.|||I appreciate the feedback... and will do a lot more research... any experienced suggestions or tips that a tutorial or book might not mention .... thanks again guys...|||Or maybe they would like to read the 2005 features for themselves:
http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx|||I have a solution that not only replicates the data (standard or enterprise, 2000 or 2005) but also load balances read-only statements and provides effective means of resynchronization. I work for a company, PCTI (Parallel Computers Technology Inc), out of the Philadelphia area. We have a product, DBx, that does Parallel Synchronous Real Time Replication (up to 16 servers, local or remote) guaranteeing zero-loss of transactions. Anyone looking for more information should visit http://www.pcticorp.com or send me a private message via dbforums.com.
replication SQLServer 2-way
We have 2 SQLServer-databases with the same table in it. In both databases the table can be updated by users.
Is is possible to replicate the updates in this table from the one database to the other and vice versa? We want to use transactional-replication.You can setup an updatable transactional replication. To do that, specify 'true' to the paramenter @.allow_sync_tran when you execute sp_addpublication. And @.update_mode = 'sync tran' when you execute sp_addsubscription.|||Just out of curiousity, why can't he use merge replication instead?
I'm still learning from this replication thing...is there any reasons for chosing transactional against merge for this purpose?|||My personal experience in both merge and updatable transactional is that merge is slower, and it take a lot more to setup and manage. You can treat updatable transactional just like the regular transactional replication with a couple of more parameters to setup. There are other minor reasons but the main thing is the simplicity and better performance of transactional replication.
Hope this helps.|||joejcheng,
We tried the solution you provided and it works fine now. Thank you for your information.
Is is possible to replicate the updates in this table from the one database to the other and vice versa? We want to use transactional-replication.You can setup an updatable transactional replication. To do that, specify 'true' to the paramenter @.allow_sync_tran when you execute sp_addpublication. And @.update_mode = 'sync tran' when you execute sp_addsubscription.|||Just out of curiousity, why can't he use merge replication instead?
I'm still learning from this replication thing...is there any reasons for chosing transactional against merge for this purpose?|||My personal experience in both merge and updatable transactional is that merge is slower, and it take a lot more to setup and manage. You can treat updatable transactional just like the regular transactional replication with a couple of more parameters to setup. There are other minor reasons but the main thing is the simplicity and better performance of transactional replication.
Hope this helps.|||joejcheng,
We tried the solution you provided and it works fine now. Thank you for your information.
Monday, March 26, 2012
Replication scenario - seeking suggestion
I have two sites. Site A and Site B
Each site has two databases
Site A
Db1
Db2
Site B
Db1
Db2
Site A Db1 has to perform transaction replication to Site A- Db2 and Site B- Db1 and Db2.
I started Site A as pubisher and distributor and Site A and Site B both as subscriber.
Site B is in a different geographical area (state).
-
Please suggest the best scenario to save bandwidth and server load for Publisher, and Distributor.
-
Earlier I thought that I will implement local replication between Site B - in between Db1 and Db2. The Sql Server does not let me set Db1 as publisher, and distributor for its local database Db2.
-
P.S. My all databases need same transactions though they are connected to different hardware at different places. So please don't question that why I need four similar databases.
You can publish to Db1 then use Db1 as a republisher to publisher to Db2.
See in Books Online: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/a1485cf4-b1c4-49e9-ab06-8ccfaad998f3.htm
Martin
|||Thank you!!
looks good.
For Site A - Db1
publisher, distributor and subsriber(Site A- Db2)
Db 2
Publisher Site A
Distributor Site B
Subscriber Db1
Subscriber Db2
I think, this is what you are suggesting.
|||My suggestion would have the distributor on the same machine so:
A - DB 1 (master publisher)
A - DB 2 (subscriber)
B - DB 1 (subscriber , republisher)
B - DB 2 (subscriber (to B - DB 1)
Martin
|||Thank you!!
I never tried republisher, I am running Sql Server 2000.
Let me read it, if I will have any question then I will get back.
Moreover, I could not access that help, this does not work from my computer.
This is exactly I would prefer, because otherwise it seems stupid to send data twice to the other site.
|||Oh this is for SQL 2005 only. You need to install the SQL2005 books online to view the help link.
Martin
|||Thank you!Friday, March 23, 2012
Replication question - updatable subscription?
Hi all,
I am planning replication around a large SQL Database Server; 3 databases need to be replicated for reporting purposes.
My plan was to use Transaction Replication, as the Reporting database will be mostly read-only. However, we must add a whole bunch of additional, historical data to some of the tables of the reporting database that does not exist in the Publisher. I understand that changes can be made to subscriptions in Transaction Replication scenarios, ideally when the changes are few and far between. (Merge replication is excessive for our purposes.)
I don't want the additional historical data to be propagated back to the publisher. Is it possible that data is written to a subscriber in a Transactional Replication scenario without writing these updates to the Publisher? Given the nature of the data, there will not be conflicts between the historical data and the new changes applied from the publisher.
Any help would be much appreciated!
Thanks,
FarazWhile you are flirting at the edge of what works well, at least what you've described is possible. I'm pretty sure that Microsoft does not recommend it, I'm not sure if they support it, but I have done it under very limited circumstances for very specific purposes.
This is not for the faint of heart. You need to think things out carefully. It might pay you to find someone experienced in replication that you can contract to help set things up and shake out the problems.
-PatP|||Thanks for the reply. We won't be using transactional replication. In fact, from my understanding, one of the goals of replication is ensuring that data consistency is maintained between publishers and subscribers; in our case, we don't actually want the data to be consistent. Hence, we'll go forth with a less elegant, but far simpler solution.
Thanks again!
-Farazsql
I am planning replication around a large SQL Database Server; 3 databases need to be replicated for reporting purposes.
My plan was to use Transaction Replication, as the Reporting database will be mostly read-only. However, we must add a whole bunch of additional, historical data to some of the tables of the reporting database that does not exist in the Publisher. I understand that changes can be made to subscriptions in Transaction Replication scenarios, ideally when the changes are few and far between. (Merge replication is excessive for our purposes.)
I don't want the additional historical data to be propagated back to the publisher. Is it possible that data is written to a subscriber in a Transactional Replication scenario without writing these updates to the Publisher? Given the nature of the data, there will not be conflicts between the historical data and the new changes applied from the publisher.
Any help would be much appreciated!
Thanks,
FarazWhile you are flirting at the edge of what works well, at least what you've described is possible. I'm pretty sure that Microsoft does not recommend it, I'm not sure if they support it, but I have done it under very limited circumstances for very specific purposes.
This is not for the faint of heart. You need to think things out carefully. It might pay you to find someone experienced in replication that you can contract to help set things up and shake out the problems.
-PatP|||Thanks for the reply. We won't be using transactional replication. In fact, from my understanding, one of the goals of replication is ensuring that data consistency is maintained between publishers and subscribers; in our case, we don't actually want the data to be consistent. Hence, we'll go forth with a less elegant, but far simpler solution.
Thanks again!
-Farazsql
Wednesday, March 21, 2012
Replication Problem!
Hello Group,
I'm working on two SQL server databases on two different remote
servers. What client needs is that his database on one remote server
should be mirrored to the other one. The changed should be propogated at
regular intervals at the other server.
I'm working on two SQL server databases on two different remote
servers. What client needs is that his database on one remote server
should be mirrored to the other one. The changed should be propogated at
regular intervals at the other server.
For now i'm quite sure that i'll have to use replication to resolve
this issue.
But which type of replication should i use? Transactional, Merge or
Snapshot?
Please note that the client is running his website from the main
server, which is using the source SQL Server database - the one i'll
have to replicate.
Last time when i tried to register the destination server, at my
source server Enterprise Manager, it gave me an error failing to
register the destination server. When i asked the client, all he could
tell me was that both the servers are firewalled and that might have
been the problem.
So just tell me how should i go for it?
Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***If you go the replicated route then your choice (of merge, snapshot or
transactional) is probably straight forward:
- Will they ever want to make changes on the remote server? If yes then you
must use merge replication.
- Otherwise got for Transactional.
Unless the database is small don't go for snapshot. Snapshot is fine for
just that, taking the odd snapshot, but isn't really suited if you want to
regularly propagate changes.
Regarding your problem connecting to the remove server. It is quite likely
that firewalls could be causing you a problem. I've not tried it, but
presumably the most secure way forward would be to create a vpn connection
between the two servers (firewalls may still be an issue) and then replicate
across the vpn link.
Hope this helps,
Brian.
www.cryer.co.uk/brian
"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:d5Sue.8$_r5.2081@.news.uswest.net...
> Hello Group,
> I'm working on two SQL server databases on two different remote
> servers. What client needs is that his database on one remote server
> should be mirrored to the other one. The changed should be propogated at
> regular intervals at the other server.
> For now i'm quite sure that i'll have to use replication to resolve
> this issue.
> But which type of replication should i use? Transactional, Merge or
> Snapshot?
> Please note that the client is running his website from the main
> server, which is using the source SQL Server database - the one i'll
> have to replicate.
> Last time when i tried to register the destination server, at my
> source server Enterprise Manager, it gave me an error failing to
> register the destination server. When i asked the client, all he could
> tell me was that both the servers are firewalled and that might have
> been the problem.
> So just tell me how should i go for it?
> Thanks in advance
> Debian
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hi again!
The problem is that all the source tables dont have a pri key... and
i have learnt that Trans Rep doesnt support replicating tables without
Pri key. Besides there's no question of adding Pri Keys to the database
which is already live!
Second thing is that, the database size is in between 1 and 2GBs. That
means even Snapshot Rep is not suitable!
Shall i go for Merge Rep? I heard that there are conflicts in that?
So then what is the way out?
Thanks Brian, for replying!
Regards
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> The problem is that all the source tables dont have a pri key... and
> i have learnt that Trans Rep doesnt support replicating tables without
> Pri key. Besides there's no question of adding Pri Keys to the database
> which is already live!
Well, it want take long before the database is dead. Not having
primary keys is asking for serious problems.
> Second thing is that, the database size is in between 1 and 2GBs. That
> means even Snapshot Rep is not suitable!
> Shall i go for Merge Rep? I heard that there are conflicts in that?
I doubt that merge replication is possible without PKs. If rows can't
be identified, it's getting darn difficult to do replication.
Seems to me you have three options:
1) Add PKs to the database, and do transactional replication.
2) Regularly backup the database and restore on the other end.
3) Log shipping.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns968035B96389Yazorman@.127.0.0.1...
> debian mojo (debian_mojo@.yahoo.com) writes:
>>
>> The problem is that all the source tables dont have a pri key... and
>> i have learnt that Trans Rep doesnt support replicating tables without
>> Pri key. Besides there's no question of adding Pri Keys to the database
>> which is already live!
> Well, it want take long before the database is dead. Not having
> primary keys is asking for serious problems.
>> Second thing is that, the database size is in between 1 and 2GBs. That
>> means even Snapshot Rep is not suitable!
>>
>> Shall i go for Merge Rep? I heard that there are conflicts in that?
> I doubt that merge replication is possible without PKs. If rows can't
> be identified, it's getting darn difficult to do replication.
> Seems to me you have three options:
> 1) Add PKs to the database, and do transactional replication.
> 2) Regularly backup the database and restore on the other end.
> 3) Log shipping.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
I fully agree with everything Erland has said.
You may not currently have primary keys, but surely there are fields that
you are treating as unique? How do you currently uniquely identify a record?
A primary key can be defined using more than one field.
Treat Erland's comment as a dire warning:
> Well, it want take long before the database is dead. Not having
> primary keys is asking for serious problems.
Its put very well, and spot on.
Brian.
www.cryer.co.uk/brian|||Hello Erland and Brian,
Thanks for the reply!
The problem is that there are around 36 user tables in the source
database and only 5 of them have pri keys, the rest are either using
foriegn keys or dont have any kinda keys at all!
Is it good to have pri keys on all tables in the database.
Please do note that the database is for a website and it is estimated
that it will hit 10GB in the first one month!
Is there any chance of replication without compromising the database
integrity or shall i look for any other options such as log shipping?
Please advise!
Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> Please do note that the database is for a website and it is estimated
> that it will hit 10GB in the first one month!
Of course with no Pkeys, the chances for duplicates increase, and so
will the database size.
> Is there any chance of replication without compromising the database
> integrity or shall i look for any other options such as log shipping?
If you don't want to add primary keys (and save the database from a
disaster furtther afield) log shipping or backup/restore is what you
have to look into.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks again, Erland,
Since the database has problems with Pri keys, i'm thinking about Log
Shipping as an alternative.
But as it is mentioned in BOL that for LS to work, it's necessary to
configure three servers:
1. Source Server
2. Monitor Server
3. Destination Server.
Now the destination and the source databases can be on the same
server, but it is mandatory to keep the monitor server separate.
The problem with this approach is that i've access to only two servers
- one source and one destination. I wont have the privilege of having
another one just for setting up the monitor server!
What should i do?
Thanks in advance!
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> Since the database has problems with Pri keys, i'm thinking about Log
> Shipping as an alternative.
> But as it is mentioned in BOL that for LS to work, it's necessary to
> configure three servers:
> 1. Source Server
> 2. Monitor Server
> 3. Destination Server.
> Now the destination and the source databases can be on the same
> server, but it is mandatory to keep the monitor server separate.
> The problem with this approach is that i've access to only two servers
> - one source and one destination. I wont have the privilege of having
> another one just for setting up the monitor server!
Looks like you are starting to get some good arguments: "Either we
fix primary keys to the database, or we go and buy some more hardware,
else we can't run this show".
I will have to admit that the need for third machine was news to me,
but I have never set up log shipping myself.
Actually, add PKeys to those tables does not have to be a killer
work. If there are unique indexes, just drop these and recreate
them as primary keys. If there are not any primary keys, just add
an uniqueidentifier with the default of NEWID() to the tables, and
make that the primary key (nonclustered!). That does not really help
to make the data model any better, but at least you can set up
replication over this dying grace.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
That's true that moving away from the problem is not a solution. But
you see that the database i'm talking about is a 24 x 7 database, and
i'm not pretty sure how apps that are using this db.
If i do pri keys to the tables, is there any possibility that the apps
may suffer is some way.
I do agree that in most of the cases there's no harm... but you see
the database is a production one and i'm really paranoid about it's
safety!
I hope you understand!
BTW, your advice was an eye-opener for me.
So what do you suggest? what are the other things i should take care
of before adding the pri keys?
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||what are the other things i should take care of on the app side, before
adding the pri keys?
Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***|||"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:aeawe.4$jU.2182@.news.uswest.net...
> Thanks Erland,
> That's true that moving away from the problem is not a solution. But
> you see that the database i'm talking about is a 24 x 7 database, and
> i'm not pretty sure how apps that are using this db.
> If i do pri keys to the tables, is there any possibility that the apps
> may suffer is some way.
> I do agree that in most of the cases there's no harm... but you see
> the database is a production one and i'm really paranoid about it's
> safety!
> I hope you understand!
> BTW, your advice was an eye-opener for me.
> So what do you suggest? what are the other things i should take care
> of before adding the pri keys?
> Debian
>
> *** Sent via Developersdex http://www.developersdex.com ***
Don't make changes to a live production database unless you have tested it
first and are comfortable about the changes you are going to make. You NEED
primary keys, but don't make the assumption that adding a new primary key
won't break one of your applications. If all you are doing is redefining an
existing unique index as a primary key then that shouldn't hurt anything,
but if you are adding a new field as a primary (default NEWID as per
Erland's suggestion [a very good suggestion by the way]) then that does have
the potential to break something - I think its only likely to break badly
written code, but the potential is there, so test it first.
Take a copy, add your primary keys to that and then run through the whole
range of applications and ensure that everything works as you expect. Only
then, once you are entirely satisfied, introduce the changes to the live
database and even then be sure that you can roll your changes back if it all
goes horribly wrong.
Brian.
www.cryer.co.uk/brian|||debian mojo (debian_mojo@.yahoo.com) writes:
> That's true that moving away from the problem is not a solution. But
> you see that the database i'm talking about is a 24 x 7 database, and
> i'm not pretty sure how apps that are using this db.
> If i do pri keys to the tables, is there any possibility that the apps
> may suffer is some way.
Sure. Apps that do SELECT * on the tables and then spit out all columns
somewhere, or assume that it has a certain width will be confused by an
extra column if you add one. If you add the new column anywhere but last,
apps that refers to columns by column number will croak. All this is
bad practice, but since this site already has proven a fondness for
bad practice...
As Brian says, you need to test any changes in a safe environment. Which
includes finding out how long time it takes to add the indexes and the new
columns, so you can determine the downtime.
I should have added that beside looking for unique indexes, also look
for existing IDENTITY columns and existing guid columns, as they can
be used for the task.
> So what do you suggest? what are the other things i should take care
> of before adding the pri keys?
Read Brian's article again. There was a lot of good advice there!
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Replication Problem
We have same database at our Head office and at factory. we use merge replication over internet to synchronize both databases.
The problem is that the internet connection is not good and keep on breaking. Is there some other way to replicate database.Originally posted by saleem
We have same database at our Head office and at factory. we use merge replication over internet to synchronize both databases.
The problem is that the internet connection is not good and keep on breaking. Is there some other way to replicate database.
Have a look at Agent profiles, for the merge agent. Perhaps change the profile to a "Slow Link Agent Profile."
Tim|||Dear colleague
You can solve (partially) the problem by augmenting the timeout factor in the merge-agent.
Simply open SQL manager, and look into the database you need, under the folder replication manager, and go to the Agents folder. Open the Merge agents subfolder and look for the one which replicates the table(s) you need.
Right click on the merge agent of your choice and select properties.
Go to the Tab Steps and select the step Run Agent. click on Edit and you'll see the merge instruction appear. At the end of this instruction you can insert a timeout factor: -QueryTimeOut 120 (120 seconds).
Save the edited entry and try it out. Try also several timeout factors ( in seconds) for optimal performance.
I hope this helps you!
Greetings,
Vincent
Originally posted by saleem
We have same database at our Head office and at factory. we use merge replication over internet to synchronize both databases.
The problem is that the internet connection is not good and keep on breaking. Is there some other way to replicate database.
The problem is that the internet connection is not good and keep on breaking. Is there some other way to replicate database.Originally posted by saleem
We have same database at our Head office and at factory. we use merge replication over internet to synchronize both databases.
The problem is that the internet connection is not good and keep on breaking. Is there some other way to replicate database.
Have a look at Agent profiles, for the merge agent. Perhaps change the profile to a "Slow Link Agent Profile."
Tim|||Dear colleague
You can solve (partially) the problem by augmenting the timeout factor in the merge-agent.
Simply open SQL manager, and look into the database you need, under the folder replication manager, and go to the Agents folder. Open the Merge agents subfolder and look for the one which replicates the table(s) you need.
Right click on the merge agent of your choice and select properties.
Go to the Tab Steps and select the step Run Agent. click on Edit and you'll see the merge instruction appear. At the end of this instruction you can insert a timeout factor: -QueryTimeOut 120 (120 seconds).
Save the edited entry and try it out. Try also several timeout factors ( in seconds) for optimal performance.
I hope this helps you!
Greetings,
Vincent
Originally posted by saleem
We have same database at our Head office and at factory. we use merge replication over internet to synchronize both databases.
The problem is that the internet connection is not good and keep on breaking. Is there some other way to replicate database.
Tuesday, March 20, 2012
replication problem
Ok I'm going to post this again in another way.
Here's what I have.
2 databases p3e_master and winship.
winship has a merge publication that I use with sqlserverce for pocketpc
users. That works fine.
p3e_master has a transactional updateable publication that includes 3
tables. Those 3 tables are also included in the merge publication for
winship.
here's my problem.
If I update a value in any of the three tables either from winship or
p3e_master (with the sqlquery analyzer for instance) the change propogates
in either direction as it should.
However, when I change data on the pocketpc and the data is synced. The
update shows in winship (as it should), but it does not propogate back to
p3e_master.
Does aybody know if this aproach should work, and if so what do I need to do
to get it working correctly?
Thanks in advance
The various forms of replication don't work well together. You would be best
to use merge all the way for such a scenario as you describe.
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
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:uk$I%23hYMFHA.688@.TK2MSFTNGP10.phx.gbl...
> Ok I'm going to post this again in another way.
> Here's what I have.
> 2 databases p3e_master and winship.
> winship has a merge publication that I use with sqlserverce for pocketpc
> users. That works fine.
> p3e_master has a transactional updateable publication that includes 3
> tables. Those 3 tables are also included in the merge publication for
> winship.
>
> here's my problem.
> If I update a value in any of the three tables either from winship or
> p3e_master (with the sqlquery analyzer for instance) the change propogates
> in either direction as it should.
> However, when I change data on the pocketpc and the data is synced. The
> update shows in winship (as it should), but it does not propogate back to
> p3e_master.
> Does aybody know if this aproach should work, and if so what do I need to
> do
> to get it working correctly?
> Thanks in advance
>
Here's what I have.
2 databases p3e_master and winship.
winship has a merge publication that I use with sqlserverce for pocketpc
users. That works fine.
p3e_master has a transactional updateable publication that includes 3
tables. Those 3 tables are also included in the merge publication for
winship.
here's my problem.
If I update a value in any of the three tables either from winship or
p3e_master (with the sqlquery analyzer for instance) the change propogates
in either direction as it should.
However, when I change data on the pocketpc and the data is synced. The
update shows in winship (as it should), but it does not propogate back to
p3e_master.
Does aybody know if this aproach should work, and if so what do I need to do
to get it working correctly?
Thanks in advance
The various forms of replication don't work well together. You would be best
to use merge all the way for such a scenario as you describe.
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
"mgarner1980" <mgarner@.kbsi.com> wrote in message
news:uk$I%23hYMFHA.688@.TK2MSFTNGP10.phx.gbl...
> Ok I'm going to post this again in another way.
> Here's what I have.
> 2 databases p3e_master and winship.
> winship has a merge publication that I use with sqlserverce for pocketpc
> users. That works fine.
> p3e_master has a transactional updateable publication that includes 3
> tables. Those 3 tables are also included in the merge publication for
> winship.
>
> here's my problem.
> If I update a value in any of the three tables either from winship or
> p3e_master (with the sqlquery analyzer for instance) the change propogates
> in either direction as it should.
> However, when I change data on the pocketpc and the data is synced. The
> update shows in winship (as it should), but it does not propogate back to
> p3e_master.
> Does aybody know if this aproach should work, and if so what do I need to
> do
> to get it working correctly?
> Thanks in advance
>
Replication prerequisites
I need to make use of replication to merge two remote databases with one
central database and then update the two remote databases with the data on
the central database. What is the prerequisites for setting up replication? I
want to use C# for the merging. What do I need to do, because I've been
confusing myself now with security issues etc, etc...
Please help anyone?
This sounds like merge replication, but could equally well be transactional
with queued updating or transactional with immediate updating subscribers.
Have a look in BOL for the differences between these methods, or if you post
back with details of the following, someone will be able to help out:
(a) are the publisher and subscribers continuously connected?
(b) does the data overlap? If so, will you need any complicated conflict
resolution methods?
(c) will there be other subscribers coming onboard?
(d) do all the tables have PKs?
(e) do you want to replicate the execution of stored procs?
As for the programming aspect, please have a look here for some details to
start off with:
The SQLDMO scripts on here: http://www.replicationanswers.com/Scripts.asp
The ActiveX control scripts on here:
http://support.microsoft.com/default...b;EN-US;319649
http://support.microsoft.com/default...b;EN-US;319646
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanx alot for your response. I should be able to sort out the programming
side of this replication through some research that I have done. If you
wouldn't mind, I would like to go into more detail on what I want to do.
I have an application that our marketers will use to keep tract of their
leads and advertisers for our business directory website. Let's call the
marketers M1 and M2. ok, M1 and M2 will go out to different clients.
Obvisouly they won't go and see the same clients at the same time so they
need to share the information that M1 and M2 has gathered. So M1 has data and
M2 has a different set of data but they need to share it.
Both M1 and M2 are using laptops which isn't connected to our server or the
internet permanently. So M1 and M2 needs to come into the office and upload
their data and then receive new data that the marketer uploaded. If M1 came
into the office in the morning and uploaded his/hers data onto the server
using replication and M2 comes in, in the afternoon, M2 must be able to
receive the data that M1 uploaded that morning and then upload M2's data onto
the server so that M1 can receive M2's data the next time M1 uploads.
Hopefully that's explained clear enough to what I want to do. So, both M1
and M2 isn't always connected to each other or the internet. Yes, their will
be data overlaping since the server will have new information and the
marketer will also have new data which shouldn't be overwrittin but added.
All the tables have PK's. I don't have any stored procedures.
What will be the best option for this problem and how should I go about
setting everything up? I thought about having the each marketer's laptop as a
publisher and subscriber at the same time? is this possible?
Thanx again...
"Paul Ibison" wrote:
> This sounds like merge replication, but could equally well be transactional
> with queued updating or transactional with immediate updating subscribers.
> Have a look in BOL for the differences between these methods, or if you post
> back with details of the following, someone will be able to help out:
> (a) are the publisher and subscribers continuously connected?
> (b) does the data overlap? If so, will you need any complicated conflict
> resolution methods?
> (c) will there be other subscribers coming onboard?
> (d) do all the tables have PKs?
> (e) do you want to replicate the execution of stored procs?
> As for the programming aspect, please have a look here for some details to
> start off with:
> The SQLDMO scripts on here: http://www.replicationanswers.com/Scripts.asp
> The ActiveX control scripts on here:
> http://support.microsoft.com/default...b;EN-US;319649
> http://support.microsoft.com/default...b;EN-US;319646
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||If M1 and M2 connect up to a central server, I'd set up the central server
as a publisher with M1 and M2 as subscribers. I'd suggest looking at queued
updating subscribers for this scenario, provided you are not dealing with
text/image columns (merge would be the other alternative).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
central database and then update the two remote databases with the data on
the central database. What is the prerequisites for setting up replication? I
want to use C# for the merging. What do I need to do, because I've been
confusing myself now with security issues etc, etc...
Please help anyone?
This sounds like merge replication, but could equally well be transactional
with queued updating or transactional with immediate updating subscribers.
Have a look in BOL for the differences between these methods, or if you post
back with details of the following, someone will be able to help out:
(a) are the publisher and subscribers continuously connected?
(b) does the data overlap? If so, will you need any complicated conflict
resolution methods?
(c) will there be other subscribers coming onboard?
(d) do all the tables have PKs?
(e) do you want to replicate the execution of stored procs?
As for the programming aspect, please have a look here for some details to
start off with:
The SQLDMO scripts on here: http://www.replicationanswers.com/Scripts.asp
The ActiveX control scripts on here:
http://support.microsoft.com/default...b;EN-US;319649
http://support.microsoft.com/default...b;EN-US;319646
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanx alot for your response. I should be able to sort out the programming
side of this replication through some research that I have done. If you
wouldn't mind, I would like to go into more detail on what I want to do.
I have an application that our marketers will use to keep tract of their
leads and advertisers for our business directory website. Let's call the
marketers M1 and M2. ok, M1 and M2 will go out to different clients.
Obvisouly they won't go and see the same clients at the same time so they
need to share the information that M1 and M2 has gathered. So M1 has data and
M2 has a different set of data but they need to share it.
Both M1 and M2 are using laptops which isn't connected to our server or the
internet permanently. So M1 and M2 needs to come into the office and upload
their data and then receive new data that the marketer uploaded. If M1 came
into the office in the morning and uploaded his/hers data onto the server
using replication and M2 comes in, in the afternoon, M2 must be able to
receive the data that M1 uploaded that morning and then upload M2's data onto
the server so that M1 can receive M2's data the next time M1 uploads.
Hopefully that's explained clear enough to what I want to do. So, both M1
and M2 isn't always connected to each other or the internet. Yes, their will
be data overlaping since the server will have new information and the
marketer will also have new data which shouldn't be overwrittin but added.
All the tables have PK's. I don't have any stored procedures.
What will be the best option for this problem and how should I go about
setting everything up? I thought about having the each marketer's laptop as a
publisher and subscriber at the same time? is this possible?
Thanx again...
"Paul Ibison" wrote:
> This sounds like merge replication, but could equally well be transactional
> with queued updating or transactional with immediate updating subscribers.
> Have a look in BOL for the differences between these methods, or if you post
> back with details of the following, someone will be able to help out:
> (a) are the publisher and subscribers continuously connected?
> (b) does the data overlap? If so, will you need any complicated conflict
> resolution methods?
> (c) will there be other subscribers coming onboard?
> (d) do all the tables have PKs?
> (e) do you want to replicate the execution of stored procs?
> As for the programming aspect, please have a look here for some details to
> start off with:
> The SQLDMO scripts on here: http://www.replicationanswers.com/Scripts.asp
> The ActiveX control scripts on here:
> http://support.microsoft.com/default...b;EN-US;319649
> http://support.microsoft.com/default...b;EN-US;319646
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||If M1 and M2 connect up to a central server, I'd set up the central server
as a publisher with M1 and M2 as subscribers. I'd suggest looking at queued
updating subscribers for this scenario, provided you are not dealing with
text/image columns (merge would be the other alternative).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Replication performing UPDATES as INSERTS and DELETES on the subscriber
I have transactional replication set up between two SQL Server 2000 databases. In some cases when I perform an UPDATE on a published table on the the publisher, SQL Server attempts to perform a DELETE followed by an INSERT on the subscriber using the stored procedures created during the initial snapshot.
Why does it do this?
How can I stop it doing this and force an UPDATE on the publisher to call the UPDATE procedure on the subscriber?
Thanks
Cut paste from a different thread, see if this helps:
>> If you can limit the update to unique columns as singleton (each update affects one row) , SQL2000 will replicate it as update. Otherwise for multi-row updates to unique columns you may consider replication of ‘proc exec’ instead.
>> You should also look at the parameter @.schema_option for sp_addarticle. There’s an option that marks FKs at the subscriber as Not For Replication, this may be what you’re looking for. Enabling this should work if you don’t have any other fancy business-triggers at the subscriber that are breaking due to the delete/insert.
|||What's happening is called a deferred update. See kb 238254 for more info. This is a by design behavior.
Is this causing any problems in your scenario? Are subscriber-side triggers breaking? Are you getting referential integrity errors?
Two common workarounds are to replicate the update via proc exec, or enable schema option 0x20000 in sp_addarticle, which does not enforce RI constraints if raised by replication agent.
Monday, March 12, 2012
Replication over the internet
I want to be able to replicate large volumes of data over the Internet
between SQL Server 2000 databases. The transmissions would be once per day,
need to be secure, and are to a location three time zones away.
What do you recommend for testing, security, support (the whole nine yards)?
TIA George
Ideally you should use a VPN for truly secure solutions. For testing you
would have to use representative loads and representative hardware. Support
is a tough one, as the problems you will see tend to be environment and
application specific.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"George Morton" <gmorton1@.optonline.net> wrote in message
news:k1GAd.18165$lU1.13967@.fe09.lga...
> I want to be able to replicate large volumes of data over the Internet
> between SQL Server 2000 databases. The transmissions would be once per
day,
> need to be secure, and are to a location three time zones away.
> What do you recommend for testing, security, support (the whole nine
yards)?
> TIA George
>
between SQL Server 2000 databases. The transmissions would be once per day,
need to be secure, and are to a location three time zones away.
What do you recommend for testing, security, support (the whole nine yards)?
TIA George
Ideally you should use a VPN for truly secure solutions. For testing you
would have to use representative loads and representative hardware. Support
is a tough one, as the problems you will see tend to be environment and
application specific.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"George Morton" <gmorton1@.optonline.net> wrote in message
news:k1GAd.18165$lU1.13967@.fe09.lga...
> I want to be able to replicate large volumes of data over the Internet
> between SQL Server 2000 databases. The transmissions would be once per
day,
> need to be secure, and are to a location three time zones away.
> What do you recommend for testing, security, support (the whole nine
yards)?
> TIA George
>
Replication or Update via Trigger ?
Hi SQL Gurus,
I have an application where the end user insist that there should be nine(9)
databases, same server within the system.
There are 'shared tables' on one of the database where any
insert/update/delete on those 'shared tables' must appear immediately on
other databases.
In this case, should I use replication or doing updates via Trigger ?
Thanks for your comments,
KristI am not sure why he/she is insisting for identical databases? Perhaps,
instead of creating tables in all these databases, you can opt to create
views which simply SELECT from a table in a single a database. You don't
have to worry about the data being in sync either.
Replication is not a solution for addressing such requirements. You can opt
for a trigger, but with the information from your post, I rather would
conclude it is an overkill to support redundant data for no apparent
reasons.
--
- Anith
( Please reply to newsgroups only )|||Hi Anith,
I used to think of View.
But I need to create FK from transaction table to these 'shared tables'
e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
and Area are 'shared tables'
How can I do this with view ?
Thanks,
Krist
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I am not sure why he/she is insisting for identical databases? Perhaps,
> instead of creating tables in all these databases, you can opt to create
> views which simply SELECT from a table in a single a database. You don't
> have to worry about the data being in sync either.
> Replication is not a solution for addressing such requirements. You can
opt
> for a trigger, but with the information from your post, I rather would
> conclude it is an overkill to support redundant data for no apparent
> reasons.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||Krist, first find out the reason that your user needs 9 databases.
1. If is for security then perhaps create 9 different users or roles instead
and assign permissions accordingly.
2. If it's a limitation in the front end application then perhaps you'll
need to stick with several databases
3. If the user later wants to distribute the 9 databases to 9 different
servers then cross-database views become more tricky.
It's a rather strange requirement, and the underlying reason for it probably
constrains your options even further.
"tristant" <krislioe@.cbn.net.id> wrote in message
news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I used to think of View.
> But I need to create FK from transaction table to these 'shared tables'
> e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
> and Area are 'shared tables'
> How can I do this with view ?
> Thanks,
> Krist
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I am not sure why he/she is insisting for identical databases? Perhaps,
> > instead of creating tables in all these databases, you can opt to create
> > views which simply SELECT from a table in a single a database. You don't
> > have to worry about the data being in sync either.
> >
> > Replication is not a solution for addressing such requirements. You can
> opt
> > for a trigger, but with the information from your post, I rather would
> > conclude it is an overkill to support redundant data for no apparent
> > reasons.
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>|||Hi Anthony,
You are absolutely right : the reason the number (3)
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
So, that is my reason, what options do I have now ?
Thanks,
Krist
"Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> Krist, first find out the reason that your user needs 9 databases.
> 1. If is for security then perhaps create 9 different users or roles
instead
> and assign permissions accordingly.
> 2. If it's a limitation in the front end application then perhaps you'll
> need to stick with several databases
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
> It's a rather strange requirement, and the underlying reason for it
probably
> constrains your options even further.
> "tristant" <krislioe@.cbn.net.id> wrote in message
> news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > Hi Anith,
> > I used to think of View.
> > But I need to create FK from transaction table to these 'shared tables'
> > e.g : SalesOrder must have FK to Salesman and Area table, where
Salesman
> > and Area are 'shared tables'
> >
> > How can I do this with view ?
> >
> > Thanks,
> > Krist
> >
> > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > I am not sure why he/she is insisting for identical databases?
Perhaps,
> > > instead of creating tables in all these databases, you can opt to
create
> > > views which simply SELECT from a table in a single a database. You
don't
> > > have to worry about the data being in sync either.
> > >
> > > Replication is not a solution for addressing such requirements. You
can
> > opt
> > > for a trigger, but with the information from your post, I rather would
> > > conclude it is an overkill to support redundant data for no apparent
> > > reasons.
> > >
> > > --
> > > - Anith
> > > ( Please reply to newsgroups only )
> > >
> > >
> >
> >
>|||Your options:
1. Partitioned views
2. Replication
3. Log shipping (backup & restore)
"tristant" <krislioe@.cbn.net.id> wrote in message
news:OQxaWDgxDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi Anthony,
> You are absolutely right : the reason the number (3)
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> So, that is my reason, what options do I have now ?
> Thanks,
> Krist
> "Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
> news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> > Krist, first find out the reason that your user needs 9 databases.
> > 1. If is for security then perhaps create 9 different users or roles
> instead
> > and assign permissions accordingly.
> > 2. If it's a limitation in the front end application then perhaps you'll
> > need to stick with several databases
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> >
> > It's a rather strange requirement, and the underlying reason for it
> probably
> > constrains your options even further.
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> > news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > > Hi Anith,
> > > I used to think of View.
> > > But I need to create FK from transaction table to these 'shared
tables'
> > > e.g : SalesOrder must have FK to Salesman and Area table, where
> Salesman
> > > and Area are 'shared tables'
> > >
> > > How can I do this with view ?
> > >
> > > Thanks,
> > > Krist
> > >
> > > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > > I am not sure why he/she is insisting for identical databases?
> Perhaps,
> > > > instead of creating tables in all these databases, you can opt to
> create
> > > > views which simply SELECT from a table in a single a database. You
> don't
> > > > have to worry about the data being in sync either.
> > > >
> > > > Replication is not a solution for addressing such requirements. You
> can
> > > opt
> > > > for a trigger, but with the information from your post, I rather
would
> > > > conclude it is an overkill to support redundant data for no apparent
> > > > reasons.
> > > >
> > > > --
> > > > - Anith
> > > > ( Please reply to newsgroups only )
> > > >
> > > >
> > >
> > >
> >
> >
>
I have an application where the end user insist that there should be nine(9)
databases, same server within the system.
There are 'shared tables' on one of the database where any
insert/update/delete on those 'shared tables' must appear immediately on
other databases.
In this case, should I use replication or doing updates via Trigger ?
Thanks for your comments,
KristI am not sure why he/she is insisting for identical databases? Perhaps,
instead of creating tables in all these databases, you can opt to create
views which simply SELECT from a table in a single a database. You don't
have to worry about the data being in sync either.
Replication is not a solution for addressing such requirements. You can opt
for a trigger, but with the information from your post, I rather would
conclude it is an overkill to support redundant data for no apparent
reasons.
--
- Anith
( Please reply to newsgroups only )|||Hi Anith,
I used to think of View.
But I need to create FK from transaction table to these 'shared tables'
e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
and Area are 'shared tables'
How can I do this with view ?
Thanks,
Krist
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> I am not sure why he/she is insisting for identical databases? Perhaps,
> instead of creating tables in all these databases, you can opt to create
> views which simply SELECT from a table in a single a database. You don't
> have to worry about the data being in sync either.
> Replication is not a solution for addressing such requirements. You can
opt
> for a trigger, but with the information from your post, I rather would
> conclude it is an overkill to support redundant data for no apparent
> reasons.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||Krist, first find out the reason that your user needs 9 databases.
1. If is for security then perhaps create 9 different users or roles instead
and assign permissions accordingly.
2. If it's a limitation in the front end application then perhaps you'll
need to stick with several databases
3. If the user later wants to distribute the 9 databases to 9 different
servers then cross-database views become more tricky.
It's a rather strange requirement, and the underlying reason for it probably
constrains your options even further.
"tristant" <krislioe@.cbn.net.id> wrote in message
news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> Hi Anith,
> I used to think of View.
> But I need to create FK from transaction table to these 'shared tables'
> e.g : SalesOrder must have FK to Salesman and Area table, where Salesman
> and Area are 'shared tables'
> How can I do this with view ?
> Thanks,
> Krist
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > I am not sure why he/she is insisting for identical databases? Perhaps,
> > instead of creating tables in all these databases, you can opt to create
> > views which simply SELECT from a table in a single a database. You don't
> > have to worry about the data being in sync either.
> >
> > Replication is not a solution for addressing such requirements. You can
> opt
> > for a trigger, but with the information from your post, I rather would
> > conclude it is an overkill to support redundant data for no apparent
> > reasons.
> >
> > --
> > - Anith
> > ( Please reply to newsgroups only )
> >
> >
>|||Hi Anthony,
You are absolutely right : the reason the number (3)
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
So, that is my reason, what options do I have now ?
Thanks,
Krist
"Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> Krist, first find out the reason that your user needs 9 databases.
> 1. If is for security then perhaps create 9 different users or roles
instead
> and assign permissions accordingly.
> 2. If it's a limitation in the front end application then perhaps you'll
> need to stick with several databases
> 3. If the user later wants to distribute the 9 databases to 9 different
> servers then cross-database views become more tricky.
> It's a rather strange requirement, and the underlying reason for it
probably
> constrains your options even further.
> "tristant" <krislioe@.cbn.net.id> wrote in message
> news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > Hi Anith,
> > I used to think of View.
> > But I need to create FK from transaction table to these 'shared tables'
> > e.g : SalesOrder must have FK to Salesman and Area table, where
Salesman
> > and Area are 'shared tables'
> >
> > How can I do this with view ?
> >
> > Thanks,
> > Krist
> >
> > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > I am not sure why he/she is insisting for identical databases?
Perhaps,
> > > instead of creating tables in all these databases, you can opt to
create
> > > views which simply SELECT from a table in a single a database. You
don't
> > > have to worry about the data being in sync either.
> > >
> > > Replication is not a solution for addressing such requirements. You
can
> > opt
> > > for a trigger, but with the information from your post, I rather would
> > > conclude it is an overkill to support redundant data for no apparent
> > > reasons.
> > >
> > > --
> > > - Anith
> > > ( Please reply to newsgroups only )
> > >
> > >
> >
> >
>|||Your options:
1. Partitioned views
2. Replication
3. Log shipping (backup & restore)
"tristant" <krislioe@.cbn.net.id> wrote in message
news:OQxaWDgxDHA.1760@.TK2MSFTNGP10.phx.gbl...
> Hi Anthony,
> You are absolutely right : the reason the number (3)
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> So, that is my reason, what options do I have now ?
> Thanks,
> Krist
> "Anthony Faull" <anthony.faull@.NOSPAMsanlam.co.za> wrote in message
> news:egFyt5fxDHA.536@.tk2msftngp13.phx.gbl...
> > Krist, first find out the reason that your user needs 9 databases.
> > 1. If is for security then perhaps create 9 different users or roles
> instead
> > and assign permissions accordingly.
> > 2. If it's a limitation in the front end application then perhaps you'll
> > need to stick with several databases
> > 3. If the user later wants to distribute the 9 databases to 9 different
> > servers then cross-database views become more tricky.
> >
> > It's a rather strange requirement, and the underlying reason for it
> probably
> > constrains your options even further.
> >
> > "tristant" <krislioe@.cbn.net.id> wrote in message
> > news:eAJAYgfxDHA.4064@.tk2msftngp13.phx.gbl...
> > > Hi Anith,
> > > I used to think of View.
> > > But I need to create FK from transaction table to these 'shared
tables'
> > > e.g : SalesOrder must have FK to Salesman and Area table, where
> Salesman
> > > and Area are 'shared tables'
> > >
> > > How can I do this with view ?
> > >
> > > Thanks,
> > > Krist
> > >
> > > "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> > > news:#uM6uAfxDHA.1680@.TK2MSFTNGP12.phx.gbl...
> > > > I am not sure why he/she is insisting for identical databases?
> Perhaps,
> > > > instead of creating tables in all these databases, you can opt to
> create
> > > > views which simply SELECT from a table in a single a database. You
> don't
> > > > have to worry about the data being in sync either.
> > > >
> > > > Replication is not a solution for addressing such requirements. You
> can
> > > opt
> > > > for a trigger, but with the information from your post, I rather
would
> > > > conclude it is an overkill to support redundant data for no apparent
> > > > reasons.
> > > >
> > > > --
> > > > - Anith
> > > > ( Please reply to newsgroups only )
> > > >
> > > >
> > >
> > >
> >
> >
>
Replication or log shipping
I have a SQL server with 90 databases (with avg of 30
tables in each) , each needs to be replicated (Transaction
immediate)
The Distrubution databse is on this server. I have been
having problems with some of the database failing to
replicate, and I was wondering if Log shipping between
linked servers would be a better option.
Log shipping replicates the entire database. Replication will allow you to
replicate a subset of the database, allow you to subset the data, but will
not replicate system objects.
While using log shipping your users will have to go offline while the logs
are being applied, and the database must be treated as read only. You cannot
place custom indexes on the subscriber or modify the schema there.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Allan" <allen.penston@.sitel.co.uk> wrote in message
news:02dc01c4dc46$908e68d0$a601280a@.phx.gbl...
>I have a SQL server with 90 databases (with avg of 30
> tables in each) , each needs to be replicated (Transaction
> immediate)
> The Distrubution databse is on this server. I have been
> having problems with some of the database failing to
> replicate, and I was wondering if Log shipping between
> linked servers would be a better option.
>
tables in each) , each needs to be replicated (Transaction
immediate)
The Distrubution databse is on this server. I have been
having problems with some of the database failing to
replicate, and I was wondering if Log shipping between
linked servers would be a better option.
Log shipping replicates the entire database. Replication will allow you to
replicate a subset of the database, allow you to subset the data, but will
not replicate system objects.
While using log shipping your users will have to go offline while the logs
are being applied, and the database must be treated as read only. You cannot
place custom indexes on the subscriber or modify the schema there.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Allan" <allen.penston@.sitel.co.uk> wrote in message
news:02dc01c4dc46$908e68d0$a601280a@.phx.gbl...
>I have a SQL server with 90 databases (with avg of 30
> tables in each) , each needs to be replicated (Transaction
> immediate)
> The Distrubution databse is on this server. I have been
> having problems with some of the database failing to
> replicate, and I was wondering if Log shipping between
> linked servers would be a better option.
>
Replication or bcp or DTS?
Hi,
I have 2 instances of databases in 2 different cities.Data entry /
updations are going on at both the sides.At any point of time data in
both the databases should be same.Currently every alternate day, i am
performing update / insert / deletes on incremental basis on both the
sides.
Can I implement replcation? What r the requisites I have to follow for
replication?
I have 2 instances of databases in 2 different cities.Data entry /
updations are going on at both the sides.At any point of time data in
both the databases should be same.Currently every alternate day, i am
performing update / insert / deletes on incremental basis on both the
sides.
Can I implement replcation? What r the requisites I have to follow for
replication?
I do not have permenant connectyvity but I need to connect using ISDN
line to a remote location as and when required for doing
synchrinisation of data.
Thanks in advance.
Regards
Rohit
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict236236.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=819975Rohit (UseLinkToEmail@.dbForumz.com) writes:
> I have 2 instances of databases in 2 different cities.Data entry /
> updations are going on at both the sides.At any point of time data in
> both the databases should be same.Currently every alternate day, i am
> performing update / insert / deletes on incremental basis on both the
> sides.
> Can I implement replcation? What r the requisites I have to follow for
> replication?
> I do not have permenant connectyvity but I need to connect using ISDN
> line to a remote location as and when required for doing
> synchrinisation of data.
I'm a little confused, since you say that at any point in time, the
contents of the databases should be the same. But then you talk about
not having permanent connectivity. Is that the connectivity between
the databases, or your own connectivity.
The gist of replication is that the I in ACID for transactions is
relaxed. That is, transactions should normally be Atomic, Consistent,
Immediate and D-something. With replication, you say that I does not
matter. A transaction can be replicated later at any time. This is good
if you have disconnected servers. For connected servers, replication
can still be acceptable, since the delay in most cases will not be
noticeable.
Since you need to send data in both directions, it looks as if you are
into merge replication. There are probably a bunch of requirements,
for instance the table must have GUID as keys. And then you need rules
to handle conflicts. (What if the same row is updated at both sites?)
My own knowledge only goes as far about this, so I would recommend you
to ask in microsoft.sqlserver.public.replication.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"" wrote:
> Rohit (UseLinkToEmail@.dbForumz.com) writes:
> > I have 2 instances of databases in 2 different
> cities.Data entry /
> > updations are going on at both the sides.At any point of
> time data in
> > both the databases should be same.Currently every alternate
> day, i am
> > performing update / insert / deletes on incremental basis on
> both the
> > sides.
> > Can I implement replcation? What r the requisites I have to
> follow for
> > replication?
> > I do not have permenant connectyvity but I need to connect
> using ISDN
> > line to a remote location as and when required for doing
> > synchrinisation of data.
> I'm a little confused, since you say that at any point in
> time, the
> contents of the databases should be the same. But then you
> talk about
> not having permanent connectivity. Is that the connectivity
> between
> the databases, or your own connectivity.
> The gist of replication is that the I in ACID for transactions
> is
> relaxed. That is, transactions should normally be Atomic,
> Consistent,
> Immediate and D-something. With replication, you say that I
> does not
> matter. A transaction can be replicated later at any time.
> This is good
> if you have disconnected servers. For connected servers,
> replication
> can still be acceptable, since the delay in most cases will
> not be
> noticeable.
> Since you need to send data in both directions, it looks as if
> you are
> into merge replication. There are probably a bunch of
> requirements,
> for instance the table must have GUID as keys. And then you
> need rules
> to handle conflicts. (What if the same row is updated at both
> sites?)
> My own knowledge only goes as far about this, so I would
> recommend you
> to ask in microsoft.sqlserver.public.replication.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
Thanks for your reply. probably I wrote it in a wrong way.
There is no permenant connectivity between the two locations. Whenever
mself / management decides to Synch. the data I connect using ISDN
connection , perform export / Import Using BCP at both the sides and
then run SQL Script to push the incremental data inside the database.
Before going to the database it comes to a buffer database FROM text
files.
regards
Rohit
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Dis...pict236236.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=821317|||Rohit (UseLinkToEmail@.dbForumz.com) writes:
> Thanks for your reply. probably I wrote it in a wrong way.
> There is no permenant connectivity between the two locations. Whenever
> mself / management decides to Synch. the data I connect using ISDN
> connection , perform export / Import Using BCP at both the sides and
> then run SQL Script to push the incremental data inside the database.
> Before going to the database it comes to a buffer database FROM text
> files.
OK. As I said, merge replication could be worth looking into. But I know
too little of merge replication as well as your application to say for
sure that it is a good idea.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Replication Options
Hello,
New to SQL Replication.
Have SQL 2000 and wanted to replicate my business & core databases to a
server at another site to increase availability.
Is SQL replication able to keep my business application running without end
users ever knowing that one of the serves went down or are there manual steps
to be taken to get end users reconnected if I lose or down one of the SQL
servers?
Which type of replication is best suited for this?
Also, my WAN bandwidth is around 768 K upto about 1 M max betweeen sites.
Will these speeds support SQL replication ok?
Thanks much,
Mark
Paul,
Thanks for the info. Seems I'm a bit limited. I read that log shipping
requires SQL 2000 Enterprise, and I don't have it!
I quess one of the forms of Replication or an upgrade to SQL 2005 may be my
only options.
Thanks much,
Mark
"Paul Ibison" wrote:
> Hi Mark and welcome to the group
> For DR, you might want to look at log shipping. In SQL Server 2005 there is
> database mirroring which is similar but offers automatic failover that you
> mention, but this isn't there in log shipping and there willbe some final
> tweaking to get things ready, and then you'll need to point the client apps
> at the new server. Have a look here for a little more info:
> http://www.replicationanswers.com/Standby.asp, and BOL for the final steps
> needed in log shipping to bring up the standby server.
> HTH,
> Paul Ibison
>
>
|||Paul,
Could I just do a manual database transfer of a full backup every night to
the standby server, and just send a copy of the transaction logs to the
standby server say every hour without using Replication?
Any reason not to do it this way?
Thanks,
Mark
"Paul Ibison" wrote:
> Hi Mark and welcome to the group
> For DR, you might want to look at log shipping. In SQL Server 2005 there is
> database mirroring which is similar but offers automatic failover that you
> mention, but this isn't there in log shipping and there willbe some final
> tweaking to get things ready, and then you'll need to point the client apps
> at the new server. Have a look here for a little more info:
> http://www.replicationanswers.com/Standby.asp, and BOL for the final steps
> needed in log shipping to bring up the standby server.
> HTH,
> Paul Ibison
>
>
|||You can do log shipping in other editions, but it is a bit more difficult as
it isn't out of the box. At the end of the day you just need jobs to back up
logs on one server, copy to a network share on another server then a third
job to restore there (no recovery). Procedures to make this setup are on the
SQL Server resource kit and on some websites so you can use someone else's
scripts if you don't want to make your own.
Cheers,
Paul Ibison
|||Exactly - you can roll your own log shipping. Just be sure to restore using
NO RECOVERY and the rest is pretty straightforward.
Cheers,
Paul Ibison
New to SQL Replication.
Have SQL 2000 and wanted to replicate my business & core databases to a
server at another site to increase availability.
Is SQL replication able to keep my business application running without end
users ever knowing that one of the serves went down or are there manual steps
to be taken to get end users reconnected if I lose or down one of the SQL
servers?
Which type of replication is best suited for this?
Also, my WAN bandwidth is around 768 K upto about 1 M max betweeen sites.
Will these speeds support SQL replication ok?
Thanks much,
Mark
Paul,
Thanks for the info. Seems I'm a bit limited. I read that log shipping
requires SQL 2000 Enterprise, and I don't have it!
I quess one of the forms of Replication or an upgrade to SQL 2005 may be my
only options.
Thanks much,
Mark
"Paul Ibison" wrote:
> Hi Mark and welcome to the group
> For DR, you might want to look at log shipping. In SQL Server 2005 there is
> database mirroring which is similar but offers automatic failover that you
> mention, but this isn't there in log shipping and there willbe some final
> tweaking to get things ready, and then you'll need to point the client apps
> at the new server. Have a look here for a little more info:
> http://www.replicationanswers.com/Standby.asp, and BOL for the final steps
> needed in log shipping to bring up the standby server.
> HTH,
> Paul Ibison
>
>
|||Paul,
Could I just do a manual database transfer of a full backup every night to
the standby server, and just send a copy of the transaction logs to the
standby server say every hour without using Replication?
Any reason not to do it this way?
Thanks,
Mark
"Paul Ibison" wrote:
> Hi Mark and welcome to the group
> For DR, you might want to look at log shipping. In SQL Server 2005 there is
> database mirroring which is similar but offers automatic failover that you
> mention, but this isn't there in log shipping and there willbe some final
> tweaking to get things ready, and then you'll need to point the client apps
> at the new server. Have a look here for a little more info:
> http://www.replicationanswers.com/Standby.asp, and BOL for the final steps
> needed in log shipping to bring up the standby server.
> HTH,
> Paul Ibison
>
>
|||You can do log shipping in other editions, but it is a bit more difficult as
it isn't out of the box. At the end of the day you just need jobs to back up
logs on one server, copy to a network share on another server then a third
job to restore there (no recovery). Procedures to make this setup are on the
SQL Server resource kit and on some websites so you can use someone else's
scripts if you don't want to make your own.
Cheers,
Paul Ibison
|||Exactly - you can roll your own log shipping. Just be sure to restore using
NO RECOVERY and the rest is pretty straightforward.
Cheers,
Paul Ibison
Friday, March 9, 2012
replication on demand
Hi,
I am writing C# application accessing 2 databases. One them is publisher
and replicates two of its tables to the other (transactional replication). I
know that replication can either be immediate or scheduled, but is there any
way to perform replication on demand (from my c# app), for instance
executing some sql statement?!
Piotrek
have a look at the initialize and run methods using the activeX controls.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Piotrek Stachowicz" <dynamite@.poczta.onet.pl> wrote in message
news:cdlqsg$r30$1@.news.onet.pl...
> Hi,
> I am writing C# application accessing 2 databases. One them is
publisher
> and replicates two of its tables to the other (transactional replication).
I
> know that replication can either be immediate or scheduled, but is there
any
> way to perform replication on demand (from my c# app), for instance
> executing some sql statement?!
> Piotrek
>
|||Hi,
> have a look at the initialize and run methods using the activeX
controls.
Could you be a slightly more specific (some msdn reference perhaps?). I'm
new to the topic, and I am not quite sure what you mean.
Thanks,
Piotrek
|||here is a code sample of what I am talking about
http://support.microsoft.com/default...&Product=sql2k
For more info on the actual methods have a look at
http://msdn.microsoft.com/library/de...bjcol_00j8.asp
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Piotrek Stachowicz" <dynamite@.poczta.onet.pl> wrote in message
news:cdnrb9$t9f$1@.news.onet.pl...
> Hi,
> controls.
> Could you be a slightly more specific (some msdn reference perhaps?). I'm
> new to the topic, and I am not quite sure what you mean.
> Thanks,
> Piotrek
>
>
I am writing C# application accessing 2 databases. One them is publisher
and replicates two of its tables to the other (transactional replication). I
know that replication can either be immediate or scheduled, but is there any
way to perform replication on demand (from my c# app), for instance
executing some sql statement?!
Piotrek
have a look at the initialize and run methods using the activeX controls.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Piotrek Stachowicz" <dynamite@.poczta.onet.pl> wrote in message
news:cdlqsg$r30$1@.news.onet.pl...
> Hi,
> I am writing C# application accessing 2 databases. One them is
publisher
> and replicates two of its tables to the other (transactional replication).
I
> know that replication can either be immediate or scheduled, but is there
any
> way to perform replication on demand (from my c# app), for instance
> executing some sql statement?!
> Piotrek
>
|||Hi,
> have a look at the initialize and run methods using the activeX
controls.
Could you be a slightly more specific (some msdn reference perhaps?). I'm
new to the topic, and I am not quite sure what you mean.
Thanks,
Piotrek
|||here is a code sample of what I am talking about
http://support.microsoft.com/default...&Product=sql2k
For more info on the actual methods have a look at
http://msdn.microsoft.com/library/de...bjcol_00j8.asp
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Piotrek Stachowicz" <dynamite@.poczta.onet.pl> wrote in message
news:cdnrb9$t9f$1@.news.onet.pl...
> Hi,
> controls.
> Could you be a slightly more specific (some msdn reference perhaps?). I'm
> new to the topic, and I am not quite sure what you mean.
> Thanks,
> Piotrek
>
>
Replication of Partitioned Table
I have a table partitioned in 2005 EE and also replicate it to other
SQL Server databases. There is a filter on the replication, so not all
the records are replicated. So, with the fewer number of records, I
don't want to include the partitioning on the subscriber databases.
Also, I'm not 100% positive all customers of the product use EE on the
subscribing databases. My question is: can I replicate the partitioned
table and not require it to be partitioned on the subscribing databases?
Its difficult to replicate a table partition's structure to the subscriber.
By default you will end up with a plain vanilla table on the 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
"Ken" <kenneth_hardy85@.hotmail.com> wrote in message
news:1169135380.823972.240550@.v45g2000cwv.googlegr oups.com...
>I have a table partitioned in 2005 EE and also replicate it to other
> SQL Server databases. There is a filter on the replication, so not all
> the records are replicated. So, with the fewer number of records, I
> don't want to include the partitioning on the subscriber databases.
> Also, I'm not 100% positive all customers of the product use EE on the
> subscribing databases. My question is: can I replicate the partitioned
> table and not require it to be partitioned on the subscribing databases?
>
SQL Server databases. There is a filter on the replication, so not all
the records are replicated. So, with the fewer number of records, I
don't want to include the partitioning on the subscriber databases.
Also, I'm not 100% positive all customers of the product use EE on the
subscribing databases. My question is: can I replicate the partitioned
table and not require it to be partitioned on the subscribing databases?
Its difficult to replicate a table partition's structure to the subscriber.
By default you will end up with a plain vanilla table on the 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
"Ken" <kenneth_hardy85@.hotmail.com> wrote in message
news:1169135380.823972.240550@.v45g2000cwv.googlegr oups.com...
>I have a table partitioned in 2005 EE and also replicate it to other
> SQL Server databases. There is a filter on the replication, so not all
> the records are replicated. So, with the fewer number of records, I
> don't want to include the partitioning on the subscriber databases.
> Also, I'm not 100% positive all customers of the product use EE on the
> subscribing databases. My question is: can I replicate the partitioned
> table and not require it to be partitioned on the subscribing databases?
>
Replication of DATABASES
I have a database that I cannot make creations of tables and or you consult.
would like to know how to create one replies of this updated database
(immediate or of times in times), and I will be able to in this new one to
create tables and querys.Frank,
You might want to check theReplication topics in Books OnLine.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:u502LVUYDHA.1580@.tk2msftngp13.phx.gbl...
> I have a database that I cannot make creations of tables and or you
consult.
> would like to know how to create one replies of this updated database
> (immediate or of times in times), and I will be able to in this new one to
> create tables and querys.
>
>
would like to know how to create one replies of this updated database
(immediate or of times in times), and I will be able to in this new one to
create tables and querys.Frank,
You might want to check theReplication topics in Books OnLine.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:u502LVUYDHA.1580@.tk2msftngp13.phx.gbl...
> I have a database that I cannot make creations of tables and or you
consult.
> would like to know how to create one replies of this updated database
> (immediate or of times in times), and I will be able to in this new one to
> create tables and querys.
>
>
Subscribe to:
Posts (Atom)