How To Do Bidirectional Replication In SQL Server 2005?
Oct 4, 2007
Greetings...
Presently, I am doing one way replication in SQL Server 2005. Server-A is local server at local place and Server-B is remote server at different place. There is not a problem in one way replication.
Server-A is Distributor and Server-B is Subscriber in one way replication.
I want to setup the following configuration using bidirectional replication (two way replication) on SQL Server 2005
And I am not able to do it. What should I do for this?
Should I use Merge Replication for bidirectional Replication.
Server-B is live server for users which cannot be stop for a moment. Server-A is local server which is live too.
Now please let me know how to do Bidirectional Replication. So whatever data in Server-B (Which is live) should replicate to Server-A or Vice versa ...
If we add some column into Server-B's table of Database what could be the effect on Server-A...
I have a problem configuring Bidirectional replication in SQL Server 2005 SP2. I configured Publication and Subscription on two different SQL 2005 instances on different machines (Station1SQL2005 and Station2SQL2005 respectively). Databases are DBTest1 in Station1 and DBTest2 in Station2. I have two tables one in DBTest1 and the other in DBTest2.
Script for the above configuration:
This below configuration does not work if i configure Publication and Subscription on the same machines
For Station1: IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest1') DROP DATABASE dbtest1;
CREATE DATABASE dbtest1 go
--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set USE dbtest1 go
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest1') DROP TABLE two_way_dbtest1; GO
CREATE TABLE two_way_dbtest1 ( pkcol INTEGER PRIMARY KEY NOT NULL, intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION, charcol CHAR(100), timestampcol TIMESTAMP )
/*Allocate a predetermined range of values to the primary key column so that the values on the different servers are not in the same range. For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database, and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database. To do so, use the following code: */ -- Constraint to enforce a range of values between 1 and 1000 in database dbtest1 USE dbtest1 go
ALTER TABLE two_way_dbtest1 WITH NOCHECK ADD CONSTRAINT checkprimcol CHECK NOT FOR REPLICATION ( pkcol BETWEEN 1 AND 1000 ) go
--Enable your server as the distributor, and then create a distribution database --Ensure SQL Server Agent service is running before executing the below statement. USE master go sp_adddistributor @distributor = 'Station1SQL2005' go
--create a distribution database for the distributor USE master go sp_adddistributiondb @database='distribution' go
--Enable the computers running SQL Server that are participating in the replication as publishers USE master go
--Enable the identified databases for replication USE master go
exec sp_replicationdboption N'dbtest1', N'publish', true go
--Create the custom stored procedures in the dbtest1 database USE dbtest1 go
-- INSERT Stored Procedure
CREATE PROCEDURE sp_ins_two_way_dbtest1 @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @rowidcol uniqueidentifier AS INSERT INTO two_way_dbtest1 ( pkcol, intcol, charcol ) VALUES ( @pkcol, @intcol, @charcol ) go
--UPDATE Stored Procedure
CREATE PROCEDURE sp_upd_two_way_dbtest1 @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @rowidcol uniqueidentifier, @old_pkcol int as DECLARE @x int DECLARE @y int DECLARE @z char(100)
SELECT @x=pkcol, @y=intcol, @z=charcol FROM two_way_dbtest1 WHERE pkcol = @pkcol
DELETE two_way_dbtest1 WHERE pkcol=@pkcol
INSERT INTO two_way_dbtest1 ( pkcol, intcol, charcol ) VALUES ( CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END, CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END, CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END ) go
-- DELETE Stored Procedure
CREATE PROCEDURE sp_del_two_way_dbtest1 @old_pkcol int AS DELETE two_way_dbtest1 WHERE pkcol = @old_pkcol go
--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases --In the database dbtest1. USE dbtest1 go
-- Adding the transactional publication. EXEC sp_addpublication @publication = N'two_way_pub_dbtest1', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication for database dbtest1.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'true', @autogen_sync_procs = N'true', --To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions --to the publication will never expire and be removed by the Expired Subscription Cleanup Agent. @retention = 120 go
/*In this scenario, the dbtest1 database is the central subscriber. Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1 and in the dbtest1 database that subscribe to the publication at dbtest2 */ --Create all the subscriptions with the LOOPBACK_DETECTION option enabled --Adding the transactional subscription in dbtest1. USE dbtest1 go EXEC sp_addsubscription @publication = N'two_way_pub_dbtest1', @article = N'all', @subscriber = 'Station2SQL2005', @destination_db = N'dbtest2', @sync_type = N'none', @status = N'active', @update_mode = N'sync tran', @loopback_detection = 'true' go
For Station2: --Create database named test1 IF EXISTS(SELECT * FROM sys.databases WHERE name = 'dbtest2') DROP DATABASE dbtest2 go
CREATE DATABASE dbtest2 go
--Create table named two_way_dbtest1 that have an IDENTITY column with the NOT FOR REPLICATION option set USE dbtest2 go
IF EXISTS (SELECT * FROM sysobjects WHERE name LIKE 'two_way_dbtest2') DROP TABLE two_way_dbtest2; GO
CREATE TABLE two_way_dbtest2 ( pkcol INTEGER PRIMARY KEY NOT NULL, intcol INTEGER IDENTITY(1,1) NOT FOR REPLICATION, charcol CHAR(100), timestampcol TIMESTAMP )
/*Allocate a predetermined range of values to the primary key column so that the values on the different servers are not in the same range. For example, you can enforce 1-1000 as the key range for the two_way_dbtest1 table in the dbtest1 database, and then enforce 1001 -2000 as the key range for two_way_dbtest2 table in the dbtest2 database. To do so, use the following code: */ -- Constraint to enforce a range of values between 1 and 1000 in database dbtest1 USE dbtest2 go
ALTER TABLE two_way_dbtest2 WITH NOCHECK ADD CONSTRAINT checkprimcol CHECK NOT FOR REPLICATION ( pkcol BETWEEN 1 AND 1000 ) go
--Enable your server as the distributor, and then create a distribution database --Ensure SQL Server Agent service is running before executing the below statement. USE master go EXEC sp_adddistributor @distributor = 'Station2SQL2005' go
--create a distribution database for the distributor USE master go sp_adddistributiondb @database='distribution' go
--Enable the computers running SQL Server that are participating in the replication as publishers USE master go
--Enable the identified databases for replication USE master go
exec sp_replicationdboption N'dbtest2', N'publish', true go
--Create the custom stored procedures in the dbtest1 database USE dbtest2 go
-- INSERT Stored Procedure
CREATE PROCEDURE sp_ins_two_way_dbtest2 @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @rowidcol uniqueidentifier AS INSERT INTO two_way_dbtest2 ( pkcol, intcol, charcol ) VALUES ( @pkcol, @intcol, @charcol ) go
--UPDATE Stored Procedure
CREATE PROCEDURE sp_upd_two_way_dbtest2 @pkcol int, @intcol int, @charcol char(100), @timestampcol timestamp, @rowidcol uniqueidentifier, @old_pkcol int as DECLARE @x int DECLARE @y int DECLARE @z char(100)
SELECT @x=pkcol, @y=intcol, @z=charcol FROM two_way_dbtest2 WHERE pkcol = @pkcol
DELETE two_way_dbtest2 WHERE pkcol=@pkcol
INSERT INTO two_way_dbtest2 ( pkcol, intcol, charcol ) VALUES ( CASE ISNULL(@pkcol,0) WHEN 0 THEN @x ELSE @pkcol END, CASE ISNULL(@intcol,0) WHEN 0 THEN @y ELSE @intcol END, CASE ISNULL(@charcol,'N') WHEN 'N' THEN @z ELSE @charcol END ) go
-- DELETE Stored Procedure
CREATE PROCEDURE sp_del_two_way_dbtest2 @old_pkcol int AS DELETE two_way_dbtest2 WHERE pkcol = @old_pkcol go
--Create a transactional publication, and then add articles to the publication in both the dbtest1 and the dbtest2 databases --In the database dbtest1. USE dbtest2 go
-- Adding the transactional publication. EXEC sp_addpublication @publication = N'two_way_pub_dbtest2', @restricted = N'false', @sync_method = N'native', @repl_freq = N'continuous', @description = N'Transactional publication for database dbtest2.', @status = N'active', @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @independent_agent = N'false', @immediate_sync = N'false', @allow_sync_tran = N'true', @autogen_sync_procs = N'true', --To avoid expiry if there are 5 continuous holidays for a company. If 0, well-known subscriptions --to the publication will never expire and be removed by the Expired Subscription Cleanup Agent. @retention = 120 go
/*In this scenario, the dbtest1 database is the central subscriber. Create transactional subscriptions in the dbtest2 database that subscribe to the publication at dbtest1 and in the dbtest1 database that subscribe to the publication at dbtest2 */ --Create all the subscriptions with the LOOPBACK_DETECTION option enabled --Adding the transactional subscription in dbtest1. USE dbtest2 go EXEC sp_addsubscription @publication = N'two_way_pub_dbtest2', @article = N'all', @subscriber = 'Station1SQL2005', @destination_db = N'dbtest1', @sync_type = N'none', @status = N'active', @update_mode = N'sync tran', @loopback_detection = 'true' go
---************************************************************************************************* It would be grateful if somebody gives me a solution.
Hi, We have Two Database server at location A and at location B, Say 'Server A' and 'Server B'. Both database contain's same schema and data. A group of user's will make updation's to 'server A' and rest using 'Server B'. My requirement is that i want the both database to be identical, ie if an updation or insertion in server A is made then it should also affect 'serverB' and vice versa. * Can go for batch updation , because very 10min or lesser i want the datbase to be syncronized. * can go for single side replication as both database will be updating Thanks In Advance, KCube
is it possible to implement bidirectional replication with queued updating subscriptions in SQL Server 2000? I am currently testing bidirectional replication on two servers and it works well so far. My concern is how to I update the subscriber or publisher once both servers become disconnected? How to I resync? thank you for your help,
According to this article from last year:http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1251149,00.htmlThese are the main options:* Merge Replication* Bi-directional Transactional replication* Immediate Updating* Queued Updating* Peer to Peer* RDAAre there any new alternatives that have popped up over the last year? Are all of six above still good options based on needs?We currently have a three server topology using merge replication.ServerA (App1DB) <--> ServerB (App2 DB)ServerA (App1DB) <--> ServerB (App3 DB)ServerA (App1DB) <--> ServerCServerA supports 1 intranet application using 1 DBServerB supports 2 extranet applications using 2 DB's (1 per application)ServerC is our DW server that we have installed a Search DB which is used by all applicationsPrior to our "upgrade" to merge replication we were using 1-way Transactional Replication so our topology looked like:ServerA --> ServerB (App2 DB)ServerA --> ServerB (App3 DB)We also had linked servers between ServerB and ServerA as well as between ServerC and ServerA to update data on ServerA. We would simultaneously update/insert the tables on ServerB/C and create custom stored procedures to handle the data already processed from the subscribers.With our new implementation we are seeing more latency as well as locking since merge replication is not running off of transaction logs anymore.My main question is would we see an increase in performance and less locking as a result of a topology like this:Master <--> ServerA (App1 DB)Master <--> ServerB (App2 DB)Master <--> ServerB (App3 DB)Master <--> ServerCWhere Master is a server and DB supporting no applications (hence no OLTP). Would latency be the same/better/worse? Should we stick with our current implementation and just performance tune it?A secondary question I have is given the bidirectional replication options above did we choose the best one for us? These servers are all on the same network hosted by the same provider over Gigabit Ethernet (I assume). I think we have the polling interval set at 5 seconds and we are thinking of moving it to 10 seconds at most. Real-time latency is not critical to our business but it would be a "nice to have". For conflict resolution we are keeping it simple, whichever was inserted/updated last "wins". It looks like Bi-directional Transactional replication might be a better option for us. Would it give us the autonomy we are looking for? Any major "cons" to using Bi-directional Transactional replication over merge replication (beside scalability). Scalability may come into play a few years down the road but for now it is not a high priority. Also would the Master model described above using Bi-directional Transactional replication be a successful implementation?ETA - One thing merge replication gives us is autonomy between our application servers, particularly when ServerA needs to come down for upgrades, the applications on ServerB can still function without any dependencies like we had before with 1-way transactional replication with linked server calls.
Following setup s1<--- p1<-->p2 ---->s2 (bidirectional replication between publishers as each one have its own subscriber) What are the disadvantages of this solution if only one publisher gets written to at the time. How about schema changes (would I need to stop all activity on p1 & p2 similar to p2p replication) ? Would changes get republished to s1 & s2 ? Are identities the only problem when instead on p1 as main server I start using p2 ? Thank you.
I want to be able to setup the following configuration using bidirectional transactional replication on SQL 2005
instance A lives on machine 1 instance B lives on machine 2
Instance A publishes to a transactional subscription on Instance B Instance B does the reverse and publishes to a transactional subscription on Instance A
Instance A pushes to a distribution database on machine 2 Instance B pushes to a distribution database on machine 1
Problems Implementing Configuration
I can setup each instance as a distributor and create separate distribution databases using sp_adddistributor and sp_adddistributiondb
I can then enable each publisher to use the correct distribution database using sp_adddistpublisher
However, when I try and run sp_replicationdboption to setup a database for publication, I get the error:
Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 55 The Distributor has not been installed correctly. Could not enable database for publishing.
If I try and configure a publication with the wizard, it says that instance A must be enabled as a publisher before you can create a publication - and then presents the dialog to add instance A to be handled by the local distribution database - which I don't want.
It appears that I need to run sp_adddistributor on the publisher as well as the distributor for the appropriate instance, but if I do this I get the error:
Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109 The server 'instance A' is already defined as a Distributor.
It seems that you can't select a remote distributor if you already have a local distributor configured.
Is there a way round this limitation?
Note that configuring the environment with a single distribution database works fine
I create a distributed database for mobile application. I replicate a table that distribute on mobile device. I follow instruction how to create distributor, publication, replication, web synchronization, and subscriber database. I have done fine for synchronization between mobile database into desktop database (in this case SQL Server 2005 Standard Edition). But the problem is how can setup publication so it can bidirectional, not only from mobile database into desktop database, but also from desktop database into mobile database. So in the mobile database can have same data with desktop database even on mobile database lost some old data.
Its like data exchange between both engine. Desktop and mobile have same data. For filtering I can put filter on the desktop server for replicated table, so don't worry how I split the data.
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
I have a problem when i start sincronyzing with the emulator of MSVS2005 to SQL2005 in Windows Vista. I have the same program in the emulator, but sincronyzing with windows XP Pro and no problem...
"Failure to connect to SQLServer with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect"
I just want to display all the data of a Single table into a Data Grid, I know that we can drag and drop the table on to a form and datagrid is generated, but here I want to retrive those values through my code, how should i do that
I am getting following errors while running the program Error 1) Error No. 28037, MS SQL Server 2005 Evrywhere Edition Error: A request to send data to the computer running IIS has failed. For more information see HRESULT Error 2) Error No. 0, SQL Server 2005 Evrywhere Edition ADO.Net Data Provider Error: The specified table does not exist [ JobLists ].
Can anybody please tell me, where I went wrong ??? In this code anywhere else????
Note: While adding a Data Source of SQL Server 2005 Mobile Edition, I have added that .sdf file into my project, thats why I have written the Data Source as : .DbFile.sdf
@"Data Source = .DbDotNetCF.sdf";
The code is as follows:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Text; using System.Windows.Forms; using System.Data.SqlServerCe;
namespace DeviceApplication1 { public partial class Form1 : Form { string filename = @".DbDotNetCF.sdf";
private DataSet dsJobLists;
public Form1() { InitializeComponent(); }
private void DeleteDB() { if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } }
private void Sync() { SqlCeReplication repl = new SqlCeReplication();
if (DbDotNetCFDataSetUtil.DesignerUtil.IsRunTime()) { // TODO: Delete this line of code to remove the default AutoFill for 'dbDotNetCFDataSet.JobLists'. this.jobListsTableAdapter.Fill(this.dbDotNetCFDataSet.JobLists); } } } }
I have created a merge replication correctlly( I suppose, there were no errros) Please help
We have been looking for books which cover the DB synchronisation subject. We have started creating a plugin to our socket server but we quickly realised how much of a challenge it would be (We are creating it "from scratch"). The conflict problems, the order to tables (rows) be synchronised (Child - Parent) and how to delete rows were some of the problems we found. The main goal is to synchronise devices in the field (Compact framework) to the server using less bandwich as possible. We are trying to be "agnostic" about the DB in both Server and Client side.
We are pretty sure there are books out there which cover this subject, we just couldn't find them!
We know this will be a challenge but we also know it will be a great feature to add to the server.
I have two servers with sql server 2005, one is in the main office and the other in a store...
I would like that the items table from the main office send new data to the server at store, and the sales data from the store to be send to the main office, can anyone help me to do that?!
I have a setup where I need to replicate the database which is actually subscribing from another database. The current setup is all in SQL Server 2000. I need to now setup a Distrbutor on a SQL server 2005 and publish the database using this distributor to another server on SQL server 2000.
Has anybody done this before. If yes what will I need to check. Can you please let me know :-
1) SQL Server 2000 which SP should be installed to support this enviroment.
2) SQL Server 2005 which SP should be installed to support this environment.
We are implementing 2005 transaction replication on source database to target staging subscring database but we want to keep all transaction changes from source within staging subscribing tables. If source column gets updated we want to keep old record and new updated record in staging subscriber. Transaction replication synchronizes but does not keep history on subscriber. Do we update stored proc's anyone have examples of code or ideas??
I read that views can't be published with SQL Server 2005 replication. Is this planned for the future? If not what alternatives are there for this?
Simple collecting the data needed in a new table ain't a solution for us (memory consuming). And joining the data on the PPC ain't a good solution either (memory and time consuming). We only want to pull the data.
Hello All, I created all the role and logins as described in oracleadmin.sql file and were able to query Oracle tables. But when I try to crate publisher on Oracle server from sqL server I get the following error: ------------------------------------------------------------------------------------------------------------------------------- TITLE: Distributor Properties ------------------------------
An error occurred applying the changes to the Distributor.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server+Management+Studio&ProdVer=9.00.3186.00&EvtSrc=Microsoft.SqlServer.Management.UI.DistributorPropertiesErrorSR&EvtID=ErrorApplyingDistributor&LinkId=20476
SQL Server could not enable 'oracle_dev' as a Publisher. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Failed to execute the HREPL.INITPUBLISHER request to Oracle Publisher 'ORACLE_DEV'. Verify that the Oracle package code exists on the Publisher, and that the replication administrative user account has sufficient permissions. Changed database context to 'master'. OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "One or more errors occurred during processing of command.". OLE DB provider "MSDAORA" for linked server "ORACLE_DEV" returned message "ORA-06550: line 1, column 8: PLS-00201: identifier 'HREPL.INITPUBLISHER' must be declared ORA-06550: line 1, column 8: PL/SQL: Statement ignored ". Error: 7215, Sev: 17, State: 1, Msg: Could not execute statement on remote server 'ORACLE_DEV'. (Microsoft SQL Server, Error: 21651)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3186&EvtSrc=MSSQLServer&EvtID=21651&LinkId=20476
-------------------------------------------------------------------------------------------------------------------------------------------------------- I searched the web and could not find any related info, where is this package can I run it manually? Any help appreciated.
We are trying to set up the Sql Server CE Server Tools on our Web Server and most of the MSDN support items assume that Sql 2005 is installed on the same PC. However, our Sql 2005 database is stored on a dedicated server off site. When we run the Sql CE ST installation we are tols we cannot sync with Sql 2005 until we install the Replication Components. It seems we cannot download them and they can only be located on the Sql 2005 install CD. Is there another way around this as the Sql SP3a and SP4 dont solve the problem and without having to arrange a courier for the CD we cannot seem to move forward.
im trying to set up a replication from a local server to a remote server.
when starting up and selecting the Distribution / publisher, i get the following errors ---> Details as followed...
TITLE: New Publication Wizard ------------------------------
SQL Server is unable to connect to server 'SERVERX'.
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantConnect&LinkId=20476
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVERY'. (Replication.Utilities)
------------------------------ BUTTONS:
OK ------------------------------ and...
===================================
SQL Server is unable to connect to server 'SERVERX'. (New Publication Wizard)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.PubWizardErrorSR&EvtID=CantConnect&LinkId=20476
===================================
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'SERVERY'. (Replication.Utilities)
------------------------------ Program Location:
at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn) at Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.Open() at Microsoft.SqlServer.Management.UI.CreatePublicationWizard.PrepareToShow()
I have a problem that about the Replication of the SQL SERVER 2005. If there are a hundred data in the delivery of replication ,after they transmited fifty data ,somebody want to read the data in the transmited fifty data that has delivery successfully,but the replication delivery hasn't finished,and could he can read the data successfully? In the delivery process of the replication,could the table in the subscription will be lock?And can we oprate the table in the subscription in the delivery process of the replication ?
I've been trying to get my merge replication to work with a sql ce 2.0 on sql server 2005, but it keeps generating shapshot scripts my pocket pc can't execute.
Example of my table.sch file in my snapshot folder: drop Table [dbo].[Application] go
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Application]( [AppID] [nvarchar](20) NOT NULL, [AddOnInfo] [nvarchar](50) NULL, [MaxClients] [int] NULL, [AppName] [nvarchar](255) NULL, [NbrDaysHistory] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysHistory] DEFAULT (10), [NbrDaysFuture] [int] NOT NULL CONSTRAINT [DF_Application_NbrDaysFuture] DEFAULT (10), [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [DF__Applicati__rowgu__4C364F0E] DEFAULT (newid()) )
GO SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY CLUSTERED ( [AppID] ) GO
I've selected that I was setting up a merge replication that needs to be compatible with sql ce. But it doesn't seem to do this...
I've tried to set the compatibility level to 80SP3 manually but it remains 80RTM... I've tried everything.....
Does anyone have any ideas what could be causing this?
If I remove these lines from the sch file everything works jsut fine:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[Application] ADD CONSTRAINT [Application_PK] PRIMARY KEY CLUSTERED ( [AppID] ) GO
Even if I make a publication for sql mobile 2005 it generates the same script.... It's almost as if SQL 2005 is ignoring my settings...
I am experiencing a problem with deployment of a VB .Net application which carries out merge replication, and would greatly appreciate any assistance. I am currently struggling to find stuff about my problem on the net, which probably means either (a) I'm missing something really simple or (b) I shouldn't be trying to do this in the first place. :)
The story so far: 1. I added the SQLMergXLib.dll COM to my Visual Studio project, allowing me access to SQL Server merge replication functionality from my code, and wrote a procedure for synchronising a given pull subscription. This worked beautifully and with minimal fuss. 2. I tried to deploy the project to a different machine. I received an error to the effect of, "Couldn't create object, it's a COM object and it's not registered correctly." I have encountered this problem a few times before when trying to use Interop, so I called myself a few names, went back to the deployment project, and specified that the object should be registered. Still no joy. I tried this a couple of different ways round but then... 3. ...found an article on MSDN suggesting that I should consider using the managed Microsoft.SqlServer.Replication, Microsoft.SqlServer.Management, Microsoft.SqlServer.RMO interfaces instead. I reasoned that this would eliminate the registry issue altogether as everything would become native to the .Net framework, and this seemed infinitely preferable anyway, so I promptly substituted the SQLMergX DLL for these, rehashing my code to match. Again, this built and ran OK on my development machine. 4. I tried to deploy the project to a different machine. I received the following error: "Could not load file or assembly 'Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. This application has failed to start because the application configuration is incorrect."
Now it's worth pointing out that I think I know what the problem might be here: No SQL Server of any kind is installed on the target machine. However, SQL Server is not required to deploy standard database solutions in .Net, and I am hoping this is also true with the Microsoft.SqlServer... namespaces, as it is a requirement that the subscription database can be located anywhere (not necessarily the target machine), and therefore that SQL Server need not be present on the target machine. However, if this is simply impossible, please let me know so that I can look at other alternatives.
Other than that, I don't mind what method I use to get the synchronisation working as long as it works (I've been at this a good few days now...), so if anyone has done this before and can offer any assistance (any registering/files I need to include etc, I am hoping it is something that simple), it would be appreciated.
I'm getting this, after upgrading from 2000 to 2005.Replication-Replication Distribution Subsystem: agent (null) failed.The subscription to publication '(null)' has expired or does notexist.The only suggestions I've seen are to dump all subscriptions. Sincewe have several dozen publications to several servers, is there adecent way to script it all out, if that's the only suggestion?Thanks in advance.
I am trying to test simple replication (only tables) of a database that resides on a SQL Server 2005 instance to a SQL Server 2000 instance. The Publisher and Distributer are set up on the SQL Server 2005 instance for Transactional replication. The subscriber is set up on in the 2000 instance. Replication Monitor shows the following error after applying a few scripts: "Category: SQLSERVER Source SQLSERVER2000 Number: 170 Message: Line 6: Incorrect syntax near 'max'."
Here SQLSERVER2000 is the name of my 2000 instance, as should be obvious.
Beyond this point, replication fails. Any pointers as to where the problem could lie? Is this a known backward compatibility issue? I've checked all tables in the database and none contain any datatype that is new to 2005 (the database was actually created in and for SQL Server 2000.
Replication from 2000 to 2005 works fine, but the other way round is failing as described above. Any clues?
