Transaction Replication && Data Archiving On SQL Server 2000

Jul 23, 2005

Hi techies


I have set up a Transaction replication from My Primary Server to
Secondary Server on Orders table.

Thousand of records gets inserted on Orders every hour which get
replicated on the secondary server. it works fine

reporting apps uses Secondory server's Orders table data for generating
reports .

The Problem :


Let say if i want to Remove older records from Orders table in the
primary serverwith out reflecting this change on the secondary server.

is there a way to PREVENT this operation /transaction to be propogated
to the secondary server.


Note : i am moving the records to another table (orders_Archive ) and
deleteing the rows from orders table . Also I need all the rows to be
present on the secondary server table.

Please advice ASAP

Regards,
Raj

View 4 Replies


ADVERTISEMENT

How To Skip Snapshot In Transaction Replication (from SQL Server 2005 To 2000)

Jun 13, 2006

We have two SQL Server 2005 production DB at remote sites. Due to network bandwidth issue, we need to replicate these DBs (publishers and distributers) to central corporate SQL 2000 DB (subscriber for backup and possible reporting (and in rare case as a failover server).

We would start out with backup from SQL 2000 db restored on remote SQL 2005 DBs. When we have DB issue on remote 2005 DB, we want to restore it from central corp. 2000 DB backup. Since two DBs are replicating to central DB, we DO NOT want combined db back up data on restored remote 2005 db. We can restore the db and delete unwanted data before we turn on replication from this restored server. So, this is not a problem.

The real problem is how to avoid snapshot replication (during initialization) when we create a transaction replication on this restored server to avoid over writing data on the central subcriber sql 2000 DB???

HELP!!



View 5 Replies View Related

SQL Server 2000 Data Replication

Apr 5, 2007

I want to replicate SQL Server 2000 data from publisher to subscriber with new data added and existing data modify at subscriber, is it possible? what is the solution.





View 4 Replies View Related

Info On Data Replication On Sql Server 2000.

Jul 20, 2005

Hi,I'm in need of detailed information on how data replication works on thesql server 2000. If someone knows about a book or articles thatdescribes how data replication works with transactions, storedprocedures and/or merging databases I would like to be pointed in theright direction./Zero_Addiction.

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

Archiving Data

Aug 2, 2000

Hi everyone!

My problem is, that i don#t know how i can archive the data. That means to documentate when, who, etc. changed the data (in a seperate table).
I tried to solve it with different triggers.

Thanks in advance,

Maria.

View 1 Replies View Related

Data Archiving

Oct 6, 2004

Hi ,

I need to archive my production database to a new Server.....

Is it possible to move data using INSERT INTO ServerName.DBName.dbo.TableName from the current Database Server!!!

Do I need to create a linked server to do this....or shoud I go for DTS..

Thanks
Cheriyan.

View 1 Replies View Related

Data Archiving

Jan 11, 2007

What's the best archiving procedures? :)

SlayerS_`BoxeR` + [ReD]NaDa

View 2 Replies View Related

Archiving Data From Some Tables - Timestamp Data Type?

Aug 21, 2015

I'm working on archiving data from some tables. I've duplicated the data structure, with the exception of not including the IDENTITY specifier on INT columns, so that the archive table will keep the value that was generated in the original table. This is all going well, until I tried to copy the data over where the column is specified as a timestamp data type. I've looked this up and found a couple of things. First, documentation for SQL 2000 says,

Timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

And then documentation for the soon to be released SQL 2016 on the rowversion data type says,

The timestamp syntax is deprecated. This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

and

Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion is generally used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The rowversion data type is just an incrementing number and does not preserve a date or a time.

OK, I've read the descriptions, but I don't get it. Why have a timestamp/rowversion data type?

View 9 Replies View Related

Archiving Data To Flat File?

May 24, 2002

How do I put data into a text or excel file before I attempt a deleteion from a large table. I know how to select the necessary data, but i'm not sure about the t-sql required to put it into a file?

are there any better methods of archiving?

thanks

View 3 Replies View Related

Archiving And Deleting Records (Data)?

Aug 10, 2015

I wrote a script to archive and delete records rom a table back in 2005 and 2009.

I can't seem to get the syntax right. Any sample script to simply archive and delete records?

This is what I have so far.

DECLARE @ArchiveDate Datetime
SET @ArchiveDate = (SELECT TOP 1 DATEPART(yyyy,Call_Date)
FROM tblCall
ORDER BY Call_Date)
--SELECT @ArchiveDate AS ArchiveDate
DECLARE @Active bit

[Code] ....

View 9 Replies View Related

Data-archiving And Purging Strategy

Mar 31, 2008



Regarding SQL Server data, I am looking to implement the beset Data-Archive and Purge policy. Normal, we do SQL Backups and keep the history for some period , for example, 8 weeks, so we can go back and restore any data point in time upto 8 month in past. and we also do Tape backups.

Question is Where can I get nice article or documentation on this to best design such policy where I make sure that I am covered for point in time recovery of database (which is sql backups) and point in time recovery in far past, say, 3 years ago using tape backups, and I need to make sure that I don't repeat the same efforsts.

Any advices or suggestion on this topic.

Thanks,

View 5 Replies View Related

Archiving Data Transformation Service Packages

Feb 29, 2000

Is there a way, for example to script a DTS Package, so that it can be deleted and recreated at a later date if necessary? I have quiet alot of these, but few are used regularly. The msdb database is now up to 80 MB. However I don't want to delete them and have no way to recreate them.If I took a backup of msdb and then deleted the packages, would restoring msdb at a later date restore the packages?????

View 1 Replies View Related

Extracting Data From A Table For Archiving Purposes

Sep 22, 1999

Hello,

I have been placed in the position of administering our SQL server 6.5 (Microsoft). Being new to SQL and having some knowledge of databases (used to use Foxpro 2.6 for...DOS!) I am faced with an ever increasing table of incoming call information from our Ascend MAX RAS equipment. This table increases by 900,000 records a month. The previous administrator (no longer available) was using a Visual Foxpro 5 application to archive and remove the data older than 60 days. Unfortunately he left and took with him Visual Fox and all of his project files.

My question is this: Is there an easy way to archive then remove the data older than 60 days from the table? I would like to archive it to a tape drive. We need to maintain this archive for the purposes of searching back through customer calls for IP addresses on certain dates and times. We are an ISP, and occasionally need to give this information to law enforcement agencies. So we cannot just delete it.

Sorry this is so long...

Thanks,

Brian R
WESNet Systems, NOC

View 1 Replies View Related

SQL Server 2005 Transaction Replication

May 11, 2007

We are implementing 2005 transaction replication on source database to target staging subscring database but we want to keep all transaction changes from source within staging subscribing tables.
If source column gets updated we want to keep old record and new updated record in staging subscriber. Transaction replication synchronizes but does not keep history on subscriber. Do we update stored proc's
anyone have examples of code or ideas??

View 20 Replies View Related

I Need Away To Show The Pending Transaction From Transaction Replication In A User Friendly Format.

Jul 11, 2007



I want to list out the pending transaction for transaction replication by publication.



Help needed.

View 1 Replies View Related

Skipping Transactions In SQL Server Transaction Replication

Jun 14, 2007

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!

View 5 Replies View Related

SQL Server 2008 :: Transaction Replication Functionality Within Mixed SPs

Jul 1, 2015

Currently, we are on SQL2008R2 EE SP1 CU13 across the board.

We are planning on upgrading the primary SQL server to SP3.

Question: Will transactional replication continue to work properly even if the Publisher is upgraded to SP3 but the subscribers remain at SP1 CU13 ?

View 2 Replies View Related

MSDE 2000 Replication To SQL Server 2000

Jun 27, 2006

i went through the documentation but i was not clear on following, here is the scenario :-

a Central server is having SQL Server 2000

3 Remote Locations :- Each having 4-5 no of computers, connected on a Lan, and on one of the machines MSDE 2000 will be running.

My Questions are :-

1. Can remote locations, update data locally and send changes (say in every one hour) to central server. If yes, then how ??

2. Same way they can receive updates from Central Server on whatever was updated on Centra Server or on the 3 remote locations. If yes, then How ??

any help will be highly appreciable.

View 8 Replies View Related

SQL 2012 :: Setup Of Transaction Replication Between One Publisher And Subscriber In Same Server

Jul 30, 2014

I have a setup of transaction replication between one publisher and subscriber in the Same server.Now, I need to add a new subscriber to the existing publisher. So publisher database name is DB_A and Subscriber 1 name is DB_B. So the new subscriber will be DB_C. Is this kind of setup possible on one server?

If yes then at the time of reinitialization is it going to apply the snapshot on DB_B as well as DB_C?Also let say if due to disk error DB_B gets corrupted then will data be still replicated between DB_A and DB_C? (Assuming publisher, subscriber 1 and 2 are sitting on individual disks).

View 1 Replies View Related

SQL Server Admin 2014 :: Skip Schema Changes For Transaction Replication

Jun 6, 2015

Whenever I am doing reinitialization, publisher table schema is applying at subscriber level

I don't want to apply all changes ...

How can I skip the schema changes for transaction replication ...

View 1 Replies View Related

Replication :: Difference Between Snapshot And Transaction And Merge Replication?

May 26, 2015

What is the main difference between snapshot and transactional and merge replication?

View 5 Replies View Related

Archiving Analysis Server Dbs

Jul 20, 2005

Hello,Does anyone know of a way to schedule the archiving of analysisdatabases? Seems pretty lame if you can't... The only answer I've gottenis "maybe in Yukon"....Thanks.*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

SQL Server 2000 SP3 Transaction Log Errors

May 31, 2008

Hi!

We have a Microsoft SQL Server 2000 SP3 running database for Microsoft
Navision 3.7

From time we encounter problems, especially when running heavy query
procedures from Navision, with the transaction log. It's actually setup as
folows:

File properties:
File growth By percent (10)
Restrict file growth (MB) 10000

OPTIONS:
Recovery model: simple
Settings: Autoupdate statistics, Auto create statistics, Autoshrink

We get the following errors (once every 2-3 months so far):

The log file for database 'ME_Prod' is full. Back up the transaction log for
the database to free up some log space..


in between numerous abovementioned messages I have the following:
Configuration option 'show advanced options' changed from 1 to 1. Run the
RECONFIGURE statement to install..

Could not write a CHECKPOINT record in database ID 9 because the log is out
of space.

Automatic checkpointing is disabled in database 'ME_Prod' because the log is
out of space. It will continue when the database owner successfully
checkpoints the database. Free up some space or extend the database and then
run the CHECKPOINT statement.

View 10 Replies View Related

SQL Server 2000 And Transaction Log Error

Jul 20, 2005

Hi All:I am getting an error when trying to open a recordset in SQL Server 2000.The error states that the transaction log is full. Is there any way I canclear out or empty the transaction log, or get rid of it alltogether as itis not really needed?Any help would be appreciated.Thanks and regards,Ryan

View 1 Replies View Related

Controlling A Transaction By User In SQL Server 2000

Jun 14, 2007

Hey Folks!
I have a typical requirement by my client. On submitting a Update (Bulk) button a huge database operation starts. A huge bulk update operation need to be performed. This would take 2-3 minutes some times. Client wants a cancel button in this case where he can be given a way to cancel the database Transaction.
 Please let me know in case if there is a way out.
Thanks, in advance.
Regards,
Uday.D

View 2 Replies View Related

SQL Server 2000 And Transaction Log Error - More Info

Jul 20, 2005

Hi All:I am getting an error when trying to open a recordset in SQL Server 2000.The error states that the transaction log is full. Is there any way I canclear out or empty the transaction log, or get rid of it alltogether as itis not really needed?Any help would be appreciated.Thanks and regards,RyanThe error msg is: tempdb transaction log is full. B/U transaction log tofree up space..."

View 2 Replies View Related

SQL Server 2000 - Table Transaction Date

Aug 9, 2007

(I may be in the wrong forum.)
How do I obtain/find the properties of a table using SQL Query Analyer (SQL Server 2000)? Specifically, I would like to run a query to find the most recent date of any transaction on a table. I have a script that I use for SQL Server 2005 but it doesn't work in 2000. I don't know 2000 but I'm guessing that the syntax is different?

Here's the 2005 SQL Server script (stolen from 2005 Books Online BTW):

CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));

GO

CREATE TRIGGER ddl_log

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

DECLARE @data XML

SET @data = EVENTDATA()

INSERT ddl_log

(PostTime, DB_User, Event, TSQL)

VALUES

(GETDATE(),

CONVERT(nvarchar(100), CURRENT_USER),

@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),

@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;

GO


Here's the error:

Msg 156, Level 15, State 1, Procedure ddl_log, Line 2

Incorrect syntax near the keyword 'DATABASE'.


Remember, I want to do the same thing in SQL Server 2000.

Thanks in advance for any assistance you can provide.

View 1 Replies View Related

Implicit Transaction Mode In SQL Server 2000

May 17, 2007

Hi all:



I know i can use the sentence SET IMPLICIT_TRANSACTIONS ON in a Stored Procedure to force SQL Server to set the connection into implicit transaction mode.



Have i a sentence or configuration to force all SQL Server connections to implicit transaction mode?



Thanks in advance.

View 2 Replies View Related

Analysis Services Archiving Is Working On One Server

Oct 5, 2001

Hi,

I am able to archive the Analysis Services db's using the msmdarch on one server but not the other server. (It is working in the Test environment but not the Production environment.) Here are the details:

Both servers are SQL Server 2000 sp1.
I compared sp_configure and both servers have the same settings.
The Production server is clustered and the Test Server is not clustered.

I can archive in TEST using the following:
- From Analysis Manager
- Using the msmdarch command from a command prompt
- From a scheduled job using the "Operating System Command" Type from a job step entering the msmdarch command in the Command box.
- Using the msmdarch command with xp_cmdshell within Query Analyzer and from a Stored Procedure.

I can archive in PRODUCTION only using:
- From Analysis Manager
- Using the msmdarch command from a command prompt
** It does not work from a job or from Query Analyzer

I have checked the permissions of the SQL Server Agent account on both servers and they are identical with sa rights and a member of the OLAP Administrators group.

Does anyone have any thoughts on what could be causing this?

Thank You

Michael

View 4 Replies View Related

SQL Server 2008 :: Batch Archiving With OUTPUT

Oct 13, 2015

I have to set up archiving of some pretty large tables (125M+ rows) and I'm trying to figure out the best way to do it.

I've been using OUTPUT into, something like this:

delete top (100000) a
output deleted.RecordID
into dbo.RecordsArchive (RecordID)
from dbo.Records

It's difficult to tell if it sub-optimal code or just sheer volume, but any performance penalty using OUTPUT into in this way? or is it advantageous to insert the records to the archive first, and then delete them in two separate operations?

View 0 Replies View Related

Newbie - Sql Server 2000 Transaction Log Size Limit

Jan 12, 2004

Has anybody encountered a physical size limit for a sql server 2000 transaction log running on win2k?

Transaction log reached ~6Gb before rolling back the delete stating transaction log was full. There was 42Gb free on the server and the log was set to unlimited growth.

View 3 Replies View Related

Control Transaction In Sybase Database Using Sql-server 2000

Apr 20, 2004

Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction


Create procedure myCurrentDataBaseProcedure
as
begin

begin tran
insert into mytable values(1)
if @@error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @@error <>0
begin
rollback transaction
return
end
commit transaction
end

The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.

But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions

Pl.Help

View 5 Replies View Related







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