Merge And Transactional From The Same Server

Sep 27, 2006

I need to come up with a solution to my replication issue. The solution that I come up with involves using one publisher to push transactional and merge replication. The database that is published is the same.

Would this cause any issues?? Can this be done?? I would think that if a table is needed for both, I would have to create a separate publication for each type right??

Is there any documentation anywhere to help prove if this is possible??

View 6 Replies


ADVERTISEMENT

Sql Server Merge Replication Vs. Sql Server Transactional With Updatable Subscribtion

Jun 12, 2006

Hi,

Transactional replication allows updatable subscriptions where changes at the subscriber are replicated up to the publisher, this can happen via Immediate Updating subscriptions, Queue subscriptions and P2P (new in SQL 2005), all forms of Transactional replication.

Any compared document between merge replication and Transactional with updatable subscribtion ?

Thanks,

Tarek Ghazali

SQL Server MVP

View 4 Replies View Related

Merge Vs. Transactional Replication

Jul 13, 2001

Hi everybody,

I'm still pretty new when it comes to SQL server and I'd like to get some opinions about replication. I need to set up replication between 10 sites. The database is about 2 GB and at each size about 10 users are entering data. The databases need to be synchronized at least 3 times a day. Now our application developer told us to use transactional replication while I'm thinking of using merge replication.

Can anyone give me some more advice about the pro and cons of either replication type.
TIA

Charlie

View 1 Replies View Related

Replication - Merge Or Transactional??

Jun 26, 2000

We have 3 sites & would like to do replication btw the 3 sites. 1 publisher & 2 subscribers. All 3 sites should be able to do updates. Subscribers should be able to operate even if publisher is down. Any advice will be appreciated.

View 1 Replies View Related

Replication (transactional,merge)

Nov 10, 2001

Hi
This is my problem
1. I have been requested to implement either merge or transactional
replication at my site.The replication will be over a wan
2. The problem i have encountered is that the tables have been denormalized
to such an extent that they do not have primary keys ,this is because all
the logic has been programmed into the application.I do not want to
interfere with the table schema as this will create major problems
in the application
transactional replication requires that tables participating in this type
of replication must have primary keys .IS THERE A WORK AROUND?
3. The second issue is that if i try to implement merge replication i
am sitting with a problem that merge replication requires that all
timestamp fields be removed to participate in merge replication.This
application also has all the logic built into it.Almost all tables have
the timestamp datatype in the rowstamp field. IS
THERE A WORK AROUND FOR THIS PROBLEM

THanks

View 1 Replies View Related

Bidirectional Replication - Use Merge Or Transactional?

Feb 6, 2003

Hello:

I am planning to set-up a bidirectional replication where both servers are remore - i.e. they do not share a common domain.

Would it be better to replicate using Transactional Replication or Merge Replication?

How stable is bidirectional replication? I could not find much material on this. :(

Thanks,

Bianca

View 2 Replies View Related

Errors With Combined Use Of Transactional And Merge Replication - SQL2005

Oct 13, 2006

I am investigating the feasibility of a configuration with 3 databases on SQL2005

DB_A is an OLTP database and serves up transactional publication pub_txn - with updateable subscriptions

DB_B is a subscriber database which subscribes to pub_txn

DB_B is also a publisher which serves up merge publication pub_merge

DB_C is a subscriber database which pulls pub_merge

===============================

Updates on DB_A are successfully replicated to DB_B

Howvever, when DB_C pulls updates, it doesn't find the update sent to DB_B

===============================

Updates on DB_B are successfully replicated to both DB_A and DB_C

===============================

Updates on DB_C initially failed with the error

Msg 916, Level 14, State 1, Procedure trg_MSsync_upd_course_type, Line 0
The server principal "repllinkproxy" is not able to access the database
"DB_C" under the current security context.

I then changed the login repllinkproxy to be a db_owner in DB_C

I now get the error

Msg 208, Level 16, State 1, Procedure sp_check_sync_trigger, Line 23
Invalid object name 'dbo.MSreplication_objects'.

=================================


I have three questions as a result
1) Is there anything fundamentally wrong with what I am trying to achieve?

2) Why is update on DB_A not reaching DB_C

3) Why can't I update DB_C?

Any suggestions gratefully received

aero1





View 10 Replies View Related

Merge Replication Set Off Transactional Replication

Oct 9, 2007

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??

thanks...

View 5 Replies View Related

Transactional Replication From SQL Server 2005 To SQL Server 2000

Nov 8, 2006

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?

View 3 Replies View Related

SQL Server - Transactional Replication Questions

Sep 5, 2005

Our database has grown to the point where our current server is struggeling with the query load. One option is to get a 4 processor machine with 16GB of RAM, but I'm also looking at transactional replication as a solution. Currently we run dual Xeon with 4GB of ram (using the /3GB switch in the OS) We have SQL 2000 Enterprise.
The idea is to setup a secondary server with transactional replication pushed from the main server, so that some SELECT-only queries can be executed on the secondary server - thus taking load of the main one. We should be able to add PKs to the small number of tables that currently don't have them, and we should be able to run all updates / inserts on the main server.I'll setup a push-subscription for the entire DB (maybe excluding some log tables) and then for ceratain stored procedures I'll direct our applications to use the backup instead of the main server.
So: Is this a good idea? Is it easy to backup the server using transactional replication? How much extra overhead will this mean for the main server?

View 1 Replies View Related

Sql Server 2005 -Not Allow The Transactional Replication

Aug 29, 2006

we have the standard licensed copy for sql server 2005

while configure transaction replication not allow to move

message is required licesence for replication again i have to get license for

replication

please advice

View 2 Replies View Related

SQL Transactional Replication Distribution Server Struggling

Jun 5, 2006

We are attempting to rollout a name and address system to 10,000 users who will use an application connected to an MSDE database.

We are using transactional replication to distribute data updates to them. Clients are connecting via the On-Idle feature of Synchronization Manager to grab transactions.

Server spec:
Network card: 1GB
Processors: 2* Xeon 3.2Ghz
Server spec: DL380 2Gb memory
Concurrent connections set to: 600
Disc: RAID 10 with 6400 controller

We are not using hyper-threading.

So far we have rolled the system out to 3500 subscribers, 500 per day.

Each day a subsciber will receive at least 400 transactions and 5000 commands.

Latency is 6 seconds, delivery rate 180 commands per second at less busy times.
Latency is 14 seconds, delivery rate 127 commands a second at busy times.

I have seen it get as slow as 0.04 commands a seconds at busy times.

The server becomes incredibly slow when there are more than 50 concurrent connections.

We are seeing 100 CPU for most of the day as clients connect to the distributor at various times. Lunchtime is particularly busy when people go to lunch, leaving their machines idle. We see lots of "time-outs" and "unable to connect to distributor" messages on the replication monitor during peak times.

What can we do to improve the performance of the distribution server?

Are we being over-ambitious by selection SQL Replication for this scenario?

Thank you for any help!

Best wishes

Julian

View 1 Replies View Related

SQL Server 2008 :: Schema Changes With Transactional Replication

Jun 15, 2015

How do we know our schema changes will propagate to subscriber without breaking the replication? Is there any t-sql command to find out the option true or false?

View 2 Replies View Related

SQL Server 2014 :: Transactional Log File Restore

Sep 12, 2015

I have a database which gets refreshed on the daily by restoring 1 transactional file log, at the moment it is done manually but I'm trying to create an automation job which would get the transactional life file and restoring it.

View 5 Replies View Related

Oracle To SQL Server Transactional Replication Error

Jun 6, 2007

Hi,



I'd appreciate any help i can get here. I'm working with an MS SQL Server 2005 SP2 db on a Windows 2003 OS. I set up replication from an Oracle 10g instance a few months ago, and everything was working until i installed SP2 for SQL Server.



I get the following error:

2007-06-06 16:06:22.488 Status: 4096, code: 20024, text: 'Initializing'.

2007-06-06 16:06:22.488 The agent is running. Use Replication Monitor to view the details of this agent session.

2007-06-06 16:06:23.082 Status: 0, code: 1008, text: 'ORA-01008: not all variables bound
'.

2007-06-06 16:06:23.082 ORA-01008: not all variables bound

2007-06-06 16:06:23.082 Status: 0, code: 22037, text: 'Heterogeneous Logreader encountered an error in call to LoadReplCmds when processing state 'PROCESSSYNCDONE'.'.



I can get snapshot replication to work just fine, but transactional replication throws up the error above.



Anyone with any ideas on how i can proceed?



Thanks in advance!



Regards,

Rama.

View 7 Replies View Related

Transactional Replicating Hanging SQL Server 2005

Dec 22, 2005

Hi,

It appears that every 5 or so days, my transactional replication is hanging.  I see that I have a couple thousand undistributed commands and this number keeps growing.  It forces me to generate a snapshot in order to sync up.  When generating the snapshot, I will get frequent messages like "waiting for a response from server...".  So I will keep stopping and starting syncronization.  Eventually the snapshot will go out, with a delay after generation. 

Has anyone experienced this? 

Your help is much appreciated.

Thanks,

Nick

View 6 Replies View Related

SQL Server 2008 :: Transactional Replication (Snapshot Agent)

Mar 19, 2015

Is there a way to force the snapshot to use a format file when bcp'ng the data to the subscriber?

The publisher and subscriber schemas are slightly different. Mainly added columns to the subscriber.

We have a post snapshot script that updates these additional columns on the subscriber.

I'd like to force the snapshot to use a format file, so it doesn't error when initially loading the data to the subscriber.

Is this possible?

View 1 Replies View Related

SQL Server Admin 2014 :: Transactional Replication With Different Versions

May 17, 2015

We have installed 2014 sql server. We have currently 2008r2. We have to run the real time report. So we need to set up transactional replication b/n those two servers. We need to use 2008r2 as publisher and 2014 as subscriber.

Is it ok to have subscriber higher version than the publisher?

View 6 Replies View Related

SQL Server Admin 2014 :: Transactional Replication And CDC On Subscriber

Jun 15, 2015

Any experience of success or failure setting up CDC on the subscriber end of transactional replication?

Also for a bonus answer, why are explicit index operations not permitted (I'm assuming this is even on the publisher?) From BOL:

• Explicitly adding, dropping, or altering indexes is not supported. Indexes created implicitly for constraints (such as a primary key constraint) are supported.

View 0 Replies View Related

SQL Server Admin 2014 :: Setting Up Transactional Replication

Sep 10, 2015

I have created a publication on SQL 2014 SP1/CU1 it is going to a distribution server also a SQL 2014 database. The subscriber is also SQL 2014 SP1/CU1. When I set it up there are no errors. I fire up Replication MGR and it looks good after the snapshot and distribution agent have been started. Again, no errors. When I do a tracer token the information gets to the distributer in 1 - 4 seconds but it never gets to the subscriber, the tracer token monitoring screen only shows pending for distributor to subscriber.

I can upload the scripts used to create the publication and/or the subscription but am a little hesitant because of server names in the scripts.

View 2 Replies View Related

SQL Server 2014 :: Restore The Latest Transactional Log File Job?

Sep 23, 2015

I'm trying find a way of crating an sql server job which would restore only transactional log file just once a day. The trouble I'm having is trying to get only the latest transactional log file it it possible to achieve through e.g powershell etc as I can't find a way through sql query?

View 9 Replies View Related

Can Not Generate Snapshot (SQl Server 2005 Transactional Repl )

Sep 15, 2005

Hi,

View 6 Replies View Related

SQL Server Admin 2014 :: Transactional Log Backup With Frequency Of 1 Month

Jan 6, 2015

I am planning to take one full backup and Transactional Log backups for every month ..as i will be making the changes in database only once in a month .

And I am aware of that in case of disaster i need to restore database with all the Transactional Log backups . My Plan is to have Transactional log backups for 5 Years and after 5 years i would be taking a full backup .

So should I need to take any other precautions or concerns with this approach.?

View 9 Replies View Related

SQL Server 2008 :: Identify Which Indices Are Replicated - Transactional Replication

Feb 20, 2015

Is there a script to find which non-clustered indices are replicated? I know i can do this easily through GUI , having a script will make my life much easier ....

View 0 Replies View Related

SQL Server 2008 :: Transactional Replication And Subscriber Log Growth Errors

Jun 14, 2015

I am getting the error The transaction log for database 'ReplicationDB' is full due to 'LOG_BACKUP'.log_reuse_wait_desc from sys.databases is showing logbackup

The database is subscribed database. We configured transactional replication. But the transactional replication is getting errors and failed. Is there relation b/n this replication failures and log growth in subscriber db?

View 3 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot In Multiple Files

Sep 15, 2015

We have a large database with a small number of large tables in it (and a larger number of SMALLER tables), and it is a publisher for a transactional replication scenario. When I create a snapshot to initialize a new subscription, I notice with the larger tables that sometimes it generates multiple files in the snapshot folder, usually in multiples of 16, and numbers them like this:

MyTable_3#1.bcp
MyTable_3#2.bcp
...
MyTable_3#16.bcp

With other tables, I'll get just one LARGE snapshot file, named:

MyOtherTable_4.bcp

In the latter case, the file can be very large (most recent is 38GB).

In both cases, the subscription will eventually be initialized, but the smaller files will generate separate log entries every few minutes in the Replication Monitor, showing 'Bulk Copied data into 'MyTable' (34231221 rows)', whereas the larger table will generate only ONE log entry, showing 'Bulk coping data into table 'MyOtherTable', and it may take a couple of hours before there is anything else showing...except for an entry saying, 'The process is running and is waiting for a response from the server.'

My question is: what would be the difference between the two tables that would result in one generating MULTIPLE snapshot files, the other only a single, much larger one? The only difference I can see in the table definition is that the one generating multiple files has a clustered index, whereas the others do not.

View 0 Replies View Related

SQL Tools :: Script To Monitor Server Transactional And Snapshot Replication

Apr 28, 2015

Any TSQL script to monitor replication(Transactional, Snapshot) with current status ? I have tried below script but it giving error.

"Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested."

DECLARE @srvname VARCHAR(100)
DECLARE @pub_db VARCHAR(100)

View 4 Replies View Related

Transactional Publication With Updatable Subscriptions In SQL Server 2005, SvPk 2

Jul 31, 2007


Subscription to "Transactional Publication with Updateable Subscriptions" works only one way. Changes take effect on subscriber, but the subcriber is unable to update data on publisher.

I have Sanpshot Agent process running under SQL Server Agent service account with login 'sa.' All agents are running at the Distributor (Publishing Server.)

The subscriber is unable to connect to the Distributor using the SQL Server login.

Following is the error message I get:


Creating Subscription(s)...

- Creating subscription for 'SQL3' (Warning)



Messages

Unable to set the Publisher login for the updatable subscription. You may have to set this up directly on the Subscriber machine using sp_link_publication. (New Subscription Wizard)

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

MSDTC on server 'SQL3' is unavailable.
Changed database context to 'DB_SQL1_to_SQL3_on_3'. (Microsoft SQL Server, Error: 8501)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3054&EvtSrc=MSSQLServer&EvtID=8501&LinkId=20476


Please suggest procedure to end this dilemma. The link has no info available.

View 2 Replies View Related

Transactional Replication Setup (SQL Server Could Not Configure 123456 As A Distributor

Jul 17, 2007

Hello



I have server "123456" SQL 2005 On clustered environment. I added permissions to snapshot folder, it is on sharing for both nodes.



I got error during the set up as:



Microsoft.SqlServer.ConnectionInfo



Additional Information:

An exception occurred while executing a TSQL or Batch(

Microsoft.SqlServer.ConnectionInfo)



An error occurred during the execution of xp_cmdshell. A call to "Create Process" failed with error code 5



Destination path d:Microsoft SQL ServerMSSQL.1MSSQLData is not Valid. Unable to list directory contents. Specify a valid destination path

Changed database context to 'master' (Microsoft SQL Server, Error 15121)



The datafolder exists, sql cluster service account from domain added in to snapshot folder, administrators access also on this folder.



Please advice how to troubleshoot this issues. Thanks, Jay

View 1 Replies View Related

SQL Server 2008 :: Initialize Transactional Publication From Backup Loss Of Data

Mar 18, 2015

I have automated process, which synchronizes a transactional publication using initialize from backup approach. It drops subscriptions and puts them back again once the restore on the subscriber is completed.

Dropping the subscriptions causes a lot of blocking and deadlocking. I've decided to remove those steps, but it causes loss of data on the subscriber.

Is it a must to drop and re-create the subscriptions during such process? If not, how can I avoid the loss of data?

View 0 Replies View Related

SQL Server Admin 2014 :: Transactional Replication With Filtered Published Data

Apr 15, 2015

From distribution db, which table(s) store info about filtered data?

View 0 Replies View Related

SQL Server 2008 :: Transactional Replication Snapshot - Does Not Generate Any Files For New Article

Oct 22, 2015

There is a SQL Server 2008 R2 SP3 Clustered Instance that has Transactional Replication. It is by no means a large replication setup in terms of data/article count. SQL Server was recently patched to SP3 and is current on Windows 2008 R2 Patches.

When I added a new article to replication (via 2014 SSMS GUI) it seems to add everything correctly (replication tables/procs show the new article as part of the publication).
The Publication is set to allow the snapshot to generate for just new articles (setting immediate_sync & allow_anonymous to false).

When the snapshot agent is run, it runs without error and claims to have generated a snapshot of 1 article. However the snapshot folder only contains a folder for the instance (that does have the modified time of the snapshot agent execution) and none of the regular bcp/schema files.

The tables never make it to the subscribers and replication continues on without error for the existing articles. No agents produce any errors and running the snapshot agent w/ verbose output provides no errors or insight into any possible issues.

I have tried:

- dropping/re-adding the article in question.

- Setting up a new Snapshot Folder

- Validated all the settings and configurations

I'm hesitant to reinitialize a subscriber since I am not confident a snapshot can be generated. Also wondering if this is related to the SP3 Upgrade, every few months new articles are added to the publication and this is the first time since the upgrade to SP3 that it has been done.

View 0 Replies View Related

Transactional Replication - Updatable Subscribers - Image Data Type- Sql Server 2000

Aug 21, 2007

Sql Server 2000


I am looking for bidirectional transactional replication using updatable subscribers (queued or immediate) . Is it possible to replicate the image data from the updatable subscribers to the publisher. I understood that the Image data can't be replicated to the publisher from the updatable subscriber. I am not using the WRITETEXT or UPDATETEXT. I am using just INSERT and UPDATE for image data type transactions.

Any thoughts on this is greatly appreciated.

Thanks in advance

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved