Windows 8 has a great design for touch screens, and some great shortcuts for the PC User as well. I've been using it since Consumer Preview and to be honest - it did take a week or so to get used to the new interface. There are one or two tricks, that when you know them, make it a lot easier to work with as a PC User.

To start of with, I am going to talk a bit about charms. Charms is the new "navigation" for Windows 8 - it appears on the right hand side whenever you move your mouse to either the top right, or bottom right corners of the screen.

This will bring up the following:

In order they are:

1. Search
2. Share
3. Windows 8 UI
4. Devices
5. Settings

There is one thing about charms which is a very subtle yet powerful feature... it is context sensitive! Depending which application is active, will depend what charms enables you to do. Some of you might have downloaded the Wikipedia app (for example) from the store; good looking, side scrolling; so how do you search? With the charms menu, just open it up and search whatever you're looking for. For those of you which lives in a suffocated broadband area like me - this is also where you go to limit the amount of data which live tiles uses in the Win8UI (this however is only from RTM onwards).

Another handy feature is Flip-2D - better known as the 3D Aero flip in Windows Vista/7. This is done by moving your mouse to the top left corner of the screen, or pressing WIN + Tab.

It looks similar to this:

When it is fully expanded it will look like this (this one is cropped, at the bottom you will have the link to the Win8UI):

A big question would be why to use this one instead of ALT + Tab? While ALT + Tab cycles through both the desktop apps and your Win8 apps, the WIN + Tab allows you to select an individual application and pin it either side of your screen directly if you do not want to close your active application. Apart from this, the only other difference is that it does not cycle through desktop applications.

The last quick feature is the poweruser menu, which is accessible by right clicking on the Win8UI shortcut on the bottom left of the screen:

This gives you quick access to control panel, command line, etc. which is very useful.

The last most notable feature is Hyper-V which is available in Windows 8 Pro and Enterprise editions, with the limit being an x64 edition with a SLAT capable CPU. This is very handy especially when you build and destroy multiple virtual machines and do not want to install a Server edition on your Computer/Laptop or if you do not have access to a separate virtual environment. This is enabled via the add/program features.

One last shortcut that I find extremely useful when working with SQL Server Data Tools, Management Studio, etc. is CTRL + Shift. When holding these two keys and launching an application from Win8UI, this immediately launches the application in Administrator Mode.

If there is any features you find handy, feel free to leave a comment.

As of the 1st of April ( and no, it is not an April fools joke, I confirmed with HR ) I will be joining Microsoft South Africa as one of the Premier Field Engineers for SQL Server. This will be a very exciting challenge and great position for learning more about SQL Server in its various ways of implementation, and looking forward to work with my new team.

But, moving to this position I'm actually curious to know how many IT Professionals actually know about PFE's and the Premier Agreement for EA customers as a few of my friends down in South Africa wasn't aware of PA's which can contribute to special training called WorkshopPLUS. Feel free to comment if you don't know about this, or if you do have an agreement if you are making use of the various benefits and resources.

With the new SQL Toolbelt, I've also installed the Redgate integration which integrates all the tools for you into Management Studio. On my way to make some changes to my configuration for SQL Prompt, I noticed the Experimental Features... portion which altered my curiosity setting to a full 100%.

This results into the following screen with all its interesting beta programs,features,etc.

After reading through the descriptions, SQL Tab Magic caught my attention the most as I regularly have multiple query tabs open within SSMS. After downloading and installing it, you will see the following toolbar set appear:

This will show you(after the installation) all the closed or open query tabs with a portion of the query. For example:

You can also through the toolbar, set it to look for new tabs and configure it to open any tabs on start up that you had open in a previous session.

There are a few other labs as well which you can find at RedGate Labs all though they don't feature SQL Tab Magic there.

SQL Server 2008 vs. SQL Server 2012

The new release of SQL Server is around the corner, and with it there are numerous enhancements to the various components as well as new features. The auditing component is no different. There are a total of 507 audit actions in SQL Server 2012 compared to the 454 in SQL Server 2008 R2. Some of the new actions are more specifically geared to the new SQL Server 2012 Database Containment Feature.

In addition to this, it also introduces filtering to your audits. This is set up on your audit instead of the Server Audit Specification or Database Audit Specification. This will limited what is written to the audit file even more, however there will be the additional CPU overhead to pay for filtering the audits.

Let's take a look how to set it up through the SSMS:

First of all, create the audit as previously and navigate as in the screenshot. Note that we will use testdb, check for dbo schema and only actions taking place by users which are defined as a DB_Owner:

Next we define a Server Audit Specification to Capture Schema access:
Note. This is necessary to capture the events. Previously a Server Audit Specification captured ALL the events across ALL databases.

To create the same audit using TSQL(which is easier) is as follows:

Below is the results of querying an audit file with the filter:

Other additional functionality is OnError:Fail Operation as well as maximum number of files. Maximum number of files indicates the number of files needed to be kept.

PBM - Last Take On Auditing Your Audits

Continuing from the last post, below is some policies which you can use with SQL Server 2008/R2/2012.

They are as follows:
1. Audit files.
2. Database Audits.
3. Server Audits.

Note. I included a ServerVersion condition if you intend to use this with the Enterprise Policy Framework. This will only execute the policies against SQL Server 2008 upwards.

These policies will take a look at the naming convention for the policies as follows:

Database Audits:

1. Name starts with 'DBAudit_%.
2. Audit file name starts with 'DBAudit_Spec_%'.
3. Audit is enabled.

Server Audits:

1. Name starts with 'SQLAudit_%'.
2. Audit file name starts with 'ServerAudit_%'.
3. Audit is enabled.

Audit Files:

1. Audit file is enabled.
2. Destination type is file.
3. 100 Maximum files.
4. 10 Roll over files
5. File size is maximum 5GB.
6. File size unit is GB.
7. Reserve disk space true.

Below are the create conditions:

Next are the policies:

This concludes the PBM monitoring for this series. the conditions can be changed as per your needs, and the name prefixes are only suggestions that will work well with a central repository.

The Auditing Repository

So you are capturing audits to a remote server, and they are all sitting there and getting backed up occasionally. Current value of the auditing? Zero, NULL.

In this form they're not providing you with any insight. Let's rather pull it into a database. First off we need one:

This gives us our database, you can just move you files to the correct directory as well as add all the secondary files and file groups you need to sustain this. There is also a table "template" with which to create your tables. Each table will be specific to an audit file.

Next we need to get the audit information into the database, below is the script I used and can be modified to fit into either a SQL Job or SSIS package.

This is currently very basic, and I included some comments into the scripts to give you an overview of what is going on.

For one of the audits used in demoing the SQL Server Auditing feature, I pulled all the information into a table. As per the sys.fn_get_audit_file we can return the same results from the table. In the following example I'm using sys.dm_audit_actions to join to the table to make the information more clear:

This is the result:

Currently the database is only hosting the information and it will be nice if Microsoft will add the ability to audit directly to a table in future versions of SQL Server.

Closing the Chapter

The auditing feature very useful, and is significantly more so than C2 auditing due to the level of granularity. This is also one of the drawbacks; if the auditing is not well planned out it can bring your database or even server to its knees. As with all auditing mechanisms this feature can chew it's way through SAN and a lot of thought needs to go into planning this, and if done so, will pay off greatly in the end. With filtering included in SQL Server 2012 it relieves some of this drawbacks in terms of the impact across a SQL Server instance. Overall I think it's a great feature, and it still has the ability to improve greatly.

Below are the links to the rest of the series:

SQL Server Auditing: Introduction
SQL Server Auditing: Creating a Server Specification
SQL Server Auditing: Creating a Database Specification
SQL Server Auditing: Managing Your Audits

Note. Keep an eye on LessThanDot.com to see some cool things happen to the audit repository!!!

Here is the links to the posts covering the topics:

SQL Server Auditing: Introduction
SQL Server Auditing: Creating a Server Specification
SQL Server Auditing: Creating a Database Specification

Now that we have set up our audits covering failed logins on instance level and deletes on database level, let's take a look at how we can see what audits are on a server as well as some views to make your life easier with reading the audit files.

DMV's and Views

First off, let's see what views are available at our disposal:

This returns the following for us:

DMV's

So what does the audit DMV's do? Let's take a look:

sys.dm_audit_actions

This DMV contains friendlier names, as well as all the actions as well as which action groups they belong to across database and server level specifications.

sys.dm_audit_class_type_map

This contains all the audit classes and action groups available.

sys.dm_server_audit_status

This takes a specific look at the audits defined on the server and returns info such as what audits are enabled, statuses for the audits, event times, etc.

Sys.dm_audit_class_type_map and sys.dm_audit_actions are especially useful to join with the other views to get a more easily readable report on the audits, or to join it with the fn_get_audit_file function when reading audit files.

Server Views

The server views are split up into two "sections", one for the audits, and the second for the server specification details.

Audits:
sys.server_audits
sys.server_file_audits

These views contain details about the server level audits defined on the server, This returns information such as audit names, file names and paths, audit id's, statuses, etc.

Specification:
sys.server_audit_specification_details
sys.server_audit_specifications

These views are very helpful when you have multiple audits on a server, and matching the various defined audit actions as well as their groups to audits.

Database Views

The database views available are almost identical to the server level ones, except the fact that it's specific to databases, and that there are no "audit" views; only for database specifications. Another thing to note is, that these views won't return information across databases on an instance, this needs to be executed per database.

sys.database_audit_specification_details
sys.database_audit_specifications

The views return the specification names, enabled audit actions, audit results, etc.

Now that we've got the views covered, a question still lingers... "How do we manage audits across multiple servers?" Well the answer is quite simple, we make use of another new feature introduced in SQL Server 2008 which is Policy Based Management, otherwise known as PBM.

So PBM is a subject on it's own, so I will not go in depth into this topic.

A key resource required for monitoring your environment is the Enterprise Policy Framework.

Below is a test policy I created for the post and will monitor the audits for the following:

1. Maximum File Size
2. On Failure Action
3. Reserve Disk Space
4. Maximum Rollover Files
5. Maximum File Size Unit ( MB, GB or TB )

This is just a basic policy using the "audit" facet. There are additional facets for Database Audit Specifications as well as Server Audit Specifications.

Additionally if you decide to use the Enterprise Policy Framework, it should be noted that you need an additional condition to be set up on the policy that will only evaluate the policy against SQL Server 2008+.

Last Thoughts

These are the views we can use to discover what audits are running across an instance or databases, which actions we are auditing for and a brief policy we can use when we have to manage the audit files specifically to ensure we have a set standard.

Previously we looked at how to create a Server Audit Specification and creating a Database Specification follows the same steps. The main difference is the location, as well as the objects to capture. In the following example we will look at capturing delete statements on a database.

It is also important to note, that you cannot audit across databases, you will need to set up a Database Specification and its corresponding Audit per database.

Creating the Audit

We follow the same steps as previously by creating the Audit file:

Expand your instance >> Security >> Right-Click the audit folder >> Select New Audit

This should bring up a new window similar to this:

Creating the Specification

To Create a Database Specification you can find it under:

Database >> Security >> Database Audit Specification

The first configuration I have for the Database Specification, I am capturing on a database level, only deletes, and only when executed by user belonging to the db_datawriter role.

I executed the following query on my account which is sysadmin:

This resulted in no deletes being captured:

When executed as test_writer, which belongs to the db_datawriter role and I execute the following:

I receive the following results:

One thing to note on the output is under "audit_file_offset". All three values are 6144, which indicates that the three statements where executed in the same query.

As with the Server Audits, use the following query to retrieve the audit information as it is displayed above:

The second example is just to audit a specific table:

Note the areas in the red squares, it changes to audit objects, then a specific table, and lastly to which principle it should be bound and again in this case it will be db_datawriter.

Again, I execute a delete statement:

Inspecting the statement column, we can see that it only captured the delete statement that was executed against tab1:

To create the users, database, and populate it with the data I used - run the following:

Final Thoughts

Just based on auditing for delete statements on database can prove to be very dynamic when using the auditing feature. It doesn't even stop here, as this was only a demo, there are a lot more you can audit like stored procedures, functions, views, etc. This is a powerful feature especially for monitoring critical and highly sensitive databases.

To see what else you can audit with the Database Specifications go here and skip down to Database-Level Audit Action Groups.

As a DBA one of the things to know is when login attempts are made, and which ones is failing as it could be someone unauthorized trying to gain access . This can be easily retrieved from the SQL error log, however we want to keep track of these events over a period of time, there for the error log might not suffice.

In other circumstances, the system can be under certain policies and regulations which requires you to keep the data for a set amount of time, and we don't want other data that is not as critical to be clutter the data we are actually looking to keep.

Creating The Audit

To start off with the auditing, we will first set up an audit file. The audit file contains the target destination, file size and max roll over files (only applicable to using a file target) as well as what actions to take when an event triggers.

Limitations on the auditing file is, that you can only have one audit file per specification. This will leave you with either capturing multiple actions or action groups to one file, or multiple specifications to multiple audit files. You can also not mix a database specification with a server specification to one audit file.

To create an audit file using SSMS do the following:

Expand your instance >> Security >> Right-Click the audit folder >> Select New Audit

This should bring up a new window similar to this:

On Audit Log Failure

Here we can select between the different actions to take based upon what audit action is triggered. We might just want to log all the database option changes, and who made the change, then we will only use CONTINUE or if we don't allow changes, then we can fail the operation. In more secure environments when we receive certain login failures, the audit mechanism can shut down SQL and prevent a possible security breach.

Audit Destination

We can choose between file, application, and the security log. The different options visible is only applicable to the file destination as where the application and security log destinations will make use of the settings applied within Windows Server.

For the file destination there are some handy features like choosing the different sizes and reserving that disk space upfront. In the case of using the audit log due to regulation, it is advised that you keep an eye on the files to ensure that back them up before they are recycled.

After creating the audit, under the same location - select your audit >> right-click and select Enable Audit.

Creating The Specification

So we created our audit, which is now happily running in the background however no audit events are being captured. To capture the events we need to set up either a Server Audit Specification or a Database Audit Specification.

Now we will set up a Server Audit Specification, under the same security folder in SSMS,

Right-click Server Audit Specification >> New Server Audit Specification.

This will bring a window similar to this:

Under Audit can select the audit you have just created. If you have multiple audits all of them will show up, however if you choose one with a specification already assigned, then it will throw an error and warn you.

You can assign multiple audit actions, or audit action groups to one specification however for this exercise we will only use database_change_group to track database changes.After creating the audit the same steps should be followed to enable the specification.

Note: It is the default behaviour of Management Studio to create the specifications as well as the audits in an offline state.

So now that both the audit and the specification is in place, I made a few changes to a database and now we can look into the file using the following code:

Note: All the files will be appended with a guid, there for it is easier to use the name and then a wildcard which is * to make it easier to load the file and to read the code.

The output of my test looks like this:

This is a quick specification to monitor for failed logins using the Server Audit Specification and to demonstrate how to do it. There is more information for the different action groups to use with the server audit specification here.

Then came SQL Server 2008... One of the numerous "enhancements" or features introduced in SQL Server 2008 are the Audit Specifications. The new component is built on top of the extended events feature, and there for provides a lightweight auditing mechanism.

In this series which will continue over the next few days, I will cover some of the basics to set up auditing such as requirements, use cases, and some general audits that will poke their heads out in the environment.

To start the series off, let's look at the components that makes up the auditing feature.

Both the target and server specification can be found under the instance security list, and the database level specification can be found under the security lists for the various individual databases.

The three main components that make up the auditing feature is;

Server Audit Specification

This specification is specifically aimed at events on an instance level which includes login attempts, DBCC commands, backup and restores, and other instance level events.

Database Audit Specification

This specification is aimed at events related to all database level actions, like T-SQL statements, stored procedure executions, object modifications in terms of schema changes, etc.

Targets

The new auditing mechanism supports three targets; file, security log, and application log. This determines where your audit records end up as well as some additional actions as to what SQL Server should do when certain conditions are met i.e. shut down SQL when a failed login attempt occurs.

When using a file target, the ACL should be set up to allow only the required accounts access, the SQL Service account, person or group that will act as the administrators which in both cases will require READ/WRITE to the directory.

The application log is less secure as it can be read by anyone who successfully authenticates on the server. There for it is recommended to use the security log when writing to the event log.

To log the events to the security log, the generate security audits policy needs to be granted to the SQL Server service account. A detailed process can be found here.

In the following posts we will cover creating a basic audit specifications on both server and database levels. We will also look at how to manage the audits, as well as some additional analysis we can do with the information that is captured.

For all of you(like me) who rushed to download and install it, there is a little secret. There are a few feature packs as well available to accompany your new installation and Visual Studio 2010(with SP 1).

All the feature packs can be found here, including the alluding Books On Line which doesn't force you to connect to the internet version.

Happy exploring and testing the new features.

To provide a bit of history on the subject, I was tasked in March to evaluate a server which came under severe strain. After investigating the numerous issues from insufficient memory to slow internal disk space ( raised alerts in SQL log, and monitoring some of the buffer and memory counters in perfmon), I came to the intermediate solution to redo the log allocation.

T-Log Overview:

Before we dive into the virtual log files, let's take a quick recap on what the T-Log is and how it works.

What is a Transaction Log and How Does it Work?

A transaction log is a record of all modifications made to a database, including system stored proc's, DDL statements, begin/end of a transaction, extent allocation/deallocations, etc. It is also the key roleplayer in restoring back ups, point-in-time recovery(FULL recovery model only) and transactional and merge replication. A log record for each modification is made in the log cache, and a LSN(log sequence number) is assigned to the modification.

SQL Server uses a read-ahead log model; this means that any data modifications are not immediately made to the data directly, rather a copy of the data residing in memory. Let's see how it works:

1. SQL will first check the buffer cache if the required data page is there.
2. If the data page is not found, then it will make a copy to the buffer cache.
3. The data modification is applied to data page in the buffer cache.
4. When the data page is written to disk, it is called a page flush.
5. The log will write all of the modified data pages to disk via the lazy writer when;
   1. A CHECKPOINT occurs.
   2. A COMMIT TRAN occurs.
   3. Transactional/Merge replicated transactions are transfered to the subscriber.
   4. Space is required for new data pages.
6. Note, when a modification is written directly to disk without a log record being created; that modification cannot be rolled back until the next FULL backup is made.

The use of the read-ahead log model ensures that the ACID principals are not broken and to ensure redundancy, integrity as well as a recoverable database when you are using FULL recovery model.

Virtual Log Files:

VLF's is what makes up the transaction log internally. We can see the VLF's with the following command:

DBCC LOGINFO(DB_Name)

You should then see something similiar to this:

Columns of note are:

FileSize - file size in bytes.

Status - 0 not in use, 2 in use.

CreateLSN - Log Sequence Number.

When looking to optimize the T-Log we are looking actually looking to allocate the right number, and correctly sized VLF's. When VLF's are created, either by autogrow or a manual grow; a certain number of VLF's are allocated according to the size.

< 64MB will create 4 VLF's

>= 64MB < 1GB will create 8 VLF's

and > than 1GB will create 16 VLF's.

This is by design due to the algorithms used within the SQL Engine. If you create a new database, and pre create the log file with 16GB, then you will have 16 VLF's with a size of 1GB each.

In some cases you do not want to pre create the log file with a size that might make your VLF's unmanageable like a 64GB log. A 64GB log will create 16 VLF's with a size of 4GB. This can be problematic because a VLF is only cleared after it has been filled, but the same account to having VLF's which is too small.

"Why don't you create multiple log files to ensure that transactions are split?" is a popular question, with a simple answer. The T-Log works sequentially and in a round robin fashion. The only benefit in adding multiple log files is redundancy, in case the drive or RAID configuration fails for the particular log file.

The Test

I created a 50,000 row data set, for two databases using Data Generator from Red-Gate Software.

Below is the schema for the table:

I then used three basic statements to test how long it takes to complete:

Following company policy, I created for the SlowDB a log file which is 30% of the data size. This resulted in a log file which is 75MB. I manually grew the log in increments of 15MB, this resulted in 20 VLF's each with a size of 3.75MB. The second database, using the exact same data had a 8GB log with 16VLF's each sized 512MB.

The results of the queries are as follows:

It's quite shocking to see how something like this can affect a database, and the impact it can have on business. IF the log is allocated correctly, then it shouldn't auto grow which in some cases can cause that incorrectly sized VLF's are created, which could hamper performance.

Conclusion

Optimizing the transaction log escapes most of us when it goes further than the best RAID configs, or SSD's. But on systems without these configurations this can often be the solution to poor performing databases when every other avenue have been explored.

For systems, to properly allocate the log file; is testing. A good tool I found for this is SQLStress as it can simulate different workloads and it's flexible.