Depending on their industry sectors, many database professionals have to deal with audits at some stage, often removing vital years off their lives and inches off their hairlines! Having worked as a DBA in the financial industry, I’ve experienced  both internal and external auditor visits on multiple occasions. In all cases, we pretty much had to drop all other work to ensure they were provided with the relevant information, or  to implement the changes they required so we could provide the information in the future.

The auditors’ levels of experience and understanding varied wildly. This was not their fault, as they are not paid to be database experts, but it could make them frustrating to work with. I spent a few days arguing with one who decided that I, as the DBA, should not be able to create logins and change permissions. As the database administrator, I felt it was sensible to let me administer the database! This is why we are paid, to be trusted to do our jobs properly. We reached a compromise to properly document the changes – a set of documents no one ever looked at – but I understood why it was necessary. I have met many DBAs who are frustrated day-to-day by the limitations put on them by auditors. Full separation of duties is not uncommon in the financial sector, so you can write your scripts, but someone else has to run them. Automated auditing has become a necessary part of the job description for many DBAs.

The key with any auditing is to decide clearly what actually needs to be recorded and reported on to pass the audit or satisfy your requirements. Some environments need to know when database or server objects are created, changed or dropped; some need to know when login permissions are changed; and some need to know when certain data is accessed. Different industries have to comply with their own individual legislation, which again changes the requirements for auditing. This all affects the amount of data to be stored and reported on, as well as the potential impact on the database.

SQL Server has long been weak in this area, and third-party products were really the only way to go until SQL Server 2008 introduced database auditing built on the new Extended Events functionality. SQL Server used to rely on C2 auditing, profiler or server side traces, or DML and DDL triggers. These solutions either imposed too much overhead on database performance, or required a lot of management and configuration. SQL Server 2008 introduces a highly configurable, lightweight solution that can fulfil almost all auditing requirements. One of its most impressive features makes it easy to have different audit configurations running at different times of the day, as well as varying audit specifications on different databases.

So it seems Microsoft has successfully addressed the auditing issues for SQL Server 2008, although it is only available in Enterprise edition, which, for some companies, can prove expensive to implement. Unfortunately, the IT world is not a simple place, and almost every environment I visit is still running SQL Server 2000 and 2005 – and, in some cases, even 6.5! These cannot be audited with the new functionality. Many environments require auditing across other database platforms as well, and it’s not in Microsoft’s interest to allow you to also audit Oracle and Sybase, for example. All of this leads to frustration for the DBAs and whoever is responsible for compliance in the IT department.

For more on SQL Server 2008 auditing, go to http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx

For a SQL Server Auditing podcast from Colin Stasiuk, go to http://sqlserverpedia.com/blog/sql-server-2008/guest-podcast-auditing-your-database-server/

Originally published in the February 2011 issue of Database Trends and Applications.

In line with recent SQL Server releases, Microsoft on Nov. 9 announced at the PASS Summit that the first Community Technology Preview (CTP) version of SQL Server 11 –  codenamed Denali (after the Alaska mountain also known as Mt. McKinley, the highest peak in North America) –  is available for download and evaluation. This is our first opportunity to look at some of the features that will form the basis for the next major release of SQL Server, expected to be fully available in the second half of 2011.

The CTP program is an effective way to allow the community to preview the new functionality in SQL Server releases since the release criteria for CTP features are the same as those for a final release. Microsoft works with partners to develop features, and then releases them to the community for widespread testing and feedback. MVPs get earlier access, which helps facilitate dissemination of information out to the community at the time of release. SQL Server 2008 had six CTP releases, so, right now we are probably in the early stages for SQL Server 11. Microsoft refers to a CTP as a release candidate rather than a beta, so the quality is expected to be high but still allow for changes and fixes based on feedback.

 Some of the cool features included in CTP1 include: 

HADR – Built on database mirroring functionality from SQL Server 2005, HADR allows you to group databases together into availability groups, and configure automatic failover to up to 4 failover partners. Each failover group enables the constituent user databases to failover as a single unit. In addition, you can use the replicas for read-only access to take reporting load away from the production databases, and you can even use them for production backups, making maintenance window management significantly easier.

Contained Databases – SQL Server has long struggled with portability of databases. While it has always been easy to move data and data structures, other objects, like logins, related SQL Agent jobs and linked servers have had to be moved independently. This was because these objects were tied to the instance rather than the individual databases. A contained database will move all these objects, along with information such as system settings and error messages – and the database – to a new instance. This can greatly assist application development, deployment and ongoing administration.

Management Studio Enhancements – It is possible to undock windows in SQL Server Management Studio (SSMS). This provides SQL Server users much more flexibility within the environment, and brings the user experience closer to that of Visual Studio, which is desirable for programmers and DBAs who need to port their skills.

One of the exciting features expected in CTP2 is Columnstore Indexes. This functionality will enable significantly faster query execution targeted for large scale data warehouses utilizing OLAP queries. Hundreds, possibly thousands, of times faster queries are possible, and storage compression can be much more efficient. Inserts, updates and deletes are not possible without rebuilding the columnstore index, so a regular load cycle would be required to update the data as necessary.

You should also be equally aware of the features that are being depreciated so you can prepare for migrations when the release becomes necessary for your environment. Early testing of your applications with a new database platform will help with planning for the future. Probably the major change to note here is that support for Data Transformation Services (DTS from SQL 2000) is now removed entirely and replaced with SSIS. This was a significant headache when moving to SQL 2005, but, you could still use the old DTS functionality. This is no longer possible. The documentation also says support for database compatibility mode 80 (SQL 2000) will be removed; however, it is still shown in CTP1.

Have a look at the CTPs and provide Microsoft with as much feedback as possible, as this will help ensure the high quality of future SQL Server releases for all of us.

All content in this column was correct at the time of this writing; feature names and functionality are subject to change in future CTP releases.

Useful references:

CTP1 Download – http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en

CTP feedback forums – http://social.msdn.microsoft.com/Forums/en-US/category/sqlserverprerelease

Unsupported features – http://msdn.microsoft.com/en-us/library/ms143729.aspx

Contained Databases – http://msdn.microsoft.com/en-us/library/ff929071(v=SQL.110).aspx

HADR – http://msdn.microsoft.com/en-us/library/ff877884(SQL.110).aspx

Columnstore Indexes Whitepaper – http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf

Originally published in January 2011 edition of Database Trends and Applications

When implementing a new application it is often difficult to plan properly for disaster recovery. Many DBAs just implement what seems obvious or sensible without considering if their strategy will actually cover them adequately in a Disaster Recovery (DR) situation and can be catered for considering environmental limitations. In some cases the obvious and sensible options are not even covered and in (hopefully) rare cases there is no strategy at all!

It is also important to revisit existing applications and make sure the disaster recovery plan is extensive and robust enough. Fundamentally this is why DBAs exist – to provide the database service within acceptable boundaries, so it’s a vital area to focus on. There is often confusion between High Availability (HA) and DR. No matter how many HA options you have implemented it is still crucial to have a proper, tested DR strategy in place in case of catastrophic disaster.

Some of the considerations include:

1. The Business SLAs
The most important of all to help build the strategy. If the business can accept losing up to 24 hours of data changes there is no point implementing transaction log backups every 5 minutes. A full backup once a day in simple recovery mode will cover the SLA and reduce overhead and management costs. If the acceptable loss is 30 minutes then log shipping would be adequate and relatively cheap. If no loss is acceptable things can get complex and expensive. HA options like clustering, database mirroring or SAN replication need to be included and perhaps a combination of them. I often ask DBAs what the acceptable data change loss is and they guess at a timeframe. The key is to ask the application owners what is really acceptable then hit them with the budget costs to provide it. Don’t sign up to anything you can’t deliver with your existing infrastructure. It is also important to consider the backup retention policy. Some companies need to be able to recall data from 20 years previously. This adds to cost and manageability.

2. Database Size
It is often very difficult to estimate how large a database, log and hence backups will be, especially when working with a brand new application. 3rd party application providers may be able to provide some guidance but in house developed applications require some educated guess work. For DR the important consideration is volume of backups. A full backup will be the same size as the database data size unless compressed. Compression should be tested to find the expected compression ratio for your data and allow for deviation. Differential backups can be more difficult to manage but save significant disk space. Transaction log sizes can vary wildly, especially during database maintenance.

I have recently seen an environment where they had 12 x ~5GB databases and 1TB of storage for backups. However, the business required that each database had a full backup taken every day and they be retained for a week. In addition that a weekly backup be retained for a month and a monthly backup to be retain for a year. They had no offsite storage solution in place as yet. So by the end of the year they would have:

12 x 5GB x 6 days = 360GB
12 x 5GB x 5 weekly backups = 300GB
12 x 5GB x 12 monthly backups = 720GB
1.38TB!

So even though 1TB sounded a lot to them for their small databases they were significantly short on storage for the amount of retention the business required (assuming no growth) and also had a single point of failure if they lost the storage array.

3. Offsite Provision
a. A remote DR site with equivalent hardware to provide application availability and acceptable performance. Backups are copied and restored ready for when they are required. This also provides a good opportunity to regularly test the integrity of your backups.
b. Backing up to disk locally and then offsite to tape. In the event of outage the tape can be recalled and data recovered. The time taken to source the tape and copy and restore the backups must be considered within the acceptable downtime.
c. Using virtualised servers increases your options here. It is far easier to provision new servers and replicated existing ones independently of hardware with a virtualised infrastructure. Most think that virtualisation improves server resource usage, however it turns out most companies implement for DR purposes.

4. Disaster Strikes
In the event of a full DR situation there are many unexpected things that have to be prepared for. If a complete server (bare metal) restore is required then you will need at least:
1. Hardware and any required drives.
2. O/S installation media plus any service packs.
3. Apply required windows updates and patches.
4. SQL Server installation media plus service packs and any hotfixes.
5. Restore system databases (from disk/tape? Is the tape available, how long will it take?)
6. Restore user databases and apply any differentials and/or transaction logs.
7. Check security and network configurations.
8. Anything relevant for the application.
9. Reconfigure backups before users have access to the databases.

This is not a comprehensive list, but shows there is a lot to consider for when implementing a DR strategy for SQL Server. The key is to test for ALL possible scenarios. Where are the single points of failure? What happens if the building blows up? What if the most recent backup is corrupt? What happens when the DBA is on leave?

Microsoft is introducing a new technology in SQL 11 (Denali) which adds options for HA and DR, called inventively HADR. Built on database mirroring functionality, this allows up to 4 failover partners for groups of databases and allows backups to be taken from the partner which could greatly help manage maintenance windows.

Denail is available now as a Community Technology Preview (CTP) release. CTP1 was released late in 2010 and others will follow before SQL 11 is launched. It is a great way to see and test what is coming in future for SQL Server.

More details on HADR here:
http://msdn.microsoft.com/en-us/library/ff877884(SQL.110).aspx

You can download CTP 1 of Denali here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9&displaylang=en

CTP feedback forums – http://social.msdn.microsoft.com/Forums/en-US/category/sqlserverprerelease

Project Lucy is a free cloud based service from Quest Software to analyse the performance of your SQL Servers. Please let us know what you think from the feedback links.

Here is a video to describe what Project Lucy is and how to get started. I seem to have some lighting and resolution issues which I’m working on for future videos!

Visit www.projectlucy.com and start uploading for free analysis now.

I first met Adam at SQLPASS in Seattle where we discussed the benefits of blog syndication at SQLServerPedia. I’m delighted that he has agreed to syndicate his posts.

Adam Jorgensen, MBA, MCITP, MCDBA is the President of Pragmatic Works Consulting, a global BI solutions firm based on Jacksonville, FL. and has been delivering SQL Server and Business Intelligence Solutions for over a decade.  His passion is finding new and innovative avenues for clients and the community to embrace business intelligence and lower barriers to implementation. Adam focuses on mentoring executive and technical teams helping them realize the value in their data, while accelerating the technology learning curve.

Adam a regional mentor for  SQLPASS and a Virtual Technology Specialist for Microsoft. He is also an active member of the South Florida SQL Server User Group (SFSSUG) and a co-founder of BIDN.com (Business Intelligence Developer Network). Adam co-hosts SQLLunch.com,  is a co-chair of the SQLPASS Virtual Chapter for Business Intelligence, and serves on other community groups and professional boards.

He speaks regularly in person and virtually at industry group events, major conferences, Code Camps, and SQL Saturday events on strategic and technical topics. He has co-authored white papers and several books on business intelligence, among other topics. Prior to his role with Pragmatic Works, Adam garnered extensive technical and managerial experience in industries including automotive, manufacturing, software, education, security, and retail. 

http://sqlserverpedia.com/blog/author/adam-jorgensen/ 

SQL Azure is Microsoft’s cloud-based relational database service hosted in their data centers, and it’s got some DBAs worrying about the future. The more I look at the technology, however, the more I see practical applications for it and the less I think people should be concerned.

Working with smaller companies, I often find SQL Server struggling along without a specialist DBA. It is generally an exercise in keeping your head above water and hoping nothing fundamental goes wrong. By moving suitable applications to the cloud, companies can remove the burden of worying about areas such as hardware, high availability, and patching. Microsoft will manage resource allocation and transparent failover, but normal database and security administration is still in the hands of the IT department. You have no control over CPU, memory, or storage configuration, but you still manage schemas, indexes, statistics, and query optimization.

Any organization considering SQL Azure should be aware of some important limitations, which are fundamental to the decision process:

Editions

Web: maximum of 1 or 5GB of data.

Business: maximum of 50GB of data, charged in increments of 10GB.

For example, chargeable size is calculated purely on your objects, data, and indexes, not system tables. You can have multiple databases, but you cannot query across them. You are charged by edition, size, and bandwidth usage.

Growth is automatic, as is switching between editions; you just incur increased costs. You can set a maximum size to prevent excessive costs; however, this will prevent data inserts when the threshold is met.

Security

SQL authentication only-for obvious reasons, integrated security cannot be implemented, but this may be an issue for your security model.

Unsupported features compared to SQL Server 2008 include:

• SQL Agent
• Profiler
• Replication
• Database Mirroring
• Service Broker
• Log Shipping
• Reporting Services
• Trace Flags
• Analysis Service
• Database Tuning Advisor
• Resource Governor

The two that really stand out for me here are SQL Agent, so fundamental for scheduling anything with SQL server, and Profiler, which, particularly for an in-house developed application, is like trying to drive without looking at the road.

Interestingly, every table must have a clustered index-my heart jumped a little when I read this – finally a way to enforce this.

Timeouts

Long-running queries and idle connections will be closed. Also, excessive resource usage will result in the closing of a connection. I struggled to find a definition of what excessive resource usage would equate to, but this is an inevitable constraint, as others will be sharing the resources allocated to your database, much like your ISP’s “fair usage policy.”

Disaster Recovery

Backup and restore are not supported, which is terrifying to the traditional DBA. Microsoft will handle high availability and restores if necessary, but this is not enough. If you need to roll back to a previous point in time, you will need to take regular extracts of your data. Alternatively, Microsoft allows you to take copies of your database to another Azure database with relevant costs.

With these limitations taken into account, SQL Azure, as it stands, is not a good fit for high end, volume transaction, large storage environments, but perfect for a segment of the SMB market-particularly companies with no DBA and limited budgets, such as a startup company looking to keep costs down until its business shows success.

Further information is available at www.microsoft.com/en-us/sqlazure/default.aspx.     

Originally published in December 2010 edition of Database Trends and Applications

There is lots of new content available from our latest bloggers added to SQLServerPedia Syndication:

Sarvesh Singh

Sarvesh is a DBA at COEO Ltd, a Microsoft Gold Partner providing SQL Server consulting and Remote DBA services to customers who outsource management and 24×7 operations of their SQL Server platform. He currently holds MCITP: database administration for sql 2008, MCTS 2008 -Database Development and 70-652 – Windows Server Virtualization, configuring.

http://sqlserverpedia.com/blog/author/sarvesh-singh/

His online presences include:

• Blog: http://sarveshsingh.com/

Brian K McDonald

Brian K McDonald MCDBA, MCSD is a Business Intelligence consultant for Pragmatic Works. Brian has experience in Database Administration, SQL Server Reporting Services and SQL Server Integration Services. He has worked as an Application Developer, Network Administrator and a Database Administrator throughout his 11 years in the IT industry. He even spent a year on a large Help Desk operation a long, long time ago. Brian takes pride in all his projects and diligently works to not only meet expectations, but greatly exceed them. Brian has written articles for industry websites and continually strives to learn more and become a better version of himself. Brian is an active member of the Jacksonville SQL Server User’s Group and has presented at User group meetings, Code Camps and various SQL Saturday events across Florida.

http://sqlserverpedia.com/blog/author/briankmcdonald/

His online presences include:

• Blog: http://www.sqlservercentral.com/blogs/briankmcdonald/
• Twitter: @briankmcdonald

Melissa Coates

Melissa is an enthusiastic Business Intelligence developer based in Charlotte, NC. As a consultant with Mariner, she specializes in delivering BI and Data Warehousing solutions using the Microsoft platform. Melissa has expertise with SQL Server Reporting Services, Analysis Services, and Integration Services, as well as SharePoint BI.

Formerly a CPA, Melissa brings a deep understanding of business issues and is able to foster productive working relationships between IT and business departments. She has a keen interest in visualization techniques and best practices. Melissa enjoys blogging, writing articles, and speaking about Microsoft BI development.

 http://sqlserverpedia.com/blog/author/melissa-coates/

Her online presences include:

• Blog: http://www.sqlchick.com
• LinkedIn: http://www.linkedin.com/in/melissacoatesprofile

Mark Broadbent

Mark Broadbent is currently working primarily as a SQL Server Specialist. He has been using SQL Server since 1997, starting with SQL 6.5 and every release since in numerous highly available configurations. He has worked with many other Microsoft products and likes to dabble with other platforms and environments. Mark currently holds many Microsoft professional accreditations including MCSE+i, MCAD.NET, MCDBA, MCITP Database Administration for SQL 2005 and SQL 2008 and MCITP for Database Development for SQL 2008.

http://sqlserverpedia.com/blog/author/mark-broadbent/

His online presences include:

• Blog: http://tenbulls.co.uk/

For recognition and as a thank you to all the contributors to the site we are holding annual awards for the best blog posts syndicated on the site from November 2009 – November 2010.

Editors Kevin Kline, Jeremiah Peschka and myself have scoured SQLServerPedia.com to find the most informative and useful posts across a wide range of categories.

Please click below to cast your vote.

http://sqlserverpedia.com/awards/

Voting opens today (November 1^st) and closes November 8^th.  Winners will be announced by e-mail, on the site and at the Quest hosted cocktail party at The Tap House Grill, Tuesday, Nov. 9, at 8 p.m. PT at PASS.

If you are attending PASS please come along and enjoy a drink. It will be a great opportunity for me to meet some of you in person.

Two more blogs added for syndication on SQLServerPedia today:

Jose Chinchilla

Jose Chinchilla (aka SQLJoe) works as a Business Intelligence consultant for Convergence Consulting Group, a full-service consulting firm based in Tampa, FL. He has over 12 years of experience in infrastructure, virtualization, web development, network and database administration and holds multiple certifications such as MCITP SQL Server 2008 Database Administration, MCTS SQL Server 2008 & 2005 as well as MCTS Business Intelligence SQL Server 2008. Jose Chinchilla serves as President of the SQL PASS Business Intelligence Chapter for the Tampa Bay area and is an active contributor to the SQL Server community, avid speaker, twitterer and blogger.

Twitter: http://www.twitter.com/sqljoe
Blog: http://www.sqljoe.com
LinkedIn: http://www.linkedin.com/in/josechinchilla

He has recently posted about using Change Data Capture and his frankly nuts (and successful) attempt at completing 4 Microsoft certifications in 30 days!

http://sqlserverpedia.com/blog/author/jose-chinchilla/

David Levy

David Levy is a Senior SQL Server DBA at CDW with 10 years of IT experience, mostly as a software developer building web and windows based applications (VB, VB.NET, C#, C++ and a smidge of Java). He has always found database design and set based logic interesting, so 3 years ago he took the plunge and became a DBA, soon after he discovered people would tell anyone who would listen all about the SQL Server internals. He was hooked and has not looked back since.

Blog: http://adventuresinsql.com/

He has recently posted about resolving deadlocks using indexes and using the Dedicated Administrator Connection (DAC) to troubleshoot a SQL Server.

http://sqlserverpedia.com/blog/author/david-levy/

A warm welcome to you both.

Being UK based I’m always delighted when a blogger shows an interest in syndicating their blog on SQLServerPedia who is from the UK.

Richard Douglas

Richard Douglas has been working as a DBA in women’s clothing since the start of 2010, not literally he hastens to add! He is certified in SQL Server 2008 and in his spare time plays the trumpet in local symphony orchestras. You can read Richard’s blog at http://SQL.RichardDouglas.co.uk, follow on twitter at http://twitter.com/SQLRich and on find him on LinkIn at http://www.linkedin.com/in/RichardPDouglas.

Richard has recently written about Unused Indexes for a T-SQL Tuesday and Recovering TempDB.

If you would like to syndicate your blog to thousands of readers, you can get started here.