I have a product that sits on a main server and wish to implement
functionality to allow salesmen to come along, pick up a snapshot of the
database, go away and maybe modify/add to it and then come back and
"synchronise" their data. I'm reading up on Merge Replication for this
purpose. But anyway, I created a publisher on my server and it went away
and generated a "rowguid" column on all of my tables (my tables all have an
Identity column key field). Now of course my "Inserts" no longer work, as
they expect a GUID. I would have expected SQL Server to automatically
generate a guid for new inserts (in a similar way to it's TIMESTAMP), but it
appears it doesn't, despite the fact I have "(newid())" as the default for
the column. It always inserts the same value:
{00000000-0000-0000-0000-000000000000}.
So, back to basics, now I have a guid field for each record, how do I manage
inserts?
On the national Server: SQL 2005 Enterprise On the mobile clients: SQL 2005 Workgroup.
I was asked to find a solution to such scenario
2 mobile subscriber S1 and S2 to the same simple merge publication P1 on server N
Day 1,
S1 and S2 both synch up with N and both go off to do fieldwork Day3,
S1 and S2 both synch up with N.
S1 goes back to work
S2 shutdown the laptop and goes on 2-week vacation. 2 Weeks late
S1 Synch up wit the server and goes off to do fieldwork.
S2 meets S1 in the field. Their workfield is in the North Pole.
S2 has the laptop with data 2-weeks old but no longer can have access to the master publisher N to synch the replica and get latest changes.
S2 will have to sync with S1 since S1 database is fresh. The challenge is to have S2 and S1 replica identical
The Questions:
Is it possible for S2 to sync with S1?
if yes, How to go about it€¦ we need S1 and S2 to have identical replica on their machines?
Now that S1 and S2 are have identical databases and are both doing their fieldwork in the northpole. Can they both sync back with the national publisher N when they have access? Keep in mind that S2 got its data updated from the replica on S1?
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).
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.
Since Windows Integrated Authentication does not work over proxy for Replication, could I still use SSL or SQL authentication over proxy? Thanks for any advice.
I know that adding a column using ALTER TABLE to add a column automatically allows SQLSERVER 2005 to replicate the schema changes to the subscribers, however, I would like to add a new column to an existing article that is being used for merge replication, however, I don't want this column to be replicated. Re-initialising the subscriptions is not a option. Help would be appreciated.
So when a mobile device pulls down information, say a typical order entry scenario, then continues to edit/add orders and merges those changes back to the server, when/how are those records ever purged from the device once they are no longer needed?
I am working on bringing our disaster recovery site to be a live site. Currently we replicate to one of out servers (server B) with merge replication (from server A). Server A also does one way transactional replication form some table to several other servers including servers at the DR site.
This setup is not going to be fast enough for what we need so I am wondering if a table is receiving merge replication will the merge updates also replicate down the transaction path??
Example... Server B update a row and merges to Server A. With this update them replicate (via transactional) to Server C??
I am building a system that requires a small database to be replicated.
The DB is made up of a few tables that do not change much, primarily by user-input or triggers on those tables, and one constantly growing table for incoming messages with rows constantly being added to it.
We require having two systems (System=Hardware&OS/SQLDB/Application) running in parallel where one could act as a failover to the primary system. Both systems would be run in the same site and have a direct network link.
My goal is to have both systems maintain identical databases and to be able to "re-synchronize their data after a failure in some recovery state.
I am looking at:
1. SQL Server Replication Methods (Publishers/Subscribers) A possible scenario i need to support: ServerA: primary server acts as publisher ServerB: secondary failover server acts as subscriber ServerA: Fails! ServerB: Assumes role of primary server (i.e Publisher) ServerA: recovers later, becomes subscriber to ServerB(secondary)
Q: Can Roles Be Switched or A server be a subscriber and a publisher at the same time?
2. Use ServiceBroker Queues and Have Each server SEND a message to the other server whenever it gets incoming data?
3. Use Transact-SQL in ServerA to write data to ServerA and ServerB and vice versa.
4. Not worth replication, just have the application write data to both servers in two seperate requests.
Hi everbody, I setup the Merge Replication , it is working perfectly. But i have one problem now it is updating both ways. I nedd one way. Any body tell me which parameter i have to change.
My production box is running on NT4.0,SP6, SQL Server7.0,SP2. We implemented Merge replication. Working fine last 7 months. Last weekend i disabled replication, Successfully removed Distributor and Publishor. After that try add new fileds but won't allowed me. It's give the error message. I Also found Some Conflict_tables found almost 20 tables. All system Tables. Can delete these these tables, if i delete any problem my database. I added filelds many times but this time i got errors.
I have just installed replication on our production server to Merge Replicate with a Laptop server that will travel from time to time. I have now noticed that we cannot add or change any fields or attributes on the tables which are being replicated (which are all tables in the DB). This is a problem because we are changing and adding columns all of the time. Is there a way around this issue like shutting down the replication service or something? I have been unsuccessful in finding a way around this other than removing replication while we make changes.
I have implemented a Merger replication on our development server and I get a fillowing error when I try to update one of the table in publisher. "Transaction cannot start while in firehose mode"
Hi, I read some where that replication has two types conflict resolution, 1. row based and 2. Column based... If I am right... Can any one point me how to find out this option and how to set it up....
I have a merge replication going between 4 servers. The problem is when ever I do some BCP transfer to one of the tables in one of the servers. It puts the data in that table. But that Data does not get replicated to any other server like it should.
Please Advice on what to do. Is there any option I am forgetting to set or something.
I have successfully tried merge replication on single server with 2 databases. now i want to do the same with different servers, when i create pull subcription on server 2 which user account should I use? it is giving log in failure i tried using windows admin account and also the 'sa' account.
I have posted this earlier and I am re-posting it simplifying what I had said.
The scenario is:
I have two sql server database instances with the same database schema and all. However, both of them have different data. I have not set them for replication at all. Now, I want to do merge replication between them such that the data between them could be syncronized.
When I do pull merge subscription I have two choices - 1.Bringing schema and data to subscriber from publisher
2.Not bringing the schema and data from publisher to subscriber.
Obviously, I chose the second choice. But upon syncronizing I dont see any data from publisher coming to subscriber and vice-versa. If I add new data to publisher and do syncronization, I can see ONLY the new data created after the replication setup in subscriber. If I add new data to subscriber and do syncronization then the new data is removed from subscriber and not propagated to publisher.
I have just set up Merge replication, I have two servers, server A and server B, the merge replication worked successfully but I don't quite sure which databases should or should not replicated? If not, what other methods should I use?
I would really appreciated any comments or advice out there!
I had set up merge replication. I got these error messages where replications starts "Column names in each table must be unique. Column name 'PubID' in table 'bonflict_DBName_PHP_Data_Publications' is specified more than once "
PHP_Data_Publications table defind as:
CREATE TABLE [dbo].[PHP_Data_Publications] ( [PHP_Pub_ID] [uniqueidentifier] NOT NULL , [PHP_Data_ID] [uniqueidentifier] NOT NULL , [PubID] [uniqueidentifier] NOT NULL , [UserID] [uniqueidentifier] NOT NULL , [Username] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Publication] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Pub_Year] [datetime] NULL , [Pub_Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[PHP_Data_Publications] WITH NOCHECK ADD CONSTRAINT [DF__PHP_Data___rowgu__01E91FA0] DEFAULT (newid()) FOR [rowguid] GO
CREATE UNIQUE INDEX [index_1466488303] ON [dbo].[PHP_Data_Publications]([rowguid]) ON [PRIMARY] GO
Is it possible to modify or add new fields in sql 2000 when the instance became publisher ? I tried it several times but it failed. I access microsoft website but i did not help.
SQL Server BOL says merge replication can be done in only one direction. But my understanding is Merge replication happens in both ways between publisher and subscriber.
How can i allow data movement from only wince sql ce subscribers and not from publisher running sql 2000 and win 2k.
Please reply immediately bcoz i require a solution which has to be implemented very soon.
hi, in merge replication,i make a request subscriber with a priority 25.00,when i update the same date,it always choose the update from the publishing server,why????how does it work??? thx inadvance
I am using merge replication at remote connected via ISDN Dialup line. I got following error and replication fail.
publisher - PRSTGINDSQLIND agent - PRSTGINDSQLIND-pml-pml-192.168.100.50SQLDWS-4 publication - pml subsctription - 192.168.100.50SQLDWS:pml error - The process could not deliver the snapshot to the Subscriber. Agent Merge replication provider -2147201001 Agent 192.168.100.50sqldws 20037 ODBC 192.168.100.50sqldws
Agent - PRSTGINDSQLIND-pmst-pmst-192.168.100.50SQLDWS-3 error - The subscription to publication 'pmst' is invalid. last command {call sp_MSgetreplicainfo(?,?,?,?,?,?,?)}
I am in a process of learning Replication in MSDE, especially Merge Replication
Server runs on MS-XP Professional -------------------------------------- I have a sample Access project 'ReplTest' which has only table with only 2 columns. DatabaseName:ReplTestDB Table Name :TestTable MSDE Instance Name:SVRMYINSTANCE
Now I would like to know how I can configure this database for merge replication using SQL-DMO
Laptop runs on MS-XP Professional -------------------------------------- I have another access project which is running on computer 2 and connected to the ReplicaTest database.
MSDE Instance Name:LPTMYINSTANCE
My task is, when I am disconnected from server I would like to have a local copy of the database to work with and then, when reconnected, need synchronization with the server database and continue working from server database.
How to write this replication process from scratch using SQL-DMO objects in both Server computer and Laptop computer.
I dont have enterprise manager in both computers since they use only MSDE
Hi, I have a problem when doing merge replication. I need to have a identity column on both the publisher and subscriber, eg id. When the publisher is down, my app will now reference to the subscriber and insert into the subscriber. However when the publisher is up, the app will reference to publisher and start to insert into the publisher. This will cause conflict in the id. Also, i need the id to be in running order, therefore i cant use range for publisher and subscriber. Anyone have idea how to solve this problem?
I am having a problem getting my merge replication to work out. I am wanting to make it where the merge happens on demand. I have tried to run replmerg through cmd prompt and I get the error 'The subscription to publication [namehere] has expired or does not exist.' I have had no problems running the merge agent within Enterprise Manager. I have thought of running windows sync manager but I do not want my people to have to type a password in everytime they need to sync. I am running the whole replication process through FTP. I am in desperate need of help. Anything would be great!
I have merge replication setup between 2 servers and the subscriber had been offline for about 24 hours due to a network outage. What would be the best and easiest way to resynch the data. Would my best bet be a reinitialization or should I start a new snapshop and reapply. Also can I start a snapshot while users are using the publishing database?
