| A community of more than 1,600,000 database professionals and growing |
| | Can Auditing Fail? This editorial was originally published on May 5, 2015. It is being republished as Steve is at SQL in the City in Chicago. I saw someone's list of requirements for a SQL Server auditing system recently that included many of the standard items most of us would want. DDL changes should be captured, along with configuration alterations. The performance impact should be low, and all versions of SQL Server must be supported. However, there was one requirement that surprised me. This person noted that the auditing must be asynchronous and the application should not be dependent on the auditing. If auditing failed, the application shouldn't have any of its transactions limited or rolled back. I'm sure there are use cases where this is important, and where the auditing might not be critical, but the auditing data is available for informational purposes to troubleshoot any issues that relate to the database. However in many places where auditors review information, or the data is part of a legal record, the auditing cannot fail. If it does, then the application needs to stop working. If an audit is truly an audit of activity, then all activity must be audited. The C2 audit mode in SQL Server has been deprecated. Probably for multiple reasons, but It did seem that the idea that a failure in auditing could stop the database wasn't a setting many people were willing to implement. To me, this means that auditing isn't as important as having the system continue to process data. If that's the case, then is auditing that important? I know auditing data can be overwhelming. I know that the management of audit data, including archival, is complex. I also know that most of the data isn't very useful and will never be examined. However when we need audit data, we really, really, need accurate audit data. I really wish that Microsoft would integrate auditing better into SQL Server to ensure the data can be easily managed, compressed, and archived in an automated fashion. Steve Jones from SQLServerCentral.comJoin the debate, and respond to today's editorial on the forums |
|
| ADVERTISEMENT | | NEW SQL Provision: Create, protect, & manage SQL Server database copies for compliant DevOps With SQL Provisions virtual cloning technology, databases can be created in seconds using just 1MB of storage, and sensitive data can be anonymized or replaced with realistic data to ensure it is protected as it moves between environments. Download your free trial |
| | The industry standard for comparing and deploying SQL Server database schemas Trusted by 71% of Fortune 100 companies, SQL Compare is the fastest way to compare changes, and create and deploy error-free scripts in minutes. Plus you can easily find and fix errors caused by database differences. Download your free trial |
|
|
|
| | | Steve Jones from SQLServerCentral.com Learn the basics of how Dynamic Data Masking can be used to obfuscate data in SQL Server 2016+. More » |
| Additional Articles from Brent Ozar Unlimited Blog SQL Server 2016 & 2017 enable a new way to get query performance metrics live via Extended Events. More » |
| Find out what Redgate have been doing with their data masking tool. Find out where they are heading next in terms of development and help them gain a deeper understanding of your requirements for the tool. More » |
| James Serra from SQLServerCentral Blogs At Microsoft Ignite, one of the announcements was for Azure SQL Database Hyperscale, which was made available in public preview October 1st,... More » |
| Grant Fritchey from SQLServerCentral Blogs I was recently asked if we could tell why a plan was removed from cache. If you read this blog,... More » |
|
|
| | Today's Question (by Steve Jones): In one of my SQL Server 2017 instances, I have a database that contains multiple filegroups, each with multiple files. One of these filegroups is the Logger filegroup, which contains a table that grows often with auditing information. I have spent some time trying get ensure all the files are roughly the same percentage full of data. My goal is to alter the database so that when there is a need for a file to grow to accomodate new information, all files in this filegroup grow. What option do I add to the ALTER DATABASE MODIFY FILEGROUP syntax? |
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: Administration. 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 Steve Jones): Beginning in SQL Server 2017, we can enable strict CLR security to better protect our systems. If this is enabled, which levels of assemblies are treated asable to call unmanaged code or access external resources? Answer: All assemblies are treated as UNSAFE, regardless of the PERMISSION_SET information used Explanation: The Strict CLR setting will cause all PERMISSION_SET information to be ignored. All assemblies are treated as UNSAFE. Ref:CLR Strict Security - click here » Discuss this question and answer on the forums |
|
|
| Database Pros Who Need Your Help |
| Here's a few of the new posts today on the forums. To see more, visit the forums. Job just stopped working - I have a job that does backups and out of the blue just stopped working. I deleted the job and... How to build an app with GUI that works with SQL Server? - I have to create a project which should have a GUI because people who gonna use it don't know TSQL.... Move old DBs to new existing servers. - I have bunch of DBs I am trying to migrate from old DB serves (2005, 2008, 2008R2) to 2016. We... Database in recovery mode not comming online - I checked database is IN Recovery. Error log Long Sync IO: Scheduler 11 had 1 Sync IOs in nonpreemptive mode longer than... seperate data in 1 row - Hello HOw can I separate this data I have buch of emails in a row, I need to keep just one and... Encrypted Password - Hi Guru's, I have weird situation while passing username and password in BCP command. Can any one let me know how... increment months for two dates combination as a new label column - I have to calculate a new Label column for each combination of Start Date and End Date. If start Date... Deleting data from a table ... quickly - I'm working on a system which stores what you might call "temporary working data" in a number of database tables.... Identifying the 3rd qualifying record - I have a scenario where the client wants to identify the 3rd successful attempt at contacting a customer within 5... Importing from XML to SQL 2014 - This is my 4th day on SQL so I am a newbie. I created a query to import from an... SSIS package assistance, importing files from folder - I'm trying to create a package to import SQL Audit files to a table on my server, to make searching... string to DATE failing when appending to a table. - Hi All, I ran and import from a flat file with a string date format 'dd/mm/yy', and converted to 'Date' type... Item category details - I have a below tables and want to get the result as per updated price of items in a query. CREATE... Help with ETL - I am dealing with a situation to where I need to bring payment (check), invoice, and credit association information from... EXCEPT operator problem - I'm trying to update a set of rows and exclude several, but my EXCEPT operator is erroring: UPDATE a SET a.Address1... NOT IN vs NOT EXISTS - A little while a go I was told that NOT EXISTS will "never perform better" than NOT IN, and that... CASE to table conversion - This may be a fool's errand, but I have to try... I have a CASE in my code that checks... I need to change the value of two fields as I insert the rows into a table - So I have the source table TdatasegTest I have the target table TdataSeg If I needed a simple insert I would use this... Split comma delimited field? - Hi I have a field that is comma dellitted (text1,text2,text3) Id like to split the field into separate columns, but not separate... Help with SQL Queries - |
|
| 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 |
|
|