SQL Server 2000/7.0
Problem creating P2P
Last Post 06 Oct 2009 05:53 AM by trans53. 4 Replies.
05 Oct 2009 11:01 PM
i am trying to create P2P between 2 nodesL fisrt node is sql 2005( node1) and second node is sql 2008(node2).
This is what i did so far:
1) on node1 i created transactional replication and configured for peer-to-peer.
Also created distributor database.
2) on node1 created backup of the database and restored on node2.
3) connected to sql 2005 instance using ssms 2008 and trying to configure peers using
"configure peer-to-peer topology" but when i select second option where you specify the backup use and click on browse and choose the backup file i got the error below from step2 when the job is running to create subscription.
I am not sure why i am getting this error.
Can someone please point to the right direction? i am stuck at this point
i did also run this command in ssms to verify the backup and don't see any problems:
FROM DISK = 'S:\BACKUPS\P2P_TEST.bak'
SQL Server could not create a subscription for Subscriber 'SQL02\SQL02'. (Configure Peer-To-Peer Topology Wizard)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand)
at Microsoft.SqlServer.Replication.ReplicationObject.ExecCommand(String commandIn)
at Microsoft.SqlServer.Management.UI.CreatePeerToPeerWizard.CreatePushSubscription(P2PProgressItem actionItem, Boolean& subscriptionAlreadyExists)
at Microsoft.SqlServer.Management.UI.CreatePeerToPeerWizard.CreateP2PSubscription(P2PProgressItem actionItem, Int32 currentProgressRow)
Could not locate backup header information for database '[SQLPREP01].[P2P_TEST]' in the specified backup device. Specify a backup device that contains a backup of the Publisher database.
The subscription could not be found.
Changed database context to 'P2P_TEST'. (.Net SqlClient Data Provider)
06 Oct 2009 05:53 AM
Both sql 2005 and 2008 are enterprise, sql 2005 is sp3 and sql2008 has sp1 installed. i am trying to create that both nodes has it's own distributor.
06 Oct 2009 08:25 AM
The servers are geographically separated. sql 2005 in one data center and sql 2008 in another.
Can you please tell me what are my options? And what is the best way to accomplish this?
We are trying to build active/active and i have a deadline for this.
isn't not having a distributor on each node for peer-to-peer is a single point of failure?
i am just trying to understand how this thing is working since it's my first setup.
06 Oct 2009 09:05 PM
Thank you for reply's.
So basically there is no way to create to separate nodes one with 2005 and one with 2008 and each has each own local distributor? This is what i was thinking but i was told it is possible.
I think the way you described in 2005/2008 topology i can only have remote distributor and make that sql 2008. i am not sure if it's possible to have distributor as 2008 on sql 2005 instance.
Is this correct?
07 Oct 2009 07:16 PM
Just want to share with you that i did created P2P today using sql2005 and sql2008 on different nodes and each has it's own local distributor installed. I tested and it works. Geez, it took me like 2 days for this with all kind of errors.
SQL Server 2000/7.0
Acceptable Use Policy