SQL2005 Peer-to-Peer Transactional Repl. Table Constraints
Nov 10, 2005I am researching the possible use of Peer-to-Peer Transactional Replication with SQL 2005 to improve the availability and scalability of the SQL-farm.
View 5 Replies
ADVERTISEMENT
Peer-to-Peer Transactional Replication And Deadlock
Nov 3, 2006Hi all,
I have implemented Peer-to-Peer Transactional Replication only for two servers.Everything works fine but in highlevel of concurrency some updates are rolled back due to deadelocks(Never comes without replication).Is there any type of document which can guide me to avoid deadlocks (under Peer-to-Peer Transactional Replication env).
Combining Peer-to-Peer Transactional Replication With Standard Transactional Replication
Dec 1, 2006Hello,
I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:
We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.
Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?
We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.
That's the scenario.
Any feedback is welcome and appreciated.
Thanks,
Terence
MSrepl_commands Table Is Growing Very Big In Peer To Peer Replication
Oct 23, 2007We have Peer-to-Peer Replication configured and running on SQL Server 2005 EE + SP2 (64 Bit) on Windows 2003 R2 Enterprise x64 edition + SP1 platform.
Distribution clean-up job is running to keep last 4 hours of Transactional Data and last 48 hours of history data.
There are five databases set for Peer to peer replication and functionality wise everything is running fine at both ends.
Here is the problem:
1. We are running Distribution Clean-up job ever 15 minutes with Transaction retention for last 4 hours and History retention for last 48 hours, MSrepl_commands table of Distribution database is growing very fast on one publisher and has become size of 135 GB. DBCC Reindex has not helped in reducing space and sp_spaceused shows all space used out of this 139 GB of Data file where 135 GB has been occupied only by this one table, i.e. MSRepl_commands table.
MSrepl_transactions table shows entry_time from May 2007 for every database, which is even more confusing.
2. Distribution Database size on other publisher is tiny, it is just 4 GB.
I have already recycled the SQL Server/restarted Agents and added a secondary Data file on Distribution DB but this did not resolve any problem.
Please suggest.
Many Thanks in Advance
Surbhi
Installing Sql Express On A Peer To Peer Network Log In And Password Problems
Mar 3, 2007I am trying to set up Sql Server Express on a peer to peer network. I need to set a log in and password for the service so that I can access the DBs from any machine. (ultimately I will be using VB but for now I am just trying to make the connecton through the Management Component studio). I have sql express installed on both machines. I can not find anywhere it will allow me to change from Windows authentication to Sql Authentication I have tried both in the properties of Sql Express management studio and the local Computer management. Help please!!!
View 3 Replies View RelatedRookie Question? Peer-to-Peer Replication Not Detecting Conflict
Mar 8, 2007Hi
Sorry for asking what may be a rookie question... We have configured transactional peer-to-peer replication in a testing environment with two servers, each publishing and subscribing to each other. If we write a piece of code that updates the same row on both servers with different values, we are not seeing data conflict issues. The data updated on server 1 propagates to server 2, but at the same time data updated on server 2 is updated on server 1. This leaves the records on each server out of data.
I expected my test case to produce errors, but instead I got inconsistent data. How do you turn on data consistency checking?
Thanks in advance
Using SQL Server 2005 Express In A Windows Peer-to-peer Workgroup
Mar 6, 2006Does anyone have a experience or links to resources for implimenting SQL Server 2005 Express in a Windows peer-to-peer workgroup?
Thanks.
SQL 2005 Peer To Peer Replication Bandwidth Issue
Mar 11, 2008Hi All,
I am setting up P2P replication for a high latency environment (although at 100MBit per sec, the bandwidth is not a serious issue ).
I have noticed that at high load, the bandwidth between Distribution and Subscriber servers maxes out at 1MBit per second (our network bandwidth is 100MBit per second).
This causes transactions to 'back up' and when the load reduces they 'catch up' and synchronise.
Does anyone know where this limitation of 1Mbs is being enforced and what is the way round it?
I have seen the -SubscriptionStreams NN parameter, but this is not applicable in P2P replication.
Any advice would be appreciated.
Thanks
Aasim
Transaction Peer-To-Peer Replication Error MSSQL_REPL20011
Apr 18, 2006I set up a peer-to-peer replication among three servers. To do the setup, I set up replication on Server A, created a backup of that database, then restored the backup to Servers B and C. Then I went back to Server A and created the peer-to-peer topology. When I loade the replication monitor, Sever A has nothing but errors. Server B and C allegedly have no errors but they aren't getting any data from Server A. I've googled and searched here for the replication log errors and came up completely empty.
Here are the errors:
The process could not execute 'sp_repldone/sp_replcounters' on 'SERVERA'. (Source: MSSQL_REPL, Error number:MSSQL_REPL20011)
The specified LSN {00000000:00000000:0000} for repldone log scan occurs before the current start of replication in the log {00000bdd:00013151:0001}. (Source: MSSQLServer, Error number: 18768)
The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
The process could not execute 'sp_repldone/sp_replcounters' on 'SERVERA'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
These four errors are repeated approximately every minute. If anybody can help me out, I'd really appreciate it!
Thanks.
Randy
Can Not Get Sqlcmd And Unable To Create Peer-to-peer Replication.
Sep 12, 2007Hi, Pros,
I am new to SQL server 2005. We have installed SQL server 2005 cluster. I tried to setup peer-to-peer replication with another standard server. Unfortunately, when I creating publication, I got error message : SQL server cound not configure "MyServer" as a Distributor [New publication Wizard] Additional information: An exception occurred whild executing a Transact_SQL statement or batch.
The Server "Myserveris already defined as a Distributor. To reconfigure the server as a Distributor, you must first uninstall the exisitingDistributor. Use the stored procedure sp_dropdistributor, or use the Disable Publishing and Distribution Wizard. Changed database context to 'master'. [Microsoft SQL Server, Error: 14099]
I tried to use sqlcmd and I got another error message:
C:>sqlcmd
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
Does anyone have any idea how to get the sqlcmd work?
Thank you in advance.
How To Block Access During Peer-to-peer Transaction Recovery?
Dec 27, 2006Is there a clean way to block access to a peer which has been offline, during peer-to-peer transaction recovery (while the outstanding transactions are being applied)?
Thanks, Liston
Peer To Peer - Pending Change Stuck In Queue
Dec 14, 2006In a peer to peer setup, Ann modifies a row on instance A while Bill deletes it on instance B. Bill's delete passes Ann's modify in the replication message queues; the delete succeeds on instance A, but the pending modify fails and blocks further changes from instance A. How do I get the modify out of the queue?
Thanks, Liston
PS: Yes, I know you're not supposed to do this. Why doesn't peer to peer replication come under the perview of the distributed transaction coordinator? Please - put this on my wish list.
Peer-to-Peer Replication (Programmatic Control And Setup)
Feb 7, 2007I have reviewed the BOL documentation on how to configure Peer-to-Peer replication via T-SQL and how to use the Replication Wizard to implement replication.
What I would like to find out is how do I configure the peer-to-peer replication process to use an existing column on a table that contains a GUID instead of creating an extra column with a uniqueidentifier GUID value. When you use the Wizard each table article has this extra column added to it.
I don't seem to be able to find it in the books-on-line. Can some one point me to the correct article or BOL page.
Thank you.
...cordell...
Where Is Configure Peer-to-Peer Topology Option?
Mar 22, 2007Where is Configure Peer-to-Peer Topology option, in transactional replication?
I have created a publication using,
http://msdn2.microsoft.com/en-us/library/ms152536.aspx#
but not able to find Peer-to-Peer Topology option...
Can any one guide here
I have refreshed all nodes in ssms ;-)
------------------------
I think, therefore I am - Rene Descartes
Peer-to-Peer Replication In MS SQL Server 2005
Nov 7, 2006Hello
Question:How to configrure the load balancing(Read/Writes) in Peer to Peer Replication in SQL Server 2005?How to send Write request to a single,dedicated server and spread out the Read requests among the remaining boxes?
Thank you in advance
SA
Peer To Peer Replication Problem With BLOBs
May 29, 2007I've set up a development peer to peer replication between 2 SQL Server 2005 on 2 Windows XP SP2 systems.
The distribution agent is configured to use UseOledbStreaming. When saving smaller BLOBs of approx 10000 KB all works fine. When I try to replicate larger data I get the following error message in the Synchronisation status window and in the history of the distributor to the subscriber tab in the replication monitor:
Der Verteilungs-Agent verwendet die OLE DB-Streamingoptimierung für die Replikation von BLOB-Daten (Binary Large Objects) mit mehr als 16384 Bytes. Der Schwellenwert für die Optimierung kann über die Befehlszeile mithilfe des -OledbStreamThreshold-Parameters festgelegt werden.
(Quelle: MSSQL_REPL, Fehlernummer: MSSQL_REPL21060)
Hilfe abrufen: http://help/MSSQL_REPL21060
I also tried different values of OledbStreamThreshold.
I need to be able to replicate BLOBs in size of up to 1 GB.
I'm writing the BLOB data in chunks to the table DB_BLOBData , that isn't an article for replication and at the end copy it to the destination table MyBLOBTable which is an article. I do this, because I noticed that the transaction log on the subscriber database becomes incredibly huge (>60 GB for maybe 50 1000KB BLOBs written in blocks size of 65536 bytes) when directly updating the row using BLOB.Write(...). I think, that for each write at least the whole BLOB contents is written to the transaction log.
The replication uses Stored procedures, Update using SCALL syntax.
The code of the SP:
...
IF @action = 1
BEGIN
UPDATE MyBLOBTable
SET DocContent = (SELECT BLOB from DB_BLOBData where GuidId = @id)
, DocSize = (SELECT DATALENGTH(BLOB) from DB_BLOBData where GuidId = @id)
WHERE DocId = @id
DELETE FROM DB_BLOBData WHERE GuidId = @id
END
IF @action = 0
BEGIN
IF @Offset = 0
BEGIN
INSERT DB_BLOBData( GuidId, BLOB )
VALUES ( @id, @value )
END
ELSE
BEGIN
UPDATE DB_BLOBData
SET BLOB.Write( @value , @Offset , @Length )
WHERE GuidId = @id
END
END
SELECT @ModDttm = modDttm
, @ModUser = modUser
, @ModClient = modClient
, @ModAppl = modAppl
FROM MyBLOBTable
WHERE DocId = @id
RETURN 0
END
TIA,
Hannoman
Peer To Peer Replication And Cascading Deletes
Sep 14, 2007I was hoping someone could explain how cascading deletes work in Peer to Peer replication on SQL 2005 SP2, the child tables are also being replicated.
View 1 Replies View RelatedAccessing Sql Express Peer To Peer On Xp Home
Dec 15, 2005HI
2 pc's networked on XP home - SQL Express installed on one (on both actually, but that's probably superfluous info)
I've got the CTP studio express installed on both, but can't connect to the instance on the other machine - I've enabled TCPIP and named pipes, restarted the services, the XP filewall is disabled and still no luck - I've got mixed mode authentication...
What else can I try - how do I fix ?
THanks
Bruce
Creating Peer-to-Peer Merge Replication
Apr 6, 2006We have a situation exactly like that shown in the documentation for creating a peer-to-peer merge replication. That is, we have three servers, one each in Chicago, New York, and Bermuda. Users in each office will read and write to their respective servers and the servers will then replicate with one another. The documentation, however, only shows how to set up peer-to-peer for a transactional replication. The problem is that the various Manger Studio options and the database stored procedures are NOT the same between transactional and merge replication and on the face of it, merged replication does not support a peer-to-peer topology. Can somebody walk me through the process of creating a peer-to-peer merge replication, or else convince me that I should go with transaction with updating subscriptions -- in contravention of what the documentation seems to reccommend? I'd really appreciate it!
Thanks.
Randy
RMO ReplicationDatabase Class Can't Program Peer-to-Peer?
May 11, 2006Just reading the documentation on RMO programming and noticed that it is silent about peer-to-peer replication. Further, the ReplicationDatabase class has neither a property indicating whether peer-to-peer is enabled, nor a method (or get/set) to enable peer-to-peer. Is peer-to-peer publication outside the scope of RMO programming?
Curious minds want to know...
Thanks.
Randy
Connect To SQLserver In Peer-To-Peer Env.
May 14, 2007I have a few machines set in a workgroup which I want to use for testing SQL Server but I don't seem to be able to connect to an instance from a peer client. I can ping the machine and removed the firewall and can remote desktop.
any advice, thanks
Problem With Peer To Peer Replication Across A VPN
Mar 5, 2007We are experiencing some problems with peer to peer replication configuration across a VPN between a 32 bit and 64 bit SQL server 2005. We have been able to configure it but we notice that on successful completion of the configuration the 64 bit machine does not have a subscription for the 32 bit machine article. But both servers have articles created and only the 32 bit machine has a subscription. This is a bizarre experience because the configuration was successful.
We know the server windows account has administrative rights at both end and we can telnet each server successful and all the necessary ports are open.
Might feeling is that the firewall or VPN us acting up but I cannot prove it..
I need help or advice about how to resolve this€¦
Can't Allow Peer To Peer - SQL Server 2005
Nov 22, 2006I'm trying to set up the first distribution server for a peer to peer transactional replication (updateable subscription) between two SQL Server 2005 instances on one machine (for testing). Can this be done without "virtual servers", and if so how do I enable the "Allow peer-to-peer subscriptions" option in the Subscription Options folder of the Publication Properties dialog box?
I've got:
Independent Distribution Agent True (not selectable)
Allow anonymous subscriptions False
Attachable subscription database False
Allow pull subscriptions False
Allow initialization from backup files True
Allow non-SQL Server Subscribers False
Allow data transformations False (not selectable)
Replicate schema changes True
Allow peer-to-peer subscriptions False (not selectable)
Allow immediate updating subscriptions True (not selectable)
Allow queued updating subscriptions True (not selectable)
Report conflicts centrally True
Conflict resolution policy Keep the Publisher
I've tried various combinations of options, but think these are correct. Also, the snapshot database is in a local (not network) directory; I got a warning on this, although in this special case the snapshot should be available to both instances.
Thanks...
Peer To Peer Replication Licensing
Oct 26, 2007
If only one node of p2p topology is used, and other nodes are kept only for redundancy/diseaster recovery (no reporting, no datachanges on other nodes etc), do I need to buy licence for all nodes or just for one node ? In other words, are all nodes of p2p considered as "production"/"must pay licence for" nodes ?
Thank you.
SQL 2005 SP1 And Peer To Peer Replication
May 4, 2006We are thinking about upgrading to SP1 on all of our database servers.
Our servers are running on WIN2K3 Enterprise Edition and we have
SQL2005 EE also on these servers. Each server is configured as a
distributor and subscriber in a peer to peer topology. The readme on
the services pack says to upgrade in a specific order but since all the
servers server as a publisher and subscribers how do we do it? Has
anyone upgraded in this scenario?
Thanks and have a wonderful day!
Ryan
Peer-to-peer Replication Questions
Dec 20, 2006Our current setup is as follows:
serverA - DB1, DB2
ServerB - DB3
ServerC - DB4, DB5
Question 1: In peer-to-peer, is this the right setup?
ServerA - DB1, DB2, DB3, DB4, DB5
ServerB - DB1, DB2, DB3, DB4, DB5
ServerC - DB1, DB2, DB3, DB4, DB5
Question 2: Are we backing up all DBs or just DB1?
Question 3: When serverA.DB1 goes down, does it affect the other DB1s?
Question 4: Can any of the DBs publish to any server say a reporting_serverG that is outside the peer-to-peer topology.
Question 5: Is it best to have .mdf, .ldf, tempdb, etc. on local drive or in the SAN?
Question 6: What is the recommended NLB hardware needed to handle peer-to-peer?
Peer To Peer Replication - Quiescing A System ( Quiesce A System )
Oct 23, 2007
MS documentation is short when it comes to explanation of Quiescing a system for peer to peer replication. It seems that for any change the whole p2p machine has to stop ? This would cripple our development/releases... Is it really the case ?
Can all p2p nodes be quiesced except one, the one when I would also make changes ?
i.e. N1 <> N2 <> N3 I would stop inserts/updates/deletes for N1 & N3 and keep inserting/updating/deleting for N2 as I'm making schema change on N2 (additional table, additional field etc). Would it work ?
Also, would I need to stop selects for N1 & N2 ?
Thank you.
Peer To Peer Replication
Feb 8, 2007Hello,
I have a sql server 2005 ent editon in one my server. Then I rent another dedicated server in another hosting company and want to do a peer-to-peer repliaction so that both can serve website visitor for load balancing.
http://msdn2.microsoft.com/en-us/library/ms152536.aspx#
I read the tutorials and start doing the replication by backuping the database and restore it to the new server. Since I do not want any downtime for my website so that I select the second options says the database has modified after the backup is created. When I click OK to create the Peer-to-Peer replication I received:
Building the Peer-To-Peer Topology...
- Creating publication on 'EV1SERVE-XQSEQ6.NTest' (Warning)
Messages
The publication or its Log Reader Agent already exists and will not be modified. (Configure Peer-To-Peer Topology Wizard)
- Creating publication on 'SERVER14.NTest' (Warning)
Messages
The publication or its Log Reader Agent already exists and will not be modified. (Configure Peer-To-Peer Topology Wizard)
- Creating subscription for 'SERVER14.NTest' on 'EV1SERVE-XQSEQ6.NTest' (Warning)
Messages
The subscription or its Distribution Agent already exists and will not be modified. (Configure Peer-To-Peer Topology Wizard)
- Creating subscription for 'AAA' on 'BBB' (Error)
Messages
SQL Server could not create a subscription for Subscriber 'AAA'. (Configure Peer-To-Peer Topology Wizard)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not locate backup header information for database 'BBB' in the specified backup device.
The subscription could not be found.
Changed database context to 'NTest'. (Microsoft SQL Server, Error: 18782)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=18782&LinkId=20476
Any idea?
Thanks,
Naki
Peer-to-peer Question
Mar 12, 2007We have a request to add an article to an existing peer-to-peer configuration. I'm not sure it is a good idea or very easy to get done. I've tried to do it on a couple of test databases and have received the subscriptions need to be initialized message. Not a real option since the dbs are fairly large (500GB). Has anybody done this and have a good resource I could look at? I've read book online and it isn't very clear. Thanks in advance.
View 5 Replies View RelatedPeer To Peer Replication
Aug 25, 2006Hi,
I set up a peer to peer replication on servers, but only one side seem to be working. I am getting Log Reader Agent job error.
Unable to start execution of step 2 (reason: Error authenticating proxy domainuser, system error: Logon failure: unknown user name or bad password.). The step failed.
I am using the same windows account on both servers, why is one side working and the other side not?
thanks
Transactional Repl, Diffe Table Names, Diff Columns
Mar 22, 2007Hello,
I've been able to startup a transactional replication between 2 database for some tables that only have different table names. Now there are still some tables that not only have different names but also different column names, can this be done in the wizzard ??
Thx
Configuring Peer-to_Peer Merge Replication
Apr 11, 2006We have a situation almost exactly like that in the MS documentationvis a vis peer-to-peer replication. We have three servers and threeuser groups, one each in Chicago, New York, and Bermuda. Because of ourbusiness practices, we are fairly confident that only one group will beupdating a given record at any one time but the users will be updatingtheir own local database servers and those updates must appear on allthree servers. Reading through the documentation, MS seems to recommendpeer-to-peer merge replication. Unfortunately, the documenation thengoes off and gives an example only of transaction replication withoutupdating subscriptions. Well, OK I sez, I'll just adpat what the docssay but select merge replication. Doesn't work that way. Using SQLstudio, I have never been able to get to a peer-to-peer topology optionusing anything but transaction replicas without updating subscriptions.Using T-SQL, it looks like I can brute force peer to peer replicationwith updating subscriptions but the sp_addmergedpublication functiondoes not have any options for peer-to-peer topology.So, what gives? How do I set up peer-to-peer merge replication? Anyhelp would be greatly appreciated!Thanks.Randy
View 1 Replies View RelatedError 627: Transactional Repl. SQL 7.0
Nov 11, 1999The problem case for transactional replication in MS SQL 7.0 is as follows:
Server A (intended publisher)
Server B (distributor, intended subscriber)
When configuring replication, the distributor (B) can be configured easily; the publisher (A) that uses B as its distributor will not allow for a publication to be created using either the Enterprise Manager wizards or TSQL commands. The error '627: Cannot use SAVE TRANSACTION within a distributed transaction' occurs when creating the publication.
Any suggestions; I need some help ASAP.
Thanks