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 RepliesFor SQL 2005 transactional replication I have 1400 articles (tables, views, functions, sp) that are all replicated.
Will SQL 2005 allow me to drop a table from from the publication and drop it from the publication database that is referenced by exising views or sps that are replicated or does it somehow check that?
Linda
I have 1 sql server 2005 database in Server A that is publishing a table to another database in Server B using the transactional method.
It is possible for me to delete all the rows in the database located at the publisher at the same time the subscriber pulls them?
Thanks.
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 RelatedIs 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?
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?
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.
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.
We are using push subscription using transactional replication. Is there a recommended value for retention period on distributionDb? We are using default value of 72 hrs and recently we saw an issue where data was not replicated with an error that subscription was inactive. When I searched, I fid that it is related to the retention period setting on distribution DB.
View 0 Replies View RelatedI'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.
In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.
Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR
In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).
As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.
I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?
Any SQL that can be used to identify all objects that depend on a particular table? For example, sprocs, functions, views, etc.
View 3 Replies View RelatedI have a scenario where I need to add a blank column to a table that is a publisher. This table contains over 100 million records. What is the best way to add the column? In the past where I had to make an update, it breaks replication because the update would take forever as jobs are continuously updating the table so replication can't catch up.
If I alter a table and add a column, would this column automatically get picked up in replication?
Hi All,
Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.
I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.
This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)
MH (source) -> MH1 (Replicate)
The column €œcol1€? had width of varchar(10) and this was altered to varchar(100).
insert into MH..test_mh values(4,'abcdeabcdefff')
select * from MH1..test_mh
exec sp_dropsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2', @destination_db = N'MH1'
go
exec sp_droparticle @publication = N'MH', @article = N'test_mh'
go
alter table test_mh alter column col2 varchar(100) null OR
MH1..sp_help test_mh
exec sp_addarticle @publication = N'MH', @article = N'test_mh', @source_table = N'test_mh'
go
exec sp_addsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2' , @destination_db = N'MH1'
go
Needless to say, help would be apreciated -
~Mihir
We have a database we are replicating to about 8 SQL Express subscribers from a SQL 2012 SP2 publisher. The size of the database grew too large for the 10GB license limit for SQL Express and now replication refuses to replicate any of our deletions on the publisher to reduce the size of the database. I've come up with a few options below.
1) Drop one of the larger table indices on the subscriber database to get below the size restriction. Permit the replication to replicate the deleted records and then rebuild the index. (I'm not sure how important an index is to this table. Is it merely performance related?)
2) "Upsize" SQL Express to SQL Standard on the affected boxes. Allow the deletes to replicate. Backup the database, downgrade to SQL Express and restore the database back to SQL a new SQL express instance. This would involve a lot of work on each box. I'd like to avoid it if possible.
I am task with identifying the source database name, id, and server name for each staging table that I create. I need to add this to a derived column on all staging tables created from merging same tables on different servers together.
When doing a Merge Join, there is no way to identify the source of data so I would like to see if data came from one database more than the other servers or if their are duplicates across servers.
The thing that bugs me about SSIS Data Flow task is there is no way to do an easy Execute SQL Task after I select my ADO.NET Source to get this information because my connection string is dynamic and there is no way of know which data source is being picked up at runtime.
For Example I have Products table on Server 1 and 2:
Server 2 has more Products and would like to join the two together to create a staging table.
I want see the following:
Product ID, Product Name, Qty, Src_DB_ID, Src_DB_Name, Src_Server_Name
1 IPAD 1000 2, MyDB1, Server1
100 ASUS Pad 40 1, YourDB, Server2
get database name and server name in DATA FLOW only (without using a for each in Control Flow)
Is it possible to view the Connection String information of a remote login/session? I want to know if the login is looking-up the database server via IP address, servername (NetBIOS name) or fully-qualified domain name (FQDN).
Using these DMVs I can get a lot of relevant information:
sys.dm_exec_sessions
Program Name (eg. Microsoft SQL Server Management Studio),
Client Interface Name (eg. .Net SqlClient Data Provider)
sys.dm_exec_connections
Net Transport (eg. TCP),
Client Net Address and TCP Port
but not how the server's IP address was resolved. Is the connection string ever sent by the client to the server, or just used for DNS lookup?
How do i check the size of the datacache allocated from the buffer pool by sql server?
DMV or anything to show me the pool allocation sizes for the various pools in sql server i think i may be able to work from there.
We are doing a review of a SQL 2008 server. Though we can identify what are the linked servers to the database.
However is there a sure shot way to identify, incoming DB links to the database server. I know the successful connections are easy to identify, but I wish to know all possible incoming DB links to server.
Will the DB logs support in identifying all attempted and successful DB link connections.
We had an issue recently where a (transactional) replicated table was replicating data as expected.
Then about 30 or so rows in the source table were not in the destination table, but other rows created after those 30 rows were replicated.
We have pretty much confirmed that users did not delete those rows.
Unfortunately we had to resolve the issue quickly and so blew away & recreated the subscription so a lot of evidence is probably gone from the crime scene.
We cant figure out what could cause 30 rows not to be replicated, yet leave replication operational.
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
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?
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
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?
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
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.
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?
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.
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.
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)
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
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
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 RelatedFrom distribution db, which table(s) store info about filtered data?
View 0 Replies View Related