SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Database Design and Application Architecture
 Shadow Database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

elzaiem
Starting Member

10 Posts

Posted - 04/30/2008 :  01:18:24  Show Profile  Reply with Quote
Hi, I am new in the team . I have just finished designing accounting system. The customer is looking forward to have a shadow database that traces all the transactions, modifications of vouchers done by users. Is there white papers or articles that can direct me for best performance.

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 04/30/2008 :  12:52:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
What do you mean by shadow database? Are you referring to replication? Or an audit trail?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

elzaiem
Starting Member

10 Posts

Posted - 04/30/2008 :  23:44:07  Show Profile  Reply with Quote
I mean audit trail database that I can trace the history of the voucher and its amendments. Also, I need the DB to trace the user history. ...Many thanks.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 05/01/2008 :  08:17:24  Show Profile  Visit spirit1's Homepage  Reply with Quote
so what you're saying is that you need a single database to store all your auditing?
if not you just need triggers on your tables that write changed data into your audit tables in the same database.

could you explain in greater detail exactly what you want to do? because the solution depends on the exacty requirement.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

elzaiem
Starting Member

10 Posts

Posted - 05/01/2008 :  14:26:50  Show Profile  Reply with Quote
Let me explain what I need exactly. I have Sql accounting database. In the database I want to trace the insertion and amendment of the records in tables. Let's take a journal voucher as an example. The voucher contains two tables the main JV table (Master) and the details JV table (slave). When a user inserts a new record in JV another database should record the user Name, Time and the record inserted. Once another user amends the same record later a copy of the new look of the record should be inserted into the (shadow database) , the user and the time the action took Place. Eventually, I will be having a database the is running the accounting system and another database (I called it shadow database) that registers all the transactions on the vouchers. Also I want to be able to trace the behavior of any particular user on the database.
Many Thanks again
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 05/01/2008 :  14:28:16  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can use triggers to achieve this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 05/02/2008 :  04:19:45  Show Profile  Reply with Quote
This is pretty basic data warehouse theory, and depending on the size of your accounting system I would probably recomend using a third party ETL-tool (extract, transform, load) like Informatica PowerMart or Business Objects Data Integrator. But if the system isn't too big triggers could also do the trick but it could get messy after a while. In my opinion triggers should be avoided unless you use them for keeping a "last-modified"-column up to date or keeping track of DDL-operations (DDL-triggers).

--
Lumbago
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000