| A community of more than 1,600,000 database professionals and growing |
| | | Audit systems: a good idea or a mess to maintain? Today we have a guest editorial as Steve is out of the office. I was recently involved with a design of a configuration system. Pretty early on in the design we realized it would be a good idea to have an audit system. The system would need to keep track of any changes that were made to the configuration tables and allow for the ability to back out to any of the previous changes. It also keeps track of who changed what and when they changed it. This new configuration application has its own schema. All database access is done through stored procedures. There is no inline SQL code in the application. The audit tables match the actual tables except for a few extra audit columns. In the stored procs that do the insert, update or delete, there are audit procedures that are called before the action is done and audit data that is saved to the audit tables. The configuration application has an admin screen where you can view the audit data and if you have elevated rights you can click a revert button that will restore one of the previous configurations that is selected. This allows the users of the system to maintain it without IT intervention. No more late night calls asking for some configuration to be restored because it was changed by mistake. There are a number of other ways that one can audit a system in a production environment. Some audit systems keep track of changes that have been made, but don’t really help you back out those changes. I have seen some audit systems that are trigger based, but they tend not to have a lot of visibility to the end user. I am sure there are a number of other options when it comes to audit systems. I know SQL Server 2017 has a server audit and a database audit. You can see some details here: click here I have never tried it, but it is another option to consider. If audit systems are beneficial, why don’t we use them all over the place, in every application? Well, there can be a lot of work that needs to be done anytime a change needs to be made. Something as simple as adding a column to a table now has to be done in at least two places. So, the work is effectively doubled. Also, if inline SQL is allowed, it can quickly become impossible to keep an audit system working without a lot of extra work to ensure the inline SQL isn’t breaking the audit or vice versa. What is your experience with an audit system? Have you found a certain audit pattern to work better than others? Share your experience and let us know if your audit system was a good idea or a mess to maintain? Ben Kubicek from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
|
| | ADVERTISEMENT | | | | Sign up for more free training from Redgate Redgate has committed to hosting a free virtual event in every quarter of 2018, and will be kicking this off on February 28 with a livestream themed around data privacy and protection. The agenda has now been released, so you can see who will be presenting, what they will be presenting, and how you can tune in to watch. Find out more about the sessions and register your place |
| | | | NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps Create and manage database copies effortless and keeps compliance central to the process. With SQL Provisions virtual cloning technology, databases can be created in seconds using just MB of storage, enabling business to move faster. Sensitive data can be anonymized or replaced with realistic data to ensure data is protected as it moves between environments. Download your free trial |
|
|
|
| | | | | Daniel Calbimonte from SQLServerCentral.com In this article, we will show how to create an ASDW in Bash using the Cloud Shell. More » |
| | Additional Articles from SimpleTalk Database administrators have enormous responsibility whether they manage one or hundreds of servers. Monica Rathbun tells us how she survived as the Lone DBA for 56 database servers for over a decade. While many DBAs work on teams instead of alone, she has great advice for all. More » |
| | Snippets are a great feature of SQL Prompt - they save coding time, and introduce standards and consistency to the way you build code modules. But did you know you can customize them? Phil Factor shows how by using ALTER TABLE ADD as an example. More » |
| | Devin Knight from SQLServerCentral Blogs In this module you will learn how to use the Brick Chart by MAQ Software. The Brick Chart provides a... More » |
| | Arun Sirpal from SQLServerCentral Blogs Nobody wants to waste money and being in the cloud is no exception! Luckily for us Azure is very efficient... More » |
|
|
| | | Today's Question (by Steve Jones): I run this code on SQL Server 2016: SET CONTEXT_INFO 55 I then want to get that data later in my application. I run this: SELECT CONTEXT_INFO() I don't get 55 back. Why not? |
Think you know the answer? Click here, and find out if you are right. We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: context_info(). We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center. |
|
|
| |
| ADVERTISEMENT | Design and configure SQL Server instances and databases in support of high-throughput applications that are mission-critical and provide consistent response times in the face of variations in user numbers and query volumes. Learn to configure SQL Server and design your databases to support a given instance and workload. Pick up your copy of this great book today at Amazon today. |
|
|
|
|
|
| Yesterday's Question of the Day |
| Yesterday's Question (by Evgeny Garaev): How would you optimize a query against JSON data query on a SQL Server 2016 instance?
Answer: Create a standard index Explanation: In SQL Server and SQL Database, JSON is not a built-in data type, and SQL Server does not have custom JSON indexes. You can optimize your queries over JSON documents stored as strings, however, by using standard indexes. Ref: click here » Discuss this question and answer on the forums |
|
|
| | | | MyDoggieJessie from SQLServerCentral.com Why did I write it? I wrote this years ago as a "means to an end" when trying to determine why our daily reporting process appeared stuck. After checking the built-in replication monitor of your transact replication solution and only seeing it was "30 seconds of latency" or 800,000 undistributed commands behind, I decided I wanted to be able to more quickly find out "which tables" were actually behind, and automate an email that would get sent stating such. This script provides this information, as needed, and "when" needed.
What does it do? This script compares the row counts between a specificed set of tables between the Publisher to those of the Subscriber. The built-in replication monitor, shows "number of commands in the distribution database waiting to be applied at the subscriber" This script shows which tables are behind and how many rows it needs to catch up on.
How can you use it? For cases where real-time data is important, I've employed it's use in 2 scenarios (where our SLA of 3 minutes latency is deemed acceptable). SQL Agent Jobs: In situations where a scheduled job needs to run but requires that data be current, you can add this procedure prior to the code that gets executed (or as a prior job step) and it will keep checking to ensure the record counts match before moving on. Stored-procedures: Within other procedures, where data must be current, you can add this procedure prior to the code that gets executed to ensure the record counts match before moving on Example usage: EXEC MyDatabase.dbo.dba_CheckReplicatedTableCounts @Publisher = 'Publisher', @Subscriber = 'Subscriber', @DB = 'MyDatabase', @Tables = 'Table1,Table2,Table3,Table4,Table5,Etc', @Threshold = 15, @RunningFrom = 'DAILY - RUN INVOICE REPORTS', @WaitTime = '00:00:03', @EmailAfterLoops = 20, @SuppressMsg = 0 An example of the email notification generated: What it does NOT do: It will NOT ensure that your data is accurately updated It will NOT check for any in-flight data manipulations It will NOT give you a raise Known Dependencies dbo.fx_FormatArrayText() - this is a sinmple scalar function that will take a comma delimited string and format it for use in dynamic SQL. It has been included in the SQL Scripts for this article If your replication topology involves seperate Publsher/Subscriber/Distrbutor, you will need to create a the appropriate linked servers to those instances Known Issues None at this time More »
|
|
|
| Database Pros Who Need Your Help |
| | Here's a few of the new posts today on the forums. To see more, visit the forums. Need Tutorial on Differential Backup - I need to backup a SQL Server 2016 database weekly - and take incremental backups daily I found this snippet: -- Create a... Transaction Log Growth - Hi, I am currently using Ola Hallengren's backup solution for my backups on a SQL Server 2016 Standard instance. I have... Tracking changes on Views. - Tracking changes in/on Views. Are there any good scripts/documents/advises/tips on Tracking changes on Views? I asume that I am not the first... Is there a row locking similar to Oracle's row locking in SQL SERVER 2016 - I would like to lock a certain row in a table that stores the next available numbers for various types... Update the first and last 2 charactors with year and month - I have a value that I need to change the first characters with the year and the last 2 characters... Consequences of making "dbo", directly, the owner of db_datareader schema - For the fixed database roles such as db_datareader, by default the role and a schema of the same name are... Can we execute sp_updatestats in heavy transaction time - Our database becomes very slow in heavy transaction day after being transaction about 7000 with in 2 hours even i... SQL uses 2 different executions plan (slow and fast) for a simple query. Need a new index ? - Hi everyone, I am experiencing a performance issue with one process in our system. The performance issue is intermittent, the users... Question about a composite key and autoincrement - Hello all, I was recently assigned to convert an Access database to Sql Server. I have to try and keep the... Deriving group members from nested groups - Hello All, Any input would be appreciated. I have some working code that derives group members from nested groups. There are... Looking to sort two queries by Date, Time - SELECT LTRIM(RIGHT(CONVERT(varchar(6), .,100),7)) AS --, FORMAT(., 'MM-dd-yyyy') AS , CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), .,101),'/','-')) AS , LTRIM(RIGHT(CONVERT(varchar(20), Run an FTP package from stored Procedure - Hi, I am trying to run a simple FTP package from stored Procedure. So if I run the package in SSIS it... SQL Server 2012, update Stats and Index Organize taking 16 hours for execution?? - Hi, We have SQL Server 2012, On production database SQL job update Stats and Index Organize taking 16 hours for execution. Size... SQL Server (MSSQLSERVER) keeps Stopping - Hello, Trying to connect to sql server database engine, via ssms, but won't connect. I get the standard error: A network-related or... How to handle very large dataset - I need to find the most recent post date for all the invoices in my table. There are millions of... Reporting Service don´t export to excel - Hi, Good Moring, Someone could help me about this? I have a report via Reporting Service, In the user`s pcs,... Raspberry PI as test box - Woke up and sprained my brain this morning. That's the only reason I can think of as to why this... Weird variables / expressions issue - We have an SSIS package that is totally hosed (got corrupted somehow) so I'm trying to rebuild it from scratch.... Error: Could not apply log backup file - hi i have a sql 2005 db with log shipping enabled. the secondary server is suddenly giving me the below errors, anyone... Anything out there better than SSRS - Hi All Beginning to look around for other BI tools. Don't have a vast amount of knowledge in this area, but... |
|
| This email has been sent to newsletter@newslettercollector.com. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com. | This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here. | This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com |
|
|
