Tony Bain

RockSolid

I have had another long hiatus from blogging, again due to my availability.  This time I have been working hard with Red Rock to take their RockSolid Product and DBA Out-Task services to the next level.  Internally we have done some restructuring to give RockSolid the right model to take it forward the next few years. 

Externally, we have launched new web sites, product editions, service offerings and pricing models.  It has been a busy few months!

Take a look:
www.rocksolidsql.com
www.dbaouttask.com

Mass SQL Server 2000 -> 2008 Upgrades to come

Image via Wikipedia

Despite Microsoft’s considerable investment in SQL Server 2005 if never captured the strong market momentum that SQL Server 2000 did.  The problem, essentially it was a victim of its own previous success.  SQL Server 2005 for all its merits was not significantly compelling enough, in a lot of cases, to motivate organizations to upgrade existing installations.  New database applications installations used 2005, which has seen SQL Server 2005 steadily grow its install base.  But here we are 4 years after release and most production databases still remain on SQL Server 2000 (fast approaching its 10 year aniversary).  My own data shows the breakdown as follows:

Production SQL Server Installs

• 54% - SQL Server 2000
• 45% - SQL Server 2005
• 1% - SQL Server 2008

Those SQL Server 2000 installations are now quickly reaching end of life.  Their hardware is out of date (32bit), and the database platform is now out of date.  Upgrades are becoming a forced requirement.  SQL Server 2005 will now be skipped with upgrades going straight to SQL Server 2008. 

With millions of production SQL Server 2000 installations still in existence, I see a strong period of growth for those making tools that assist with the upgrade or consolidation of SQL Server databases. 

Follow your SQL Server on Twitter!

This is kind of a gimmick but also kind of cool so I thought I would blog about it here. 

One of the companies I am involved in, Red Rock, produces the RockSolid SQL Server Management tool.  This is a product which lets you define your management process, standards, procedures etc as a policy and then the tool takes on the role of implementing and maintaining those policies on your SQL Server environment going forward.  It also does monitoring and has extensive analytical prediction (proactive management) and reporting capabilities.

RockSolid is already installed at numerous enterprise sites where it is responsible for the management of thousands of production SQL Server instances.  However this week (in the next 24 hours) Red Rock is launching its SaaS based on demand version of RockSolid.  Using this any organization can sign up for RockSolid, download a small agent and then use RockSolid for any period of time they like for SQL Server management, or for assisting in solving performance problems or security issues etc.

As a fun way of highlighting the possibilities of the SaaS based model RockSolid now has the ability to direct message you on Twitter when events occur in your SQL Server environment.  In addition to the existing email and SMS feature, utilizing Twitter as a communication mechanism allows for messages to be received via SMS but also received and organized by tools such as TweetDeck while you are in the office etc.

If you are interested take a look, they haven’t quite announced the service publically yet but the sign up URL is there so you can get in early and have a sneek peek,

https://www.rocksolidsql.com/RockSolidSignUp/

Check it out and let me know what you think.

RockSolid Cloud Services Edition

  

Today we have announced our Cloud Services Edition of RockSolid.  This edition is specifically targeted to organizations that provide SQL Server services in the cloud from in a hosting or managed service capacity. 

RockSolid Cloud Services Edition is designed to allow for the standardization of monitoring, automated management, automated performance optimization and automated issue resolution across diverse environments that may be made up of thousands of SQL Server instances and tens of thousands of SQL Server databases.

The focus of the product is to significantly reduce operational costs associated with managing very large numbers of instances and databases through the automation of all routine DBA management tasks including performance optimization.  The policy driven framework also allows for provision of very high service levels guarantees to made to end customers with little associated overhead on the service provider in doing so.

RockSolid Cloud Services Edition has a web portal interface which allows end user customers to log in and review the state of their SQL Server databases from a performance, reliability, recovery and maintenance perspective.  The same interface when logged in to by the service provider allows the management of their SQL Server environment as a whole in a consolidated view.

New RockSolid product site

We have launched the new RockSolid product site here: http://www.redrock.net.au/RockSolid/

RockSolid @ SQL Pass

We will be exhibiting RockSolid, Red Rock’s SQL Server Automated Management product, at SQL Pass this year.  The summit is November 19-21 in Seattle.  If you want to book a time for a one on one demo of RockSolid please drop me a line at sqlpass@tonybain.com

RockSolid support for Query Resource Governor

As you may be aware, SQL Server 2008 includes the query resource governor to allow resources be distributed between sessions.  This is just a quick note to confirm that the upcoming RockSolid v2.8 release will include full support the resource governor and allow resources to be controlled across SQL Server instances within an enterprise environment.  More details to follow.

RockSolid Eventing

Eventing is one of those killer features in RockSolid that everyone who has been involved in managing a production SQL Server environment instantly sees the benefit of.

RockSolid monitors SQL Server instances and picks up on everything that occurs from a performance, security, recoverability, maintenance or configuration aspect.  Obviously everything that is detected as an “issue” of some kind raises a service request and these service requests go through the resolution processes (including automated resolution).  However everything that occurs that is not necessarily a problem at that point in time (such as a backup completing, a new user being created, a stored procedure being altered) etc is logged in RockSolid as an event.

Eventing is very useful therefore for inspecting an audit trail of routine and ad-hoc events that have occurred in a SQL Server environment.  Reviewing change history, security audits, recovery audits etc all have very useful roles in system management, but one of the most interesting roles of eventing relates to its application in performance management.

Now to put some context around this, many SQL Server performance tools will show you data around system performance and most good ones will be useful for displaying when negative situations have occurred, for example by graphing data.

This is an example graph, maybe it is slow the response time of a particular query, maybe it is showing your average CPU utilization, or your log growth rate.  Doesn’t matter, the point is the graph shows you that there has been a significant change from whatever the norm was to what the norm is currently.  Now this is typically the point where the tool stops providing information, and the DBA now goes off to perform manual investigation to try and unearth the cause of the change in performance levels.

RockSolid on the other hand, makes available its event analysis capabilities.  Events are categorized based on area of impact (performance, security, recoverability, availability) and the level of impact in those areas.  Events also have content, for example an event may have a performance impact on a specific stored procedure, table, database or an entire instance.

As a DBA investigating a performance issue, you can start with the analysis graphs in RockSolid which shows you a change in some performance aspect has occurred.  But then you can request event analysis  and RockSolid will present to you the most relevant events that occurred in the lead up to the change observed, explaining factors that could be responsible for such a situation. 

For example, you may review the performance of a given stored procedure and notice that it is running 20% slower than it did last week.  Requesting event analysis shows that a few days ago that particular stored procedure was altered and a query within that stored procedure had some additional predicates added.  Clicking on that query would show that the indexing that was in place prior to the change is no longer relevant to this query following the change, so currently a number of scans are being used when previously seeks were in use.  As a DBA you may choose to alter or add indexing to mitigate this impact.

Another example may be that on reviewing a monthly performance summary report, you notice that mid month the daily I/O levels increased for a given instance significantly.  Requesting an event analysis from RockSolid could show up that at this time a new reoccurring query was executed for the first time.  Drilling into this query you see that it has been run on a frequent basis since its first execution mid month.  This was due to a new application report being deployed at this time, and as a DBA you can now take steps to optimize that report query if necessary, or speak with the application manager about the impact of the new report.

In summary, the purpose of RockSolid eventing is to provide explanation around why changes in system dynamics have occurred.  The examples I have spoken about in this post refer to performance, but event analysis covers all key areas of database management including security, recoverability and availability.

RockSolid Index Management

This is a repost of a post I wrote for www.rocksolidsql.net

In RockSolid we have tried for every feature we have added, to take a step back and re-evaluate what is truly best practice rather than just accepting current industry standards for our processes.  One such area is that of database index maintenance, and in this article I will explain our approach to index management.

First let me talk about the most common method of maintaining database indexes for a SQL Server database (true for almost all SQL Server databases).  DBAs are aware that index maintenance is an important process for improving performance as it:

1. reduces fragmentation which improves scan performance
2. adds free space (fill) into the index which avoids page splits improving insert/update performance

And the most common method for running index maintenance is to schedule a chunk of time on the weekend and effectively have an outage while the maintenance process runs through all indexes on an instance sequentially carrying out a “rebuild” (which is effectively a recreation of the index).

This is common, but is it best practice?  There are a number of issues that start to pop up especially when database maintenance windows are decreasing and the requirement to have the database online and operation at specific times increases.

The first issue is how long should the database maintenance window be for?  How long can you tell the application users, the web site manager, the developer and so on that the database will be “busy” for while performing maintenance?  What time can they start their batch process, their invoice run, their data entry?  Sure a DBA can calculate the time required to carry out maintenance for a few databases, and recalculate these maintenance times routinely as the databases grow, but as many enterprise sites exceed 1000 databases, they cannot practically calculate exact window requirements for all databases organization wide.  So what happens is they allocate a catch all timeframe, half a day, all day Sunday, 10 hours etc which is sufficient enough to cover all requirements.  Regardless if a database is being maintained for 10 mins, 30 mins or 2 hours, it is effectively considered under maintenance and unavailable for the duration of the generic window.

The second issue is how effective and efficient is the index maintenance process being?  Half a day on a Sunday to rebuild all database indexes is useful, but do all the indexes in a database need to be rebuilt weekly?  Or, are there indexes that should be rebuilt much more frequently?  Again something that can be easily determined for 1, 2 or 10 databases, not so easily accurately determined for hundreds or thousands of databases.

And the third issue is what options should be applied when rebuilding a given index?  What is the optimal fill factor?  Should sorting take place in memory or tempdb?  Should online or offline rebuilds take place?  When we are talking hundreds or thousands of databases, we are usually talking tens of thousands, or hundreds of thousands of indexes.  Again, not a practical question for a DBA to answer using traditional methods.
So what does RockSolid do to address these issues and evolve a new best practice?

Firstly, with RockSolid you do not allocate large random chunks of time for the purpose of rebuilding indexes and adjust all business and application requirements around that maintenance.  Instead you tell RockSolid what time you have available for carrying out index maintenance, regardless of how long.  Maybe you have 1 hour here on a Tuesday, and 30 minutes here between batch jobs on a Friday, and 15 minutes here between processes on a Sunday.  Tell RockSolid what time you have available, and also tell it if that time is considered an outage (so no application processes are expected to run) or if the database should remain online during the window allocation (online maintenance).

But, again the issue the DBA may face is that with such a large number of databases under their control, accurately knowing what windows are actually available may be almost impossible.  So RockSolid helps to overcome this by providing a workload analysis tool, which will analyze your historical use of the database and find times when little or no activity has been occurring on the database.  Using this tool the DBA receives recommendations for when maintenance can be scheduled (typically they would then confirm these windows with the application users before implementing).

Next, when RockSolid is given a maintenance window to use, at run time it carries out an analyses of a number of factors to determine which indexes will provide the most return in terms of performance, given the time allocated to the maintenance process.  RockSolid evaluates factors such as fragmentation, index usage, index size and of course the time allocated to determine what is the best “bang for your buck”.  The time allocated is important because RockSolid will never exceed the boundaries of a maintenance window, for any type of maintenance process that is managed by RockSolid.

Thirdly, RockSolid looks at all factors necessary to decide the most appropriate use of Fill Factors (index usage, read/write ratios, time to next maintenance window etc) and rebuild options (sort in tempdb, online/offline based on the window type) to ensure every index is rebuilt using an optimal strategy, not only for the rebuild itself but for the performance of that index for it’s intended purpose following the rebuild.

Lastly, RockSolid provides feedback on the windows you have allocated to the database for maintenance.  Are the windows too large/too many?  Are their too few maintenance windows?  A recommendation may be that you will see an estimated n% improvement in performance by adding a 30 minute daily maintenance window as apposed to a once off weekly window, for example.  RockSolid pro-actively provides this analysis and feedback, and is constantly re-evaluating the maintenance requirements for the lifetime of a database.

This approach is redefining how database maintenance is performed, resulting in improvements in availability of the database, but also resulting in improved performance of the database through more effective index management.

RockSolid Webinar: Virtualized Management

Takes a minute of two to load (using Microsoft Producer at the moment).

RockSolid Virtualized Management Webinar