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.
