Replication scnerio

Hi all,

I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.

Here is the source table and the other table's structure for reference:

DataLog

Name Datatype Size

DataLogStampID int 4
Value float 8
QuantityID smallint 2

DataLogStamp

ID (Which is foreign key to DataLogStampID field in the above table) int 4

SourceID smallint 2

TimestampSoruceLT datatime 8

What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.

I am thinking of developing a procedure which follows the following step using a temp table:

This procedure will run (say) every night.

The temp table will contain a timestamp field.

For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.

I will record this timestamp into a repl_status table which holds the timestamp of the last replication.

The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).

Any comments would be greatly appreciated.

Thanks for your time in advance,

Sinan Topuz

Hi,

Last year I made a query that replicated a table between two databases (say db1 and db2) on two remote servers (say from SRV1 to SRV2).

I added a column called "_replicated" to the table of SRV1.db1 which took binary values (0 or 1). 0 meant not yet replicated and 1 meant already replicated.

When I run the query, it inserted into SRV2.db2.table the rows of SRV1.db1.table that had "_replicated = 0".

Then I updated the column's values to 1 for SRV1.db1.table. In case of an error it would roll back the transaction.

Before execution, I also dropped the indexes on SRV2.DB2 to speed up the insertion and then recreated them.

Be sure you have a recent backup of SRV2.db2 before 'playing' in this manner (or maybe create it exactly before every replication just in case)

GOOD LUCK!

|||

Hi JohDas, Thank you very much for your answer. I would be able to use that method, if I were able to modify the source table, but in my case I have to find out if my algoritm is the best approach in a situation of this sort.

I did not mean that your post is not helpful, but just hoping to get some more suggestions from other experts as well.

Thanks for your time again.

Sinan Topuz

|||

sinan.topuz wrote:

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software.

Transactional replication, which replicates incremental changes, does not require a timestamp column. It does, however, require a PK column. If your tables do have a PK column, then you can easily use transactional replication by just scheduling the replication agents to run nightly.

If by chance your use of "timestamp column" is a typo, and you meant to say PK column, then you do have other options, one of which is just doing a nightly database backup, copy it to your destination, and restore it. This may or may not be faster than snapshot replication.

A third option is to use Log Shipping, you can read more about it in Books Online topic "Understanding Log Shipping" - http://msdn2.microsoft.com/en-us/library/ms187103.aspx.

|||

Greg,

The table does not have a primay key. In fact, it should not because I think it is being written constantly and with a lot of rows. Right now it has almost 3 million records. (This the way the apps designers may have thought while designing the system).

I appreciate your time and your comment.

Thanks,

Sinan Topuz

|||I reread your original post and now understand what you're trying to do, it's your own "change tracking" mechanism. We are developing a "change tracking" solution in the next release of SQL Server which would fit your needs as well. Otherwise your home grown solution should work, just make sure to test it out.|||

Thanks,

Just a correction, not because it is important, but I just wanted to give the correct info. The table has 300 million records now not 3 million.

|||

ok, now your 10-11 hours figure makes more sense :) However I would also take a look at logshipping if you can.

sql

Replication scnerio

Hi all,

I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.

Here is the source table and the other table's structure for reference:

DataLog

Name Datatype Size

DataLogStampID int 4
Value float 8
QuantityID smallint 2

DataLogStamp

ID (Which is foreign key to DataLogStampID field in the above table) int 4

SourceID smallint 2

TimestampSoruceLT datatime 8

What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.

I am thinking of developing a procedure which follows the following step using a temp table:

This procedure will run (say) every night.

The temp table will contain a timestamp field.

For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.

I will record this timestamp into a repl_status table which holds the timestamp of the last replication.

The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).

Any comments would be greatly appreciated.

Thanks for your time in advance,

Sinan Topuz

Hi,

Last year I made a query that replicated a table between two databases (say db1 and db2) on two remote servers (say from SRV1 to SRV2).

I added a column called "_replicated" to the table of SRV1.db1 which took binary values (0 or 1). 0 meant not yet replicated and 1 meant already replicated.

When I run the query, it inserted into SRV2.db2.table the rows of SRV1.db1.table that had "_replicated = 0".

Then I updated the column's values to 1 for SRV1.db1.table. In case of an error it would roll back the transaction.

Before execution, I also dropped the indexes on SRV2.DB2 to speed up the insertion and then recreated them.

Be sure you have a recent backup of SRV2.db2 before 'playing' in this manner (or maybe create it exactly before every replication just in case)

GOOD LUCK!

|||

Hi JohDas, Thank you very much for your answer. I would be able to use that method, if I were able to modify the source table, but in my case I have to find out if my algoritm is the best approach in a situation of this sort.

I did not mean that your post is not helpful, but just hoping to get some more suggestions from other experts as well.

Thanks for your time again.

Sinan Topuz

|||

sinan.topuz wrote:

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software.

Transactional replication, which replicates incremental changes, does not require a timestamp column. It does, however, require a PK column. If your tables do have a PK column, then you can easily use transactional replication by just scheduling the replication agents to run nightly.

If by chance your use of "timestamp column" is a typo, and you meant to say PK column, then you do have other options, one of which is just doing a nightly database backup, copy it to your destination, and restore it. This may or may not be faster than snapshot replication.

A third option is to use Log Shipping, you can read more about it in Books Online topic "Understanding Log Shipping" - http://msdn2.microsoft.com/en-us/library/ms187103.aspx.

|||

Greg,

The table does not have a primay key. In fact, it should not because I think it is being written constantly and with a lot of rows. Right now it has almost 3 million records. (This the way the apps designers may have thought while designing the system).

I appreciate your time and your comment.

Thanks,

Sinan Topuz

|||I reread your original post and now understand what you're trying to do, it's your own "change tracking" mechanism. We are developing a "change tracking" solution in the next release of SQL Server which would fit your needs as well. Otherwise your home grown solution should work, just make sure to test it out.|||

Thanks,

Just a correction, not because it is important, but I just wanted to give the correct info. The table has 300 million records now not 3 million.

|||

ok, now your 10-11 hours figure makes more sense :) However I would also take a look at logshipping if you can.

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!

Replication scenario

Hi all,

I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.

Here is the source table and the other table's structure for reference:

DataLog

Name Datatype Size

DataLogStampID int 4
Value float 8
QuantityID smallint 2

DataLogStamp

ID (Which is foreign key to DataLogStampID field in the above table) int 4

SourceID smallint 2

TimestampSoruceLT datatime 8

What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.

I am thinking of developing a procedure which follows the following step using a temp table:

This procedure will run (say) every night.

The temp table will contain a timestamp field.

For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.

I will record this timestamp into a repl_status table which holds the timestamp of the last replication.

The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).

Any comments would be greatly appreciated.

Thanks for your time in advance,

Sinan Topuz

Hi,

Last year I made a query that replicated a table between two databases (say db1 and db2) on two remote servers (say from SRV1 to SRV2).

I added a column called "_replicated" to the table of SRV1.db1 which took binary values (0 or 1). 0 meant not yet replicated and 1 meant already replicated.

When I run the query, it inserted into SRV2.db2.table the rows of SRV1.db1.table that had "_replicated = 0".

Then I updated the column's values to 1 for SRV1.db1.table. In case of an error it would roll back the transaction.

Before execution, I also dropped the indexes on SRV2.DB2 to speed up the insertion and then recreated them.

Be sure you have a recent backup of SRV2.db2 before 'playing' in this manner (or maybe create it exactly before every replication just in case)

GOOD LUCK!

|||

Hi JohDas, Thank you very much for your answer. I would be able to use that method, if I were able to modify the source table, but in my case I have to find out if my algoritm is the best approach in a situation of this sort.

I did not mean that your post is not helpful, but just hoping to get some more suggestions from other experts as well.

Thanks for your time again.

Sinan Topuz

|||

sinan.topuz wrote:

Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software.

Transactional replication, which replicates incremental changes, does not require a timestamp column. It does, however, require a PK column. If your tables do have a PK column, then you can easily use transactional replication by just scheduling the replication agents to run nightly.

If by chance your use of "timestamp column" is a typo, and you meant to say PK column, then you do have other options, one of which is just doing a nightly database backup, copy it to your destination, and restore it. This may or may not be faster than snapshot replication.

A third option is to use Log Shipping, you can read more about it in Books Online topic "Understanding Log Shipping" - http://msdn2.microsoft.com/en-us/library/ms187103.aspx.

|||

Greg,

The table does not have a primay key. In fact, it should not because I think it is being written constantly and with a lot of rows. Right now it has almost 3 million records. (This the way the apps designers may have thought while designing the system).

I appreciate your time and your comment.

Thanks,

Sinan Topuz

|||I reread your original post and now understand what you're trying to do, it's your own "change tracking" mechanism. We are developing a "change tracking" solution in the next release of SQL Server which would fit your needs as well. Otherwise your home grown solution should work, just make sure to test it out.|||

Thanks,

Just a correction, not because it is important, but I just wanted to give the correct info. The table has 300 million records now not 3 million.

|||

ok, now your 10-11 hours figure makes more sense :) However I would also take a look at logshipping if you can.

Replication Question

Hello everyone,

We are desperately wanting to perform transactional replication from a Sybase 12.5 Databse to an SQL server 2000 database. Unfortuneately we cannot migrate the entire Sybase system to SQL server!! Bascially we want to replicate a small subset of the tables from the Sybase database to a SQL server where we can manipulate this isolated data...

Any suggestuions on wether this is possible, and what new software products we may need, will be greatly appreciated.

Thank you

Chris

Moving this thread to the Replication Forum. Hopefully they can answer your question.

-Jeffrey

|||

SQL Server 2000 doesn't support hetero as publisher yet. SQL Server 2005 support Hetero DB server as publisher and/or subscriber, but so far it only supports Oracle and DB2.

Thanks

Yunjing

|||

Hi,

I have SQL Server 2005 Evaluation Edition for a POC for a client. ISQL2K5 Eval.Ed. doesn't support replication with Sybase. Do you have any idea how to set this up? I am recommneding SQL Server 2005 as a choice for their database for go live. They might not consider it if It will not support Sybase replication as their other functional systems are using Sybase as backend.

Any thoughts?

Thanks,

Mahesh.

|||

there is a code sample which shows you how to write your own distributor interface for any provider. It is the samppub one in the code samples directory. You can find it in c:\Program Files\microsoft SqL Server\80\Tools\DevTools\Samples\SQLRepl\Samppub.

If you don't want to write your own interface you can use a product like Data Mirror.

You may also be able to use DTS to pull data locally into a staging table and then load it to your production tables.

|||You will have to write something yourself or use SSIS for this.

Replication Question

Hello everyone,

We are desperately wanting to perform transactional replication from a Sybase 12.5 Databse to an SQL server 2000 database. Unfortuneately we cannot migrate the entire Sybase system to SQL server!! Bascially we want to replicate a small subset of the tables from the Sybase database to a SQL server where we can manipulate this isolated data...

Any suggestuions on wether this is possible, and what new software products we may need, will be greatly appreciated.

Thank you

Chris

Moving this thread to the Replication Forum. Hopefully they can answer your question.

-Jeffrey

|||

SQL Server 2000 doesn't support hetero as publisher yet. SQL Server 2005 support Hetero DB server as publisher and/or subscriber, but so far it only supports Oracle and DB2.

Thanks

Yunjing

|||

Hi,

I have SQL Server 2005 Evaluation Edition for a POC for a client. ISQL2K5 Eval.Ed. doesn't support replication with Sybase. Do you have any idea how to set this up? I am recommneding SQL Server 2005 as a choice for their database for go live. They might not consider it if It will not support Sybase replication as their other functional systems are using Sybase as backend.

Any thoughts?

Thanks,

Mahesh.

|||

there is a code sample which shows you how to write your own distributor interface for any provider. It is the samppub one in the code samples directory. You can find it in c:\Program Files\microsoft SqL Server\80\Tools\DevTools\Samples\SQLRepl\Samppub.

If you don't want to write your own interface you can use a product like Data Mirror.

You may also be able to use DTS to pull data locally into a staging table and then load it to your production tables.

|||You will have to write something yourself or use SSIS for this.sql

Replication Question

Hello everyone,

We are desperately wanting to perform transactional replication from a Sybase 12.5 Databse to an SQL server 2000 database. Unfortuneately we cannot migrate the entire Sybase system to SQL server!! Bascially we want to replicate a small subset of the tables from the Sybase database to a SQL server where we can manipulate this isolated data...

Any suggestuions on wether this is possible, and what new software products we may need, will be greatly appreciated.

Thank you

Chris

Moving this thread to the Replication Forum. Hopefully they can answer your question.

-Jeffrey

|||

SQL Server 2000 doesn't support hetero as publisher yet. SQL Server 2005 support Hetero DB server as publisher and/or subscriber, but so far it only supports Oracle and DB2.

Thanks

Yunjing

|||

Hi,

I have SQL Server 2005 Evaluation Edition for a POC for a client. ISQL2K5 Eval.Ed. doesn't support replication with Sybase. Do you have any idea how to set this up? I am recommneding SQL Server 2005 as a choice for their database for go live. They might not consider it if It will not support Sybase replication as their other functional systems are using Sybase as backend.

Any thoughts?

Thanks,

Mahesh.

|||

there is a code sample which shows you how to write your own distributor interface for any provider. It is the samppub one in the code samples directory. You can find it in c:\Program Files\microsoft SqL Server\80\Tools\DevTools\Samples\SQLRepl\Samppub.

If you don't want to write your own interface you can use a product like Data Mirror.

You may also be able to use DTS to pull data locally into a staging table and then load it to your production tables.

|||You will have to write something yourself or use SSIS for this.

Replication problem

How to perform replication suppose on two tables but NOT for all columns'
Suppose if i have IDENTITY column in table i can use option NOT FOR
REPLICATION, however in case my column doesn't identity, so '
Message posted via http://www.webservertalk.comI THINK IT'S ONLY FOR identity COLUMNS
Message posted via http://www.webservertalk.com

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.