Tag Info

Panic over ! When adding the stored procedure to the Article, in the properties there is a setting called 'replicate'. By default this is schema only. When i changed this to Execution of the stored procedure' instead of 'Execution in a serialized transaction of the SP'. and restarted all the agents, it worked as one transaction this time

You can do this, but keep in mind that your new column will be wiped out if the subscription is ever reinitialized. ie. The DBA at the publisher alters the publication to add a table, corruption, maintenance, etc. and the snapshot needs to be reapplied, any changes you have made to the replicated schema are dropped and recreated from the publication. So any ...

I tested it out, and yes, this works, even under Peer-to-Peer. (My original comment was incorrect as that only applies to physical columns.) Given that you don't control the publication, I would recommend naming the column(s) you add very carefully so as to not interfere with any future schema changes. Also, if this database backs a 3rd-party ISV ...

1.Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out. There will be an impact on the publisher, but depending on how you set up your transactional replication schema that can be very small. One big problem that can become an issue is if you have a local distributor, especially ...

1. Does the transaction replication have any load on the primary server(pardon my use of generic terms) when its moving data out. Many factors that it depends -- On the activity on the primary server which will be the Publisher server. Also, if the distribution agent on the same or different server matters a lot as it will be doing the heavy work of ...

I'm not sure about the core of your question ("Can you set up replication between SQL Server 2005 and SQL Azure Database?"), but my assumption is a resounding "NO". There are tools to migrate from SQL Server to Azure though - it's been discussed on Stack Overflow here and here. The consensus seems to be that This SQL Azure Migration Wizard (CodePlex hosted ...

There are perfmon counters that you can probably use for what you're trying to do. Since it's a counter provided by SQL, it's available in sys.dm_os_performance_counters. Try select * from sys.dm_os_performance_counters where object_name like '%repl%'. Slice and dice as necessary.

It depends on your workload. As a baseline, use Performance Monitor and log the network counters when not using Log Shipping or Transactional Replication and measure for 1 hour. Then setup Log Shipping and use Performance Monitor to log the network counters for 1 hour. Then setup Transactional Replication and use Peformance Monitor to log the network ...

A few thoughts. It sounds to me like you may be able to get away with a couple options if replication is what you end up with. First a word of caution - Replication isn't something that should just be configured in production and enabled/used unless you have some experience with it. This becomes truer with more important workloads and busier systems. ...

Why don't you setup your environment to replicate from DR1 to DR2 instead of from D2 to DR2. That way you don't have to re-initialize or -synch anything on failover. You just need to make sure that you fail over both D1 to DR1 and D2 to DR2 together. If that is not an option, you could also look into clustering. if D1 and DR1 build one cluster C1 and D2 ...

I'm not an expert in replication but I believe you want Merge replication at least in part. Transactional replication will not support your 3.1 requirement. Ie syncing the data back together. Only Merge replication is designed for that. I'm not 100% certain but I believe you can set up transactional replication for the data you only want to be read-only ...

SQL Server Replication isn't a good way to handle a database upgrade. A much better approach would be to take a full backup and restore then do your testing. After completing your testing you do a full backup and restore, then perform log restores until you are ready to cut over the database. After you take the production system offline you finish the log ...

The error is caused by the code in the custom delete stored procedure for each article: create procedure [dbo].[sp_MSdel_dboChild] @pkc1 int as begin delete [dbo].[Child] where [ChildID] = @pkc1 if @@rowcount = 0 if @@microsoftversion>0x07320000 exec sp_MSreplraiserror 20598 end If no row is deleted, a special procedure is ...

Transactional replication can be used for something like this, but it would be tricky. However it sounds like what you are doing is keeping multiple front facing caches up to date. This is tricky as a polling mechanism limits scalability. What works best is a push mechanism where if a change occurs, or some data meets a certain criteria it is send to all ...

To answer the question, you would need to test how many rows your change will affect (an update to a column without a where clause, or adding a column will affect all rows). A reindex will not cause replication to be "rebuilt". Can you clarify what do you mean by "rebuilt" do you mean all rows are re-replicated? or do you mean you are forced to snapshot? To ...

In the end I simply did what the server wanted, i.e. created all the "publication" tables (dbo.MSpeer_* and dbo.sys*) in the problematic database (the one that was the publisher), through Script Table As -> CREATE To dialog. Thanks to this server allowed me to disable publishing and distribution. Then I only needed to reconfigure replication. Hope, next ...

According to Define an Article: If the publication has existing subscriptions and sp_helppublication returns a value of 0 in the immediate_sync column, you must call sp_addsubscription to add the article to each existing subscription. After executing sp_addarticle, execute sp_addsubscription to add the article to each existing subscription. Then ...

Examine the output of sp_helpsubscriptionerrors. One issue I have seen is with DDL commands; they are treated differently in replication so if manual schema changes are being made to the Publisher, they can sometimes fail. There may also be specific DML commands failing on the subscriber due to transactional inconsistencies. If this is the case, you can use ...

This is a bug with Replicating BLOBS in transactional replication. when the snapshot agent ran, something updated the BLOBs currently in the publication and this broke ALL replication streams. After removing the initial table from the publication it moved onto another table containing BLOB data. To resolve this I separated all tables containing BLOB data ...

When dealing with SQL 2000 replication, the replication bits does not get cleaned up easily. You can follow this method for cleaning up the left over bits. As Denny mentioned, they are safe to remove as the replication is already removed.

generally transactional has a publisher and a subscriber, with some updating from the subscriber, peer to peer replication shares the same data in more of a node environment (often over long distances) and would be more suitable for load balancing. I'd go straight to technet for the overview ...