Moving Data For Audit - Best Practice

Oct 17, 2005

Here is the situation:

Tables:
Account
AccountDocument
AccountTest


Then we have the following "Activity" table refencing "Account" table
above:
Table: Activity
Columns: ActivityID, AccountNo,...

when "Activity" with accountno is created, we'd like to able to take
snapshop or copy all Account related data records from tables: account,
accountdocument, accounttest
and store it somewhere.


This way even if account, accountdocument and accounttest tables change
later, then "Activity" accountno integrity in maintained.

Do we need to create or replicate similar tables to store data for
audit?

or Which other ways can this be achieved?

View 3 Replies


ADVERTISEMENT

Row Versioning, Audit, Best Practice

Mar 2, 2007

I have a small webapplication, sql server 2000. The users can only update the data in the system. However my client needs a report that display changes. The changes are that needs to be monitored are only change of order status, change of delivery date and when a user splits an order.

What is the best practice to keep track of changes? A mirror table for each table with changes?

/Magnus

View 3 Replies View Related

Database Audit Specification To Audit Select On Certain User And Table

Nov 1, 2014

I have made a server security audit and specify from database audit specification to audit "select" on a certain user and on a certain table. I logged in by this user and made the select statement..when i run this query

"select * from sys.fn_get_audit_file('d:Auditaudit1*',null,null)"

It return a value at which time the query has done

after 15 minutes i repeated the same action, i run the audit query and the same result is showed off on the panel.is it suppose to return a list of values by how many times this user has made the select statement on that table ? for example at 5:00 pm then 6:00 pm and so on

View 1 Replies View Related

Best Practice - Data Sources And Data Views Vs OLE DB Source

Feb 26, 2008

Hi, i'm wondering which is the best way to search data in a SQL Server.
I reach data using Data Sources and Data Views and also with OLE DB Source with a Data access mode: Named query.
I have to write the data into a Flat File. So, does any one knows which is the best practice for this? Or any one of the two are good choices?
Thanks for your help.

Beli

View 6 Replies View Related

Audit Logon / Audit Logoff Problem With SQL 2K

Jan 18, 2006

I need help...here is the problem.Last weekend, the servers in our datacenter where moved around. After thismove, and maybe coincidental, 1 server is performing very poor. Afterrunning a trace with SQL Profiler, I saw the problem which was laterconfirmed with another tool for SQL server performance monitoring. It seemsthat all connections to the SQL server (between 200 - 400) are doing a login/ logout for each command that they process. For example, the user'sconnection will login, perform a SELECT, and then logout. This is not a..NET application. The client software was not changed, it is still thesame. The vendor has said that it is not supposed to do that, it issupposed to use 1 connection that log's on in the morning and logs off atthe end of the day or whenever the user exits. 1 user may have severalconnections to the database.At times, the server is processing over 250 login / logouts (avgeraged for30 second period). Has anyone seen this problem? I have the server inAUDIT FAILUREs only. The server has become very unresponsive, things thattook 3 seconds now take over 15 seconds.Any ideas???

View 6 Replies View Related

Bad Practice For Data Sources?

Feb 27, 2006

I'm just wondering if it is bad programming practice to have many data sources on one page. I need a "Matrix" type of page that grabs data from many tables and so far I have 5 data sources. Is this common or is there something I should be doing to make it more efficient?

View 1 Replies View Related

Audit Data

Jan 18, 2005

Hi all,
I would just like to ask whats the best way to make some audit on some of the tables in a MS SQL server, what i'm planning to have is to have a table which can contain all changes/inserts/deletion of some given tables, my first idea was to have this:

AuditTable that have the following fields:
AuditID, TableName, FieldName, OldValue, NewValue, UpdateBy, UpdateDate

then in all the given tables, i'll have insert, update and delete trigger, the issue comes down to the trigger, what will be the best way to have that trigger written in a way that it can be use for other tables as well? say if a table have more then 20 fields, I don't want to declare 20 var and compare them 1 by1, and if there is a diff, then i insert to the audittable, I want something that it can loop and (if possible) be able to use by other table as well, so the field name etc can get from sysobjects, but then how can you code it in a way that it can do that?

Or is there any better way to get the same result? currently i have an audit table for each table i want to audit on and its just wasting space, any help will be great.
Thanks,

View 6 Replies View Related

Best Practice To Copy A Lot Of Data Between Databases

Oct 30, 2007

Hi,

I have a database on sqlserver 2000 which contains some tables which receive Create/Read/Update statements from applications, and +-5 tables which contain a sort of read-only data: the applications are only reading from these tables.

Sometimes these 5 tables need to be updated with new data. Currently I am doing this as follows:


Execute a long-running operation (e.g. 1 week), which will add new data, against a Test-database (which is a restore from a backup of the original database)
Some people do some checks to see if the new data in the Test-database is correct
If (2) is OK, the only thing which needs to be done is copying the data of the 5 tables in testdatabase to the 5 tables of the production-database.For (3), I currently use a DTS-package which consists of a "Copy SQL Server Objects"-task. This task is configured to copy the 5 tables (objects) from testdatabase to productiondatabase. The data in these 5 tables is around 20GB, and this task takes a lot of time compared to a backup/restore of the same size of data. I already tried to speed it up by creating different filegroups and wanted to restore only 1 filegroup but you can read here that making a filegroup backup on a testdatabase and trying to restore it on a production database won't work. Is there some other way to speed this up? Is my current way of working good practice?

Thx!

View 1 Replies View Related

Best Practice For Data Dictionary In SQL Server 2005

Sep 1, 2006

Hi ,all here,

Would please anyone here give me any guidance and advices for best practice of data dictionary in SQL Server 2005?

I have restored a large insurance claims database with up to more than 300 tables, massively, most of them are empty tables, many of them dont have any keys, contrains, indexes, and more difficult, there is no any data dictionary for the database which gets me stuck in the understanding of the data at the moment. Thus I think data dictionary is very important for a database.

Will anyone gives me advices for that? Really need help.

Thank you very much in advance for any help.

With best regards,

Yours sincerely,

View 11 Replies View Related

Best Practice/recommendation Dev Data Maint Plans

Feb 9, 2007

We are working on converting to SQL 2005 database. During the conversion we are having to rewrite a lot of code and doing a lot of intital testing and development on development data. This is causing our transaction logs to really big. I have created a maint plan that runs nightly that does a back up of database and tran log but throughtout the day the tran logs are getting really big and eating up a ton of disk space. Does anyone have suggestions on what sort of maint plan I can setup to run on my developement data where as at this point I am not concened about being able to roll back the database just keep is small as possible and "healthly"

All ideas are appreciated

Thanks

Chris

View 5 Replies View Related

Data Base Audit Options

Mar 12, 2008

Hi All,

Is there any option present in SSMS that ll audit all the changes(insert/update/delete) done to data in a database from SSMS( not through any application/front end).

I dont want to place any trace or triggers, is there any in-built option.

View 4 Replies View Related

Data Type In Audit Record

Mar 30, 2007

Hi,I want my application to audit any data changes (update, insert,delete) made by the users. Rather than have an audit table mirroringeach user table, I'd prefer to have a generic structure which can loganything. This is what I've come up with:TABLE: audit_record*audit_record_id (uniqueidentifier, auto-assign, PK) - uniqueidenfiier of the audit recordtable_name (varchar) - name of the table where the action (insert/update/delete) was madepk_value (varchar) - primary key of the changed record. If the PKitself has changed, this will store the old value.user_id (varchar) - user who changed the recorddate (datetime) - date/time at which the change was madeaction (int) - 0, 1 or 2 (insert, update, delete)TABLE: audit_column*audit_record_id (uniqueidentifier, composite PK) - FK tocdb_audit_record table*column_name (varchar, composite PK) - name of the column with changeddatanew_value (text?) - value after the changeSo every column which changes has its new value logged individually inthe audit_column table. However, I'm not sure what data type thenew_value column should have. The obvious answer (to me) is text, asthat can handle any necessary data type with the appropriateconversion (we don't store any binary data). However, this table isgoing to grow to millions of records and I'm not sure what theperformance implications of a text column will be, particularly giventhat the actual data stored in it will almost always be tiny.Any thoughts/recommendations/criticism would be greatly appreciated.ThanksAlex

View 5 Replies View Related

Service Broker In Data Push Scenario:best Practice

May 7, 2007

Hallo I need some hints because I would like to set up my service broker in the proper way.

First: I'm going to setup service Broker between 2 databases on the same sql server instance.

My Goal: I insert the data on table1 in the DB1.On table1 there is a trigger that begin conversation and send the message to the service on the DB2.
On the receiving queue there is an Activation that take the xml message, shred it and save the content on th table 2 on the DB2.
Actually this SP is my main concern because the shred and insert involves also several checks so it could take "a while".

Volume of data: it seems that there are 100-200 daily insert in the table1 on the DB1 and it is possible that some of them arrives at the "same time".

Very quickly:
On the DB1 the trigger:

Notice that I reuse always the same conversation and the send queue as RETENTION = OFF


begin transaction;

begin

set @dialog_handle = (select conversation_handle from sys.conversation_endpoints where far_service='ReceiveService');



if @dialog_handle is null

BEGIN

BEGIN DIALOG CONVERSATION @dialog_handle

FROM SERVICE [SendService]

TO SERVICE 'ReceiveService'

ON CONTRACT [MainContract]

WITH ENCRYPTION = OFF;

END

SEND ON CONVERSATION @dialog_handle

MESSAGE TYPE Message ('uyiuy')

commit;

END


On the DB2
ReceiveQueue is defined as:

CREATE QUEUE [dbo].[ReceiveQueue] WITH STATUS = ON , RETENTION = OFF , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[OnReceivedTrade] , MAX_QUEUE_READERS = 100 , EXECUTE AS N'dbo' ) ON [PRIMARY]



But most important is the Activation's SP and this is the main code:

while (1 = 1)

begin

begin transaction

-- Receive the next available message from the queue

WAITFOR (

RECEIVE top(1)
@message_type=message_type_id,

@message_body=message_body,

@dialog = conversation_handle
FROM ReceivedQueue

)
if (@@ROWCOUNT = 0)

BEGIN

Rollback Transaction

BREAK

END



SET @ErrorSave = @@ERROR ;

IF (@ErrorSave <> 0)

BEGIN

ROLLBACK TRANSACTION ;

SET @ErrorDesc = N'An error has occurred.' ;

END CONVERSATION @dialog

WITH ERROR = @ErrorSave DESCRIPTION = @ErrorDesc ;

INSERT INTO [dbo].[tblLog] VALUES(@ErrorDesc,NULL)

END

ELSE



BEGIN

'this is the SP that does most of the work

exec [dbo].[sp_ShredXMLMessageToRelationalData] @message_body

END

commit transaction



I have to say that everything works fine but I don't think that it is completly performant.
I read several blogs/forums and now I'm confused on the best way to implement service broker messaging:
These are my questions:

1) I read that is the DB's are in the same instance It can improve performances the send the message directly on the Queue2.
Can be useful in my case? How to implement it?
2)In my case it is necessarly that I send a message back to the initiator?
3) In my case the conversation never end. I don't think that this is correct but in case of the data push who has to END the conversation? The target? the initiator? never end to avoid overhead (I read that big overhead is caused by the BEGIN and END DIALOG)?

3) where (initiator or Target) and how to handle the service broker error? In my case my applications are SPs and I need to inform the developer or DBA that something went wrong during the processing ( conversation or shredding stored procedure).

4) In my case is should be a good idea to think about how to solve the possibility of the "poison messages"



for sure I will find out other questions...
Any hint, link is appreciated!

Thank you very much!

Marina B.

View 17 Replies View Related

What Is The Best Practice To Change A Column's Data Type In A Very Big Table?

Dec 11, 2007



Hi All,

I am using SQL Server 2000 Enterprise Edition fully patched. Database is in Simple Recovery mode.

I need to change a column's data type from "int(4)" to "smallint(2)". I know for sure that there will be no data (precision) lost, because I know the possible values that this column could have.

My problem is that the table I am dealing with has 600,000,000 records in it. I dropped all indexes before I tried to alter the table. But still it is taking forever and filling up my 280GB disk with transaction log file.

I know that in Oracle, if I want I can turn off logging and do these kind of modifications relatively faster.

I was wondering if there is a way of disabling logging before running this alter command.

What is the best practice to handle a situation of this sort?

I appreciate your help.

Thanks in advance,
Sinan Topuz

View 3 Replies View Related

Converting Audit Data To Create History

Apr 9, 2008

Hi

I have a table called my_history that has columns like this


column_name , old_value, new_value, key, date
bankaccount 30 50 1 01-Apr-2008
bankbalance 10 14 2 04-Apr-2008


and so on............

The history table is populated using triggers

The main table called my has a structure like


bankaccount bankbalance bankname name address key
50 50 xyz abc ford 1
30 14 abc xyz east 2


Now using this information can i reconstruct the records in my table before the update happened ?

I am finding it very difficult to do this is there a way to do this in t-sql ?

The problem is my_history table where the column_name keeps on varrying

regards
Hrishy

View 7 Replies View Related

What Is The Best Practice To Handle Cobol Redeines In Flat File Data.

Mar 1, 2007



Here is my idea but I am looking for the best practice.

Each record can have 3 possibilites.

I would read and write the data 3 times to different tables and add an identity key on all 3 files then I would reassembe the data back together on the identity key and map the data to a fourth and final table.

Any other ideas?



Thanks,

Larry

View 2 Replies View Related

Transact SQL :: DB Audit File That Gets Populated With Data As Activity On DB Goes On

Aug 25, 2015

writing a T_SQL query for the following scenario..I have a SQL DB Audit file that gets populated with data as the activity on DB goes on.I have multiple monthly tables setup that the import should go into these monthly tables based on the event_time value in the SQL DB Audit file.all the data Like event_time '2015-08-25 15:59:39.033' should go to SQL table Audit_tbl_Aug2015 Query for reading SQLDB Audit file

SELECT * FROM sys.fn_get_audit_file ('C:ackupAudit*',default,default)
order by event_time desc
GO
--DML for Audit table CREATE TABLE [dbo].[Audit_tbl_Aug2015]( [id] [bigint] IDENTITY(1,1) NOT NULL, [event_time] [datetime2](7) NOT NULL, [sequence_number] [int] NULL, [action_id] [varchar](4) NULL, [succeeded] [bit] NOT NULL, [permission_bitmask] [bigint] NOT NULL, [is_column_permission] [bit] NOT NULL, [session_id] [smallint] NOT NULL, [server_principal_id] [int] NULL, [database_principal_id] [int]

[code]...

View 2 Replies View Related

Reporting Services :: SSRS Report Data Logging Process / Audit Trail

Jun 30, 2015

1. Does SSRS is J-Sox Compliant, an application must have audit trail feature. For Reporting printing, it should facilitate reports' data logging process. 

2. Information like WHO and WHEN printed the report and WHAT data was viewed?

View 5 Replies View Related

Best Practice To Pull Data From Sql Server 2000 To Sql Server 2005 With Dynamic Queries

May 3, 2007

Hi There,

I need to pull data using input from one table in sql server 2005. I have to query against the sql server 2000 database and pull data into sql server 2005. I have a list of ids that I have to pass to a query to get the desired data. What is the best practice for this. Can I use SSIS or do I need to build an app in C#? Can somebody please reply back?

Thanks a lot!!

View 4 Replies View Related

Moving Data

Oct 23, 2007

 I have 2 databases on one server that I want to consolidate into one database. I'm just learning SQL Server 2005. What is the easiest way to move my 3 tables from one database to a new one on the same server? Do I have use SSIS to do it, or can a simple query be written? I'm new so please be a little detailed in your answer. Thanks in advance for any comments. 

View 2 Replies View Related

Moving Data Between 6.5 And 7

Aug 3, 2000

I have a scenario where I need to refresh a database that is in 7.0 (converted from 6.5 database) from the original database. Is there an easy way to do this. I have tried creating a DTS package but the data never seems to make it accross.

View 1 Replies View Related

Moving Data From 6.5 To 7.0

Feb 16, 1999

Hello,

I'm having problems using the update wizard to move data from 6.5 sql server
(on another machine) to a 7.0 server sitting out a PDC. The wizard dies (and
passes me over to Dr Watson) when login fails for the 6.5 machine.

I am sure I have the right pasword (I've tried variations as well) and have updated the
hosts file so that the machine is known by it's name (I think one of the FAQ answers
suggested that)....

What other possibilities are there for moving the data ? I looked at bcp, but that seems
a rather long winded route (the data contains time stamps so I suppose there will be
a problem reading them in on the 7.0 side) ...

Many Thanks,

Paul.

View 1 Replies View Related

Moving Data From One DB To Another

Sep 4, 1998

Hi all!
What is the best way to move selected data from one database to another using SQL 6.5?

View 1 Replies View Related

Moving Data

Sep 6, 2001

what would be the best way to move 59 million rows from one table to another. The table has no constraint, but has has three indexes. The table has only four columns. It will be going from SQL 2000 to SQL 2000.

Thank You

View 2 Replies View Related

Moving Data From One Database To Another

Dec 5, 2006

Hi all,
I want to transfer all the data from one of my Ms Access tables to SQL Server table, using C#.
How can i do it?
Thanks in advance,
J.Jasmeeta.

View 6 Replies View Related

Moving Data From One Database To Another

Feb 2, 2007

Hi all,
                        I have an Ms Access table and a MsSql table. I am running a windows service in my localhost where the data from Ms access table will be copied to Ms sql table for every one minute. Before copying the data, the Ms sql table will be flushed inorder to avoid replicates.
                       Now i want to copy only the latest records updated within 1 min in Ms access table, to Ms sql table.
My Ms access table
Name        Id
jas            100
meena       101
viji             102
 
My Ms sql table
Name        Id
jas            100
meena       101
viji             102
 
After 1 min, say  2 records are added to my Ms access table like,
Name        Id
jas            100
meena       101
viji             102
bhuvana     103
pinky         104
Now i want to insert only the latest records from Ms access to Ms sql like,
Name        Id
jas            100
meena       101
viji             102
bhuvana     103
pinky         104
how to do this? thanx in advance.
Jasmeeta. 

View 4 Replies View Related

Moving Data Across Networks

Oct 10, 2007

I am wondering the best way to go about a task I have been assigned.  We have two similar websites but each is located on a different network.  One network is secure so it cannot be accessed on the normal WWW.  The secure network will contain the master database.  I need to write a program or do something with SQL server to retrieve all records from the WWW site and get them onto the secure database.  I also in the future will need to update records from the WWW site if they have been updated.  What is the easiest way to move data from one network to the other when I cannot connect to both databases simultaneously?
 Thanks,
Matt

View 5 Replies View Related

Moving Data Between Databases

Jan 5, 2006

I have a web app that has been regulated to a disconnected PC. It's runing IIS and 2.0 with sql server express, but no connectivity. I have changes that are made to some of the data in the db (data, bot schema). There is one particular table that I cannot overwrite, and must extract the data.
What methods are available to do this swap of data between databases? I was thinking of doing something like this:
Track last date that remote db was updated. Upload updated database into data directory, loop through records for all affected tables, any date that was past logged date then update the record if it exists or insert new record, and then loop through the remote db and delete this records that dont exist in the updated db.
This seems intensive and slow - especially as the tables get bigger but I can't think of another solution that can be done by a user using sometime of web interface.

View 1 Replies View Related

Moving Data From One Sql Server To Another

Jul 5, 2002

Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:

1what should be the ideal spec of a sql server to allow for windows 2000 in future. And how do I go about moving data from one to the other. I am new to sql and this is my first assignment.

Thanks

Eve

View 1 Replies View Related

Moving Data From One Sql Server To Another

Jul 5, 2002

Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:

1. What should be the ideal spec of a sql server to allow for windows 2000 in future.

2. Is there any kind of monitoring agent available to see how heavily loaded the server is and what load each application/database is creating.

3. And how do I go about moving data from one to the other.

4. What do I do at the clients end to ensure they are able to access their database.

I am new to sql and this is my first assignment.



Thanks

Eve

View 1 Replies View Related

MOVING DATA AND LOG FILE

May 25, 2001

I am trying to move a data and log backup file from my C drive to another drive to free my C drive. Does anybody know the easiest way to do it? Thanks.

View 6 Replies View Related

Moving Data From One Table To Another

May 27, 1999

Hi all,

This is (probably) a simple question, but I'm new to SQL Server scripting.

What I want be able to do is move data from one table in a database to another table in the same database, once one of the fields (a date field) reaches a certain value.

Specifically, we are inserting rows into a table that are stamped with an insert date and an expiry date. When the expiry date is reached, we want to move the applicable row from the original table to an identically structured table.

Ideally, we want this to be a script that is run daily.

Does anyone have any ideas or examples that we could use.

Any help is much appreciated!



Tim

View 3 Replies View Related

Moving Data Between Tables

Oct 15, 2001

What is the best method to move 1.500.000 rows from one table to another ??

Thanks,

Charles Roberto Boeing Mari

View 1 Replies View Related







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