I am beginning design for a project to maintain an on-line archive of historical data for a production database, and then truncate old, rarely-used data from the production database. I am considering a method whereby we (A) replicate data changes from the production database to an archive database and (B) truncate old data from the production database according to predetermined business rules.
I am trying to determine whether SQL Server merge or transactional replication can be used to accomplish the replication portion of this requirement.
Given this scenario, there are two types of modifications that are made to the production database: (1) application inserts, deletes and updates which should be replicated, and (2) truncation deletes (as described in "B", above) which should not be replicated.
My question is: Will I be able to control the replication process such that application deletes are replicated, but "truncation" deletes are not? Could static row filters somehow be used to accomplish this?
Is it possible to use replication for inserts and updates only for some tables and inserts updates and deletes on others. I want to be able to delete data from the source database but not my replicated database. Does anyone know of a way to use replication to facilite this?
HelloI had a question reagrding MSDTC usage. I know that MSDTC will enlisteach connection transactions as it's own (take ownership) andcommit/abort accordingly.However I'm not sure what would happen where I have a stored proc withinput parameters and 2 Delete commands on 2 different tables. In thiscase, would MSDTC also enforce ACID rules when 1 cmd succeeds and 2ndaborts ? In the script for the Stored Proc, there is no checking for@@ERROR value. There are just DELETE statement and a plain RETURN.thanksSunit
On SQL 7.0, NT SP6 we had transactional replication running fine last Friday. After a reboot (Fri night) replication continued fine and then by Tuesday am, we saw a lot of 'timeout errors' for the distributor agent and the difference in numbers of rows (count(*)) for same tables on each side grew alarmingly high even though the replication continued fine..
I have changed the timeout from '0' to '300' which seemed to help with timeouts, but the difference in row counts kept progressing slowly while the replication seemed to be running fine..
Eventually we resorted to re-intializing the subscriptions on the replicated database to get past the issue and now 24 hours later after intializing was complete, we are still pretty much in synch..
Any one have any ideas why we got so much out of synch (wrt row counts) .. ? Any bugs/ gotchas/ quirks any one can think of?
i have a publisher, a distributer and 3 subscribers all working. All created at the same time.
Today i have tried to introduce a 4 th subscriber server and replication has fallen over. Each cycle will have updates eg merged 75 data changes(6 inserts, 6 updates, 63 deletes, 48 resolved conflicts) but the next cycle will show: merged 55 data changes(4 inserts, 3 updates, 48 deletes, 37 resolved conflicts)
the 48 deletes are the conflicts from the previous cycle. this repeats hour on hour. The conflicts table is storing all of the changes but what have i done wrong in introducing the 4th server to get these symptoms? I followed the wizard all the way (may be that was it? :rolleyes: ) i have disabled this agent now and the others are working fine again. it is merge replication although the subscriber i have added is not going to be causing any updates.
I have configured a transactional replication between 2 MSSQL 2005 instances. It is set to replicate every 15 minutes. Most replications take less than a minute with about 10 - 50 transactions being replicated.
However, 3 times during the day, the replication takes about 25 minutes to complete with 500-700 transactions being replicated. This also causes the application that is accessing the DB being replicated to become unresponsive when it attempts to retrieve rows from tables in the DB.
Might there be a setting that causes the transactional replication to accumulate transactions during the day?
I'm trying to create a transaction replication from SQL Server 2000 to 2005. Basic replication between the servers works just fine. However, what I want to accomplish is to be able to skip some of the transactions. Example - from time to time we want to purge some of the historical data from the main database (the publisher). We don't want the same purging to occur on the destination database, which will be used for reporting purposes and needs to include all the historical information. I wanted to simply stop the replication log reader, purge the records, backup the transaction log with truncation and then restart the reader. The only problem - the truncation on the replicated database keeps the transactions of the purging until they are replicated, so the transaction log backup doesn't help. Any ideas would be greatly appreciated!
There is a database "Foo" sitting on server "A". There is a database "Bar" sitting on server "B". A.Foo publishes a subset of its schema. B.Bar subscribes to A.Foo's publication. The distribution database is on "B" (B.distributor). This a push subscription (transactions are pushed to the subscriber from the distributor). Every day (including the weekend) I get the following alert:
"5/12/2015 3:53:16 AM, Unsubscribed Transactions (Count) on "B" is Warning.
SQL Server instance "B" has 636771 unsubscribed replication transactions received by the Distributor and not received by a Subscriber.
Unsubscribed Transactions (Count): Number of replication transactions received by the Distributor and not received by a Subscriber."
The number of transactions will vary. The alerts will be sent between 1:20 AM (EST) and 3:30 AM (EST). I'm trying to figure out what is causing the backup of transactions. I assume the issue precedes the alerts by 30-minutes or so.
There are no backups occurringNothing is blocking the distributor agent in the subscription databaseJob activity is at a minimum; the few jobs running run throughout the dayThe machine has plenty of resources -- CPU, RAM, etc.The publisher database shows no signs of stress.
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 ?
I have multiple questions and would appreciate any suggestions in resolving them. I'm a novice to these issues.
1) First of all, what is the exact command to setup the trunc. log on chkpt. option on for a transactional log of a SQL Server 2000 database? Is this option on by default? I have noticed for one of the databases I'm managing that the transaction log was over 12 GB, while the db was only 425 MB.
2) How's it possible to run a DBCC TRACEON to see the content of the transaction log to see if we are having any issues with any uncommitted transactions, i.e. updates, inserts, and/or deletes.
3) What are the commands to truncate inactive transactions and increasing the readbatchsize?
4) lastly, how do I validate transactional replication via checksum and find valid latency between a small number of changes that need to be committed between publisher and subscriber.
We have a filtered publisher (SQL Server 2012) with several pull and push subscriptions that have been functioning fine for years.
We have just added a new pull subscriber from a remote server. We configured an alternate folder location on the subscriber (pointing to existing path on a remote server). We generated new snapshot and partitioned snapshot, and moved to subscribing server at alternate path.We're seeing a problem we've never seen before:
When the merge agent runs, it applies the schema and performs a few hundred bulk inserts, but then proceeds to 'download changes to subscriber' as though the snapshot is old (it is brand new and should have all transactions). We have never seen this before, i.e. all data is usually applied in bulk when creating a new or reinitializing an existing subscriber.
we marked the subscriber for re-initialization (from both the publishing and subscribing server) with a fresh set of snapshots. The same problem occurred.We have also tried in our test environment (which mirrors production) and the snapshot applies as expected (via bulk inserts).
(It might be relevant to note the publisher was recently upgraded in-place from 2005 to 2012.)
I am looking into work-arounds for what seems to be a flaw, or"undocumented feature" of SQL Server replication and Instead of Deletetriggers not playing together. It seems that if you want to usereplication then you cannot use Instead Of triggers as it prohibits thereplication triggers from firing. My delima is that I need to cascadedelete, but first have the record and all child records inserted intoassociated delete tables (<tablename>_del). This worked well usingInstead of delete triggers, but now we are moving to a replicationenvironment and it does not synchronize deletes to the publisher orsubscriber.The options I am looking into and the stumbling points they introduceare:1. Not using Instead Of triggers, but using After to insert thedeleted record into a <tablename>_del table (where of course<tablename> is the actual table name). The triggers are designed tocascade delete all related child records. The problem with thisoption is that there are relationship constraints that cause thedelete to raise an error complaining about these constraints. Ofcourse the child records need to be deleted first, but since thisruns after the delete I cannot perform this cascade delete viatrigger (or can I?).2. Same as above only using SQL Server 2k's "Cascade Delete"option. My question on this is, will the cascade delete execute theAfter Delete triggers for each child table in the relationship? Ifnot, is there a way to move these deleted records into<tablename>_del before they are deleted, keeping in mind that itmust be compatible with merge replication.3. Not yet found any information on this, but is it possible tomanually include information to be replicated after the "Instead Of"triggers so that the changes are included in the replication?I really appreciate the advice.
We use a SQL Server 2000 Publisher and 2 pull subscribers. Unfortunately we have some data loss from time to time. Some records are deleted during the replicaton process. Actually there shouldn't be any conflicts because of the permissions of the application, so we use standard conflict resolving that is selected when a publication is created, no extra options.
Any Ideas about that? Is it possible to dissallow deletes for replication?
BD1 ------------------------> BD0 Replicate with filter
BD2 ------------------------> BD0 Replicate with filter
BD3
Four data bases BD0,BD1,BD2,BD3 In BD1 y BD2 there are two merged publications subscribed continuous in BD0. The publications are defined as insert only and allow changes in the subscriber. Everything works perfectly. The records replicate immediately. In BD0 you can insert and modify records, however when I attemept to delete a record I get the following message:
error:
You can't insert a row with a duplicate key in the object 'dbo.MSmerge_tombstone' with unique index 'uc1MSmerge_tombstone'
Note: This is a translation of the original error message in Spanish below.
No se puede insertar una fila de clave duplicada en el objeto 'dbo.MSmerge_tombstone' con índice único 'uc1MSmerge_tombstone'
I setup replication (merge) for a test db with 11 tables. Deleted the publication and want to delete the database itself. Sql server says it can't be deleted becuase it's has replication setup! Bug??
I got the last two from Microsoft KB article 324401. The subscription was never created because it gave another error invalid object_name msmerge_subscriptions. I can create new publications but not drop the existing ones.
I created and scheduled a test snapshot publication and a push subscription using the sql server replication wizard. Tested it and it worked all fine as the data was regularly replicated to the destination server correctly.
After I was satisfied with the test I deleted the replication and subscription without any problem. But I noticed that the jobs created by the replication wizard had not been deleted. I was disappointed as I had also expected these to be deleted because there are no more any local publication or replication on the server.
I, then, tried to delete the job by right click - and then delete. But I am unable to delete the pubilcation and subscription jobs at all. When I try to delete the sql server 2005 the following error message appears:
--start-------------------------------------------------------------------- "Attempt to retrieve data failed for object for server <MYSERVER>" (Microsoft.SqlServer.Smo ) Addional Information
The job <jobname> does not exist on the server. (Microsoft.SqlServer.Smo) --End--------------------------------------------------------------------
Can anyone help me on how to delete all replication jobs.
when I look into the replication monitor of our sql 2005 server I see a lot of subscriptions that are no longer used.
I wrote a program for a smartphone using the sql compact edition (sql ce db). Just in case there are some problems i do not want to mentioned here, i use an preconfigured sql ce db, reinitialize it and afterwards synchronize it. Then the old subscription is no longer used.
The problem is that up to now I did find a way to delete this unused subscriptions at the publisher database. As a hint: there is no way to get access to the old sql ce db for removement issues. The dbs are gone.
So, is there a way to solve my problem?
We are using sql 2005 server as publisher and are using merge replication.
I had a publication (merge) setup for a database. Deleted the publication and tried to delete the database. Sql server says it can't be deleted because it's has replication setup. Bug??
In SQL Server 2005 SP1 we have a Transactional Replication with only this property in the articles:
Action: Delete data...
Ant this property in the publication:
Don't send schema changes
We have in the foreign keys NOT FOR REPLICATION = NO (Exigir para replicacion = "Si" in Spanish).
We have one problem and one doubt:
The problem is that when the sincronization goes wrong the table in the suscriptor is empty !!!!!. It seems that goes wrong because the foreign keys. The question is = Is necessary that NOT FOR REPLICATION = YES in all the tables?, we don't want any check in the sincronization.
Why the destiny tables appears empty?. Is very dangerous.
Hi there, I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following: <code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction? Any help is greatly appreciated
Hi. I have a SQL statement currently ending with "ORDER BY sc.TypeID, sc.ObjetID DESC"where the TypeID can be of value 1,2,3,4,5,6,7,8, 9 and 10. What I would like to achieve is to selectively order the datas by TypeID, meaning in this case that I want ALL the TypeID of value "1" to come first and then all the rest ("2" and higher) un-ordered. This is important that the rest remains unordered by their TypeID as I only want them to be ordered by their ID (ObjetID DESC). Is that possible? If yes how?Thank you.Francis
Hello all, Is there a way to do a selective restore? I need to create a dev db and the prod backup is 17 gig and I have a space crunch on the server. My idea was to restore the complete backup and subsequently shrink the db size after truncating data in tables which are not frequently used? Is that a good idea. Any help will be appreciated.
I need to be able to export from an existing database, all fields with a certain column id. I have been pointed in the direction of bcp, however I am having difficulty finding the right syntax.
I'm in the current mode of migration from WIn2k and I 'm trying to setup user where they are only allowed access to one table.
If I grant thenm read /write access to the db they can obviously see all of the db due to the size of the db I do not wish to go down the root of denying every table as there is over 100. Is there an easier way??
Any updates on tables at publisher are being converted to Delete/Insert pair at subscriber end with transactional replication. Our business requirement is not to delete data from few tables and also to get rid of Foreing Key Constraint error.
How to avoid this Deletes during Update? Any ideas about writing custom stored procs?
I was trying to use the case statement in where clause so that the comparison (> , <, =) can be made depending upon the value of the local variable @fl.
PLease tell me how to do that. Or suggest me how can I make selective comparison (<,>,=) in where clause depending upon a local variable. Please note that I donot want to use if/else satement and write select statement everytime based on the local variable as in that case the code length will be huge. Please suggest me a coensized query.
SQL that I queried:
use pubs go
declare @fl int
select fl = 3
select * from titles where case fl when 1 then price > 90 when 2 then price < 90 when 3 then price = 90 end
The following error is given
Server: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near '>'.
HI, Is it possible in SQL Server to restore Databases partially? In the sense, I want to backup just 2 tables from a database & restore them, instead of having to take a backup of the entire database & restore the entire database.
One way to do would be to export data & then importing data for only those selective tables, but I dont want to do that, bcause that would probably take more time & can be error prone.
I need to 'copy' selective (filtered) rows from table 'A' to table 'B' on a regular basis, but only if they do not exist in table 'B'. If rows get deleted from table 'A' they must remain table 'B'. If rows change in table 'A' (unlikely) they should be update in table 'B'
What feature of SQL should I be using? - triggerering a stored procedure on Insert - replication - SSIS
