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?
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
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 »
Nobody wants to waste money and being in the cloud is no exception! Luckily for us Azure is very efficient... More »
Question of the Day
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:
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
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
(by Evgeny Garaev):
How would you optimize a query against JSON data query on a SQL Server 2016 instance?
Answer: Create a standard index
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.
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
Looking to sort two queries by Date, Time
LTRIM(RIGHT(CONVERT(varchar(6), .,100),7)) AS
--, FORMAT(., 'MM-dd-yyyy') AS
, CONVERT(varchar(10),REPLACE(CONVERT(varchar(10), .,101),'/','-')) AS
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.