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.