Has anyone come across a change tracking tool for SQL Server. Specifically
the scenario I want is the following :
A Production DB needs some modifications to its content.
This tool will copy the DB to a dev environment.
The Dev environment will be 2 copies of the DB, 1 as a control set and the
other the change DB.
The developer makes their changes in the Change DB. They test them out and
then when they decide the changes are ready to pushto production they hit
the "go" button on the tool.
The tool calculates the delta between the Control and the Change DB and then
pushes the changes to produciton
best way to track changes for a very pesky table.We pull down a table from an Oracle database (via linked server) into our SQL Server on a daily basis. The data in this table is just truncated and reloaded daily. There is no "history" kept in the oracle database and values are not being "updated" its just dropped and repopulated.
So, I have toyed around with CDC and even creating my own custom auditing method but I can't come up with a reasonable solution that doesn't involve a massive audit table that doesn't provide much useful info.
For example, there are two date fields in the table that my customer wishes to have history tracked for. Every day, this table is truncated and reloaded - however those dates may stay the same for many many many months. If I turn on CDC, I will get tons of audit records for a delete and an insert every day but the values for the two date fields may not have even changed.
The table has tons of fields in it but I only care about the 2 date fields for history purposes.Here is a snippet of the table (I took out all the additional fields in the table and just left the two date fields that need to be tracked):
CREATE TABLE [dbo].[Fake_Name]( [lin] [char](6) NOT NULL, [boip_no] [char](6) NOT NULL, [dt_tc] [varchar](25) NULL,
[Code] .....
method to track changes to this table with it being truncated every day?
This one is giving me quite a bit more difficulty then I ever imagined it would...
Essentially I would like to use one table to store the change history for multiple tables. I would like to use an update trigger to check which fields have changed in each record, and write a single record for each field that changed containing the table name, field name, previous value and new value to a history table.
How to reset the value of the change tracking anchor id?
CHANGE_TRACKING_CURRENT_VERSION()
It's a bigint that increments on every DML operation and I just wondered how you could reset it back to zero. Turning change tracking off and on doesn't seem to do it.
Is there a table where last DDL change date exists? For instance, if I have added a column to a table is there anywhere I can find when this occurred? Auditors are askng about this.
Does anyone know of software that tracks changes to a database? Forexample, it would track anytime an SP or view was recompiled, or ifyou added or deleted a column to a database?
We are developing an application that requires change tracking.We tested it in development and test environments and we are preparing our production deployment.The very first thing that needs to be done is an
ALTER DATABASE [db_name] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
We are holding on this first step because this statement alone executed for a good 4min on the development server. The production environment is many times larger and busier, and we can’t afford service disruption, so we are at the point where we need to understand what’s involved in running this ALTER DATABASE statement.Is there any documentation on what is happening behind the scene when this statement executes such that we can assess the risks of running it in production?
How can i enable my fulltex change-tracking and update-index in my table? I recreated my fulltext catalog and start the full population, but although my fulltext index status shows active, my full-text change-tracking and the update index were disabled. - and I don't know how to enable them. Thanks in advance
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
Can anybody let me know if there are ways to programatically track changes made to a SQL SERVER CE table? I am writing a db monitoring tool on SQL server CE which should track any changes made to the table.(Insert update and delete)
We could have done this using triggers on Sql Server 2005. Since triggers are not supported on SQL Server CE, are there any alternate ways to achieve this functionality?
I am running both SQL Server 2000 and SQL Server 2005.
Lets say I have a live database that is version 1 for an application. The database is copied onto a development machine to develop version 2 of the application. I want to be able to take a snapshot of the database and then get a program to write all the changes made after the snapshot into sql code. This is so I can run this sql code on the live database and transform version 1 to version 2.
Is there any program/tool that will allow me to do this?
I was checking the logs because of some failing backups and found literally hundreds of enties for failed logins for user sa. This is does not seem to be related to the failed backups, because even after fixing the problem (disk space), the entries continue to pile up.
Needless to say, the information provided in the log is a bit sparse: date and time, source (Login), message (Login failed for user 'sa'.). If that is actually enough to go on, then there must be some way of cross-referencing this information with that found somewhere else. Is there a 'somewhere else' or are there other things I need to do in order to track this down?
I don't suspect any malicious intent, but I'm not ruling it out. My main concern at this point is twofold: to gain a better understanding of system management; to identify and correct the failed login.
Any recommendations, whether tools, documentation, or otherwise are very much appreciated.
How do you track changes to objects in SQL Server. For example changes in stored procedure,views and indexes. What system table or column track or indicate changes in text of sp or views. Help appreciated.
We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?
OK. For DDL, please refer to the classical Northwind ORDERS table,problem/challenge, find the longest duration (start_date andend_date), during which, no orders were placed.FYI, column names by the order of colid per syscolumns:OrderIDCustomerIDEmployeeIDOrderDateRequiredDateShippedDateShipViaFreightShipNameShipAddressShipCityShipRegionShipPostalCodeShipCountryAny idea/approach? TIA.
Hi I'm trying to get CDC going, it works however when i query the LSN using the functions i get no changes. The min and max LSN returns null. SQL agent is running, db is on full recovery model etc.
Yesterday I had problem with that a sp that contained join that the developer had forgotten the where part. The sp join very large tables and sp took more 95% dual itanium processor. How do fastest track this down when it accrues. Find the sp or sql that running and consume this much processor resources. I tried with a lot. Sp_who, sp_who2, activity monitor.
im trying to create a procedure that will insert/update a small table that has an ID, AmtBefore & AmtAfter.
this is just a table that i'd be using to monitor other activity on the database.
i'm really pulling a price + ID every hour and i need to track that somehow and know when the procedure runs if a price is lower than it was the last hour, plus of course keep the ID's straight. does anyone have a good approach to something like this?
We are building an inventory management system complete with BOM. It is important to track what employee is updating what tables. Currently all such tables have a Date field that is updated when a change is made, and an EmployeeID field which records the employee making the change. I am wondering if someone knows of a better way to track this information. Any suggestions?
I have several transaction tables on which I need to track the changes. That is I need to maitain the history of changes. Only few column values are changed often.
Which is the best way for tracking the changes.
1.Store the whole record after the change ?
Or
2.Store the ColumnName & its respective old & new value ?
Or any other better.
Note : UI part & SP's will take care of the tracking & no plans for triggers.
I have a table in my database and it holds some important information,I want to track the users who are executing DML commands(select,update,delete) on the data in that table. Is there any way do that?If so how could i implement that.can any one help me in this regard? Thanks in advance...
First thank you for all your help thus far. Now I'm stuck again. I've been doing a lot of reading on triggers and logging information into tables but I've been trying to capture how many times someone enters an item into the search box.
So every time somebody types Gumballs into the search box I want to capture it and the name of the person who is currently logged in. Is there away to do this? Maybe this is something that I should be checking in ASP.NET forums?
Hello everyone, I have a fairly unique need :) I am trying todetermine the use/clients for databases in my corporation that I ammaintaining, but that noone seems to know what they are for. Many ofthese databases never seem to have anybody connected to them in thecurrent activity.What I'd like to do is find a way to audit the logins, so everytimesomeone connects to a database it simply logs the clients IP address,what login they used, and maybe what time. I've been searching googlefor this and have found tons of information on auditing the logins,but not the clients, such as by ip. Any help in this regard would beGREATLY appriciated!Joshua
I have a device application that simply needs to upload data to a server. The preferred DB server is Oracle but I've made it work using RDA and SQL Server. The problem I'm having is that it just needs to upload data, whichh I send using the RDA.Push() method. The data arrives just fine, the first time. With every subsequent upload all of the previous data is deleted fromt he server. Apparently RDA is tracking the deletion of the previously uploaded data locally and on the next .Push deleting that data from the server.
My question is: Is it possible to prevent RDA from deleting data on SQL Server? I attempted to delete the rows from the __sysDeletedRows/__sysRowTrack tables but got a "Data is read only" error.
I hoipe someone can point me in the right direction here.
I have an application with the following requirements (using SQL CE 2 alas)
A set of tables on the server that need to be imported to the handheld. Using rda, I need to get the modifications to these tables from the server (add/edit/delete) but the handheld will never update these tables.
A set of tables on the server that need to be imported to the handheld. The handheld needs to add/edit existing records, and it needs to get any changes from the server.
A set of tables on the server where the handheld needs to import a subset of the records. It needs to add (but not edit) new records, upload the new records to the server, and download any changed (add/edit/delete) records to the handheld. What tracking options should I use in these 3 cases?
The problem comes in that I need to have some foreign key relationships in the database on the handheld. Since rda munges the names of primary keys (and indexes), I do not know of a good way to add these foreign key constraints. Any suggestions?
