Why Transactional Replication Option Is Disable?

May 3, 2007

i read about what replication is and what are the types of replication but when i m trying to implement this i am unable to perform one of its type "Transactional Replication" i have no idea why this option is disable,can u plz guide me where i m wrong?

View 6 Replies


ADVERTISEMENT

Transactions Are Still Pending For Distribution After Disable Transactional Replication

Apr 11, 2007

Recently we disabled the transactional replication on a prodution database(data size:300GB) , two weeks later we were aware of the transaction log of that db almost reached the limitation (70GB).I investigated the issues and found:

1. open transactions pending for distribution:

DBCC opentran('xxxx')



Replicated Transaction Information:

Oldest distributed LSN : (0:0:0)

Oldest non-distributed LSN : (323830:7083:1)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.



2. exec sp_replcounters, got over 240,000 replicated_transactions, which caused the tran log extremely large.



The transactional replication has been disabled on this server, there are no publication, distribution DBs any longer, all replication-related agents are also gone, why did this still happen???



3. use sp_repldone manaully reset those 240,000 transactions to distributed, check opentran, it was fine for a while. The tran log file was freed up 99% after that.



4. check open transactions and run sp_replcounters today, found the over 60000 transactions again on the db.



Did anybody experience the same issue and knows how to fix this issue and stop this happening ?



Many Thanks,

Henry

View 4 Replies View Related

How To Disable Connect Option In Query Analyzer

Feb 28, 2004

Does anyone know how to diable the File => Connect option in Query Analyzer?
Thanks

Clarification:
The situation is that I have several users on an application that controls their access to the DB. The user ID has full rights to the DB but the application stops them from making changes to data. Now I have a user that needs to be able to create and run SELECT statements in a “Query Analyser” type of environment. I can publish a Query Analyser session with locked credentials using Citrix, but I don’t want him to be able to use the File => Connect option to reconnect with one of the application Ids thus giving him full rights in an open environment.
Any ideas?
Thanks

View 1 Replies View Related

Want To Enable/disable Report Builder Option For Set Of Users

Feb 8, 2008


I want to restrict report builder access only to a reporting alias and block for other users.
Please suggest the solution

View 3 Replies View Related

Combining Peer-to-Peer Transactional Replication With Standard Transactional Replication

Dec 1, 2006

Hello,

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

View 4 Replies View Related

Replication Issues After A Database Restore - Unable To Drop Or Create Transactional Replication

Sep 13, 2007

Hi,I have transactional replication set up on on of our MS SQL 2000 (SP4)Std Edition database serverBecause of an unfortunate scenario, I had to restore one of thepublication databases. I scripted the replication module and droppedthe publication first. Then did a full restore.When I try to set up the replication thru the script, it created thepublication with the following error messageServer: Msg 2714, Level 16, State 5, Procedure SYNC_FCR ToGPRPTS_GL00100, Line 1There is already an object named 'SYNC_FCR To GPRPTS_GL00100' in thedatabase.It seems the previous replication has set up these system viewsSYNC_FCR To GPRPTS_GL00100. And I have tried dropping the replicationmodule again to see if it drops the views but it didn't.The replication fails with some wired error & complains about thisviews when I try to run the synch..I even tried running the sp_removedbreplication to drop thereplication module, but the views do not seem to disappear.My question is how do I remove these system views or how do I make thereplication work without using these views or create new views.. Whyis this creating those system views in the first place?I would appreciate if anyone can help me fix this issue. Please feelfree to let me know if any additional information or scripts needed.Thanks in advance..Regards,Aravin Rajendra.

View 2 Replies View Related

Replication :: Transactional Replication Removal Sequence

Sep 2, 2015

I have been researching on the proper steps or sequence to follow to completely remove SQL Server 2012 Transactional Replication.  I have read articles about using SSMS as well as using replication stored procedures and some procedures use SQLCMD or just regular TSQL executed in SSMS.  I have also read articles where people said all you really need is connect to the Publisher instance, find the publication you want to remove and choose "Delete" and everything will be taken care of behind the scene. I have three SQL servers that participate in transactional replication.  SQL-P (publisher), 

SQL-D (distributor) and SQL-S (subscriber).  Do I need to connect to the distributor instance and the subscriber instance when removing transactional replication or is it just really connecting to the publisher and click delete on the publication? I want everything gone including any metadata, systems tables, distributions db and any other replication objects created during the initial configuration.

View 6 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

Disable Trans. Replication - HELP!

Mar 20, 2000

Hi,

I am trying to disable transactional replication, but am having some problems. I used the wizard, however, it has taken 7 hours so far and is still not done. SQL Server (7.0) is showing the connection as runnable but it seems as if nothing is being removed. Am I missing something? Should I have done something else before running the wizard? I can't even kill the SPID.

Can anyone help or offer some advice?

Thanks in advance...

View 1 Replies View Related

Can Not Disable Replication In SQL7

Oct 24, 2000

Hi, guys

I have an urgent problem in replication.

Yesterday I set up a Transaction replication with 1 publication
Server A (publisher)(push)(SQL 7)
Server B (distributor and subscriber)(SQL 7)

The replication was succesful and the suscriber can get correct data.

This morning I found the Red sigh in replication mornitor/miscellaneous agents.The error message is:
"The job failed. Unable to determine if the owner (domain nameuser ID) of job Agent history clean up: distribution has server access (reason: Could not obtain information about Windows NT group/user 'domain name'. [SQLSTATE 42000] (Error 8198))."

I tried to use the 'disable publishing and distribution wizard' to disable this replication, but it did not finish and the screen jusr freeze. I used the task manager to end the task.

then I check the replication monitor, the publication was gone but the RED sigh still there.

I tried to use sp_dropdistpublisher to drop a Publisher
but it said "the server A is not a distributor"

I tried to use sp_dropdistributiondb to delete the distribution database.
but it said "Could not drop the distribution database 'distribution'.
This distributor database is associated with a Publisher."

I tried to use sp_dropdistributor to remove the Distributor .
but it said "Could not drop the Distributor 'Server B'.
This Distributor has associated distribution databases."

Any suggestion? Thank you in advance.

Theresa

View 2 Replies View Related

Temporarily Disable Replication

Jun 15, 2007

Is there a way to temporarily disable replication? My problem is that I have pull subscribers that I do not want accessing the database at certain times, so that data updates and backups can occur. Is there a way to make that happen?

View 4 Replies View Related

Transactional Replication

Jul 30, 2001

On my distribution server there is a directory called mssql7
epldataunc that houses the bcp, sch and idx files for replications. A directory is created for each publication. Most directories are empty but some contain all the table idx, sch and bcp files for the publication. Some of these files are over 6 month. Does anyone know if these files are neccessary to keep and why do only some publications have these files. I am wondering if they are kept when you push the publicaiton and it errors out originally and you have to restart it? If any one can shed some like on this I would appreciate it.

View 1 Replies View Related

Transactional Replication

Dec 28, 2000

Hi,
I am planning to do a transactional replication from production server to reporting server. In one of my database is having 150 tables in it, only 100 tables are having primary key . Remaining tables are not have primary keys. So i am not able to do transactional replication for those 50 tables. Could any one tell me how i have to approch transactional replication. Because i need to have production data at my reporting server in regular interval.
So pls suggest me .
I am using Sql 7.0 with service pack2.

Thanks!
Kavira

View 1 Replies View Related

Transactional Replication

Feb 13, 2001

I have a problem with transactional replication in SQL Server 7. I have two databases (a publisher-distributor server and a suscriber server) and when I do an UPDATE in the publisher database the Log Agent Reader reads the UPDATE as an DELETE-INSERT and when the suscriber pull the suscription and try to do first the DELETE the transactional replication stops because of the Foreign Keys in the suscriptor database (that are the same as the publisher).
If anybody knows how to solve it, please tell me

Thanks

View 1 Replies View Related

Replication - Transactional - 7.0

Nov 1, 1999

I have two servers networked via workgroups (no domain) and am trying to install Transactional Replication. Everything appears to install correctly, but when I run the initial Snapshot Agent I get the following error -

The process could not create file 'CONCEPCIONC$MSSQL7ReplDatauncCONCEPCION_Erik_Rep lication_Erik_Replication19991029153207Employer.sc h'.
Access is denied.

The SQLAgent is starting with same user account on both machines - admin rights and the same password. I have added this user to the (
epldata) folder permissions as well. I even see a "Employer.sch" file in the directory.

What am I missing?

View 2 Replies View Related

Transactional Replication

Oct 25, 1999

Hello,

I am planning to implement replication between two servers (either snapshot/transactional). Those two servers are physically connected but we don't have domain concept i.e we connected both servers physically and it can be pinked from each other, but not under any domain (not also under different domain). How should I register SQL server& agent services to start with 'Service startup Account'. In this case I cannot start both servers using same domain Account, they how can I relate...

Thanks

View 1 Replies View Related

Help... Transactional Replication

May 22, 2003

Hi guys can anyone help me with this issue. if so thankyou kindly.

1. created transactional publication (for tables, stored procs & views )
2. created empty database at subscriber
3. set up pull subscription at subscriber
4. ran the initial snapshot agent at the publisher
5. checked the schema files & found out it hadn't scripted the primary keys on the tables.

Problem: PRIMARY KEY AINT GOIN ACROSS, COULD ANYONE TELL ME IF IVE MISSED SOMETHING? THANK YOU MUCH APPRECIATED GUYS.

? Could it be that it is not picking up the primary keys on the tables because I am replicating stored procs and views with that publication?

? Should I break my publications into 3; say 1 for each object?

Thank you guys for taking the time to read this.

View 1 Replies View Related

Transactional Replication

Feb 24, 2005

Hi

I am tring to implement transactional replication.

How do i publish tables which donot have primary keys?...because transactional replication seems to publish only the tables which has a primary key.

Thanks

Madhukar

View 7 Replies View Related

Transactional Replication

May 1, 2002

Hello all! I am setting up my first Replicated database, and want to get this right.

We are a Newspaper, and I will be Publishing the database that is the back end of the web server. The goal is to load balance the web site as well as to have a second identical server in case something goes wrong. Both the Subscriber and the Publisher need to be kept in sync as new articles are posted, so my client will just be updating the Publisher. But I'd like to keep a single set of usage statistics, so I was thinking of using Immediate Updating so the Subscriber can update the Publisher using two-phase commit.

What will happen if the Publisher shuts down and an update is attempted on the Subscriber? Will it just hang? Let's assume the worst - what if my Publisher is kaput? How do I determine that my other database is a Subscriber; and how do I stop it from being a Subscriber?

Thanks in Advance,

View 1 Replies View Related

SQL Transactional Replication

Jun 28, 2002

Hi

I have a requirement for pushing transactional publication to various subscribers. Each subscriber will need to receive a different subset of rows from a table. So I need all rows published, but with horizontal filtering at subscriber level. Is there a way of applying filtering at the subscription level.

Thanks
Tariq

View 1 Replies View Related

Transactional Replication

Sep 11, 2007

I am using transactional replication between two SQL 2005 databases. On Sunday evenings, we run a maintenance plan that rebuilds the indexes on the publisher DB. When the plan runs, the replication receives errors and cannot continue. The error appears to be at the publisher level. The publisher runs continually and the subcriber runs every 10 minutes. The error states "cannot run sp_replcmds" Other than Sunday, everything runs fine.

Thank you

View 1 Replies View Related

Set Up Transactional Replication

Jul 7, 2015

I want to set up the transactional replication. How many service accounts we need and what kind of permissions it required in windows level?Only two servers we are using. Pub/Distributor and Subsciber.Which option is better Push or pull?

View 1 Replies View Related

Transactional Replication

Jul 27, 2007

at the 'specify articles" on the wizard. Some tables have a key next to it that doesn't let me select them to be replicated. This is happens on transactional replication. But if i do a snapshot, the keys next to the tables are not there.

How am I able to select those tables with a key?

=============================
http://www.sqlserverstudy.com

View 8 Replies View Related

Re Transactional Replication

Jan 11, 2006

Hi All!

Need help guys... I'm new to SQL Replication, though I successfully created a transactional replication testing on my machine using PUBS DB and a separate DB.

I'm planning to establish/setup a Transactional Replication. There are two SQL Servers, one is being used by WebApp_1 and the other is being used by WebApp_2.  Both servers are maintain by different administrators and situated on different domain/location (both used Win2003 Server).  We will transfer some data from Server_1 to Server_2. Server_1 will be setup as Publisher and the other is the Subscriber, then additonalServer_3 will be added and will serve as the Distibutor.

My questions are:

1. Do I need to request separate domain account from the two different servers to establish connection to the Distributor?

2. Will the User databases from Publisher in compromise/affected as far as database settings and data are concerned?

3. Any useful links?

Any help provided will be greatly appreciated...

Thanks!

 

View 1 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

Transactional Replication Errors

Feb 7, 2000

I setup Transactional replication between two SQL Servers.

I am getting the following errors: " Could not find stored procedure 'sp_MSins_stores'." and the job fails.

I dont have any idea why I am getting these errors. I am getting this error for every table in the publication.

Any suggestions are welcome.

Thanks in advance.

View 1 Replies View Related

Transactional Replication - Error 207

Jan 19, 2003

Hi Guys,

I have a small problem with the transactional replication when I run the initial snapshot and distributing some of the stored procedures. For eg., Say if the stored procedure has the following statements:

---------------------------------------
create procedure abc
as
declare @paytype varchar(50)
set @paytype='check'

insert into acct select cusname,cusacctno,@paytype from cuspay
---------------------------------------

I get the following error:
for statement:

set @paytype='check'
--------------------
Invalid column name 'check'.
(Source: ServerSQL1 (Data source); Error number: 207)

I tried changing to double quotes. even tried adding article using query analyzer without using EM

exec sp_addarticle @publication = N'ReplicationSrc', @article = N'abc', @source_owner = N'dbo', @source_object = N'abc', @destination_table = N'abc', @type = N'proc schema only', @creation_script = null, @description = null, @pre_creation_cmd = N'drop', @schema_option = 0x0000000000000001, @status = 16
GO

Still I get the same error. The server has SQL server 2000 SP2 running with remote distributor and pull subscription.

Am I missing anything.


Please help.

Thanks,
Anu

View 2 Replies View Related

Problem With Transactional Replication

Jan 29, 2005

hi ,
i am trying out transactional replication to replicate over 60 gb of data.

i am having a problem during the initial creation of the article.

the system hangs at article 73 out of a total of 600 + articles

i have tried this using sp_addarticle as well
but in vain

any suggestions
please let me know

thanks

View 3 Replies View Related

GUID's Req'd For Transactional Replication??

Aug 22, 2002

Does SQL Server 2000 (Enterprise Edition), mandate the creation of a GUID on each table used in transactional replication?

I was told that in addition to defining a Primary Key, I "MUST" also define a GUID on each table. As I recall - this is not true. Can someone who's setup Transactional Replication please field this question?

thx -
~BT

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

Transactional Replication On RAID 5

Dec 17, 2005

Dear All,

if i have a SQL Server running on a machine with RAID 5 array and i will be performing transactional replication on it, what are the implications?

is there any issue with it since it is a transactional replication with many writes?

there will be a lot of writing n reading to the database.

Can someone advise on this?

Thanks in advance.

Cheers,
white

View 1 Replies View Related

Transactional Replication Error

May 5, 2006

I am running sql 2000 sp3.
A pull subscription failed with the following.

errormsg: Cannot drop the table 'table_name' because it is being used for replication.

errordetail: Cannot drop the table 'table_name' because it is being used for replication.
(Source: SQLTEST2 (Data source); Error number: 3724)

This error started after a field was dropped and a new field added to one of the articles in the publication - changes were done through Enterprise Manager.

Why does this error come up. The Default Table Article Properties dialog box>>>Snapshot tab has the option "If a table with the same name as the destination table exists at the Subscriber>>>DROP the existing table and recreate it.

So if this is an option in replication for a Subscriber how come the error message says it cannot drop the table.

Any insight appreciated.

View 2 Replies View Related







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