http://www.sqlservercentral.com/blogs/steve_jones/2007/06/06/katmai-manageability/

Printed 2014/04/25 01:00AM

Katmai Manageability

By Steve Jones, 2007/06/06

The database manages itself. Not totally, but close to it. It was an analogy made by Dan Jones with cars. Auto shop isn't really offered in high school anymore, people don't "tinker" with their cars much anymore.
If this is true, we have more time for data design, data architecture. I'm not sure I agree and as much as I hear, the more I think a DBA needs to be around because when something goes south, it goes way down.

Some trends affecting managability.

Trend 1 - New Features in the product
A huge chart of new features that were in SQL Server 2005. Many more features were added in SQL Server 2005 than will be added in 2008. However the surface area is still growing in many ways.

One of the manageability things is that no one can understand so completely the product anymore because it's too large.

Trend 2 - Data center and server consolidation
Companies are trying to have fewer centers and less servers. And the amount of staff will shrink, but the work grows. In fact it's expected that more databases per DBA is the trend.
Not too many customers are virtualizing SQL Server. Bigger servers and multiple instances, but not really virtual servers in production.

Trend 3 - Remote and embedded DBs
More and more of these are remote, kiosk, small form factor databases will grow. The data will upload from these remote and embedded systems, but the DBAs aren't on-site. One retail chain, close to 1000 locations, has 3 DBAs managing 5,000 databases. Most are small, but they still need to be managed.

Laptops and mobile workers will often have databases on their laptops that need to be managed.

Katmai Manageability
To combat these trends, Katmai is trying to scale the DBA with better managability. Here are some features:
- resource governmer - I've been asking for this for years. The goal is to differentiate workloads, monitor resources per workload group, and you can limit resources. The goal here is to prevent runaway queries.

You can set applications up into groups and set min/max for memory and CPU usage. You can set priorities between groups as well, so power users, sales, etc. can be placed above regular report runners. You can also put applications into groups as well.

Programmed via T-SQL, SSMS, or SMO

All Actions Audited
A frameowrk to audit the data environment. A new object (audit configuration) will be included to specify a location to store audit information. Can audit to the Windows logs or a file. This object will have a specification that defines which actions to audit. Can define auditing activity on DML, events, on particular objects. You can lump multiple specications together. It's a securable just like any other object. It will have a T-SQL programmability API, SSMS, or SMO access.

Performance Studio
Data collectors will be introduced to collect data in memory. They can be configured via T-sQL or Win32. They run as configured to collect information and post it to a warehouse somewhere. you can configure what and how often they collect data as well as how often they post to a warehouse.

This allows you to analyze performance. Some canned reports will be provided or SSRS can write custom ones. It can be moved to SSAS as well. After Ss2K8, they expect to provide a real-time dashboard  as well as a wizard to watch trends and offer suggestions.

There will be the ability to apply policy, like system must run within parametesr. So like if it goes above 80%CPU for more than 5 minutes, send an alert.

Data Compression
Not backup compression, but compression on tables and indexes.
works on partitioned tables (or non partitioned). You can compress one partition and not another.
It has page or row compression, works on clustered or non-clustered index.
T-SQL extenions available for table and index DDL. You'll have an SP to estimate the savings. Also available via SSMS and SMO
There is a CPU hit. Not every data type is compressable.
I'm not sure if this is a great idea for many people, but for less used data, this can be a big deal. Don't forget that a savings in the database can mean a savings in backup size and time, a tape savings. It can really add up. When I bought litespeed years ago for JD Edwards, when it was $400 a server, we had about a 1 year payback based on tape cost.

Database Mirroring Page Repair
Pages can become corrupt due to physcial data problems, i.e. disk going down.
If you failed to a mirror with a bad page, you could be down. This system watches both the principal and the mirror for bad pages. If found, it can call it from the other side to restore that page. New DMV shows the last 100 page repairs, new trace event, data collector can be run on this, etc.

Powershell Integration - not committed.
Intellisense - WILL BE IN 2008!!!! About time that this will appear.

Declarative Management
also called policy based management. The focus for SS2K8 is on the db engine to allow you to set policies for server conifiguration. This can work across multiple servers, like the master/target by pushing policies to particular groups of servers.
The goal is to lower the TCO and reduce the cost of managing. Should eliminate some scripts to tweak servers. Policy should handle this. You can enforce policy or just monitor.
This will replace the surface area configuration configuration tool.

Why these changes?
All of these changes are designed to combat the trends above.

To handle the additional features, they want to give you less knobs to manage. There aren't really less knobs, but with grouping and policies, you can physically have to work with less knobs as you deploy these to other servers. The first server takes time, but changes or alterations are more easily pushed to other boxes.

These same features help with consolidations because by grouping things together, one DBA can manage more servers. It makes the DBA more scalable
.
Once again with policies, this allows you to manage all databases that are on cell phones, or on laptops, or in any other remote situations. You can control and monitor those devices in an easier fashion.

The conceptual model for management

Policy - The desired state of facets, when to check the policy (on change, on a schedule, etc), actions on check (log, rollback, etc), what to check (name, setting, etc.), and the categories. Group the policies together, like all security policies.
Facet - projection on top of a target type. A groupding of properties, like the security properties of a target type. Facets can have logic, so it can have new properties.
Target Type - object, table, view, sproc, physical objects with their properties.

Kind of confusing, but each of the items above was a box in a stack with the target type at the bottom. I have the feeling we'll see these in BOL, so start to get used to them.

Back up MSDB
I don't know anyone that isn't doing this, but it was mentioned to be sure you back up msdb. As with everything else internal it seems, the policies will be stored in msdb.

They'll also have an internal SQL Agent job to handle policies. This will also use SQLCLR, evne if you have it turned off. Very interesting that they have a back door, but more importantly, it's good to know that it's a potential problem. Not likely, but you never know. I'm assuming they backdoor SQLAgent as well, probably using the subssytem even if the service is off.

To prevent changes, they use the same DDL events that we use.

Demo
policy to prevent creation of tables in the dbo schema. Fails with a level 16 error message.
There's a new Policy Management folder under Management in SSMS. There are policies, conditions, and facets.
If there's something out of compliance, the icon on a database changes and you can find the issues.
Showed how to create policies, categories instances, and set groups up.

Once again with policies, this allows you to manage all databases that are on cell phones, or on laptops, or in any other remote situations. You can control and monitor those devices in an easier fashion.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.