One of the new things you can do in CTP5 of SQL Server 2008 is to encrypt your databases so that they are protected at rest and so are any backups made from them. So this prevents anybody from accessing a database without going through the server it belongs to.
To move an encrypted database from one server to another you would need to move the key that encrypted it as well. For example you might send the key be e-mail and then send the database on CD's in the post.
First you need a master key and then a certificate:
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'UseStrongPassword1!'; --SMK???? GO CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate for Sensitive Data' GO
Then you can use this to encrypt the database with this:
USE Retail_DWH CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyDBCert
The encryption process runs as a background task and the database is available during the process.
It's a really good idea to back up the certificate as without it you can't get the database or backups back - whihc of course is the whole objective!
This script backs up the certificate to a temp folder as an example:
BACKUP CERTIFICATE MyServerCert TO FILE = 'c:\temp\MyServerCert' WITH PRIVATE KEY (file='c:\temp\MyServerCertKey', ENCRYPTION BY PASSWORD='UseStrongPassword1!')
To move this to another instance or server the first step is to create a master key on the new server:
create master key encryption by password = 'UseDifferentStrongPassword1!'
The certificate can then be restored like this:
create certificate MyServerCert from file='c:\temp\MyServerCert' with private key ( file = 'c:\temp\MyServerCertKey', decryption by password='UseStrongPassword1!')
So very simple to setup, easy to use
SQL Server 2008 will have the ability to compress a database, but that functionality is not in the latest beta release (CTP5). However CTP5 does have the ability to compress backups by simply extending the existing backup command:
backup database adventureworks to disk='C:\abc\adventureworks_compress.bak' with compression
What's interesting about this is that it doesn't just compress the backup to less than a quarter of the uncompressed size (for this database), it does it fater - the compressed backup took 8.9 seconds compared to 20.1 seconds for an uncompressed backup on my virtual machine.
It get's better as the restore process is also faster - restoring the compressed backup in 12.3s compared to restoring the uncompressed backup in 19.7s.
Note the restore command is the same as in SQL server 2005 i.e. you don't need to tell restore the backup is compressed.
So why wouldn't you compress your backups?
I have absolutely no idea!
The feature of SQL Server 2008 that seems to get the most attention from DBA's is the Resource Governor. It basically does what it says on the tin; for example you may want to reserve a portion of CPU or other resource for a user, process etc.
At the top level Resource Governor has Resource Pools, and there is always default resource pool
Below this you create Workload groups:
CREATE WORKLOAD GROUP groupAdhoc CREATE WORKLOAD GROUP groupReports CREATE WORKLOAD GROUP groupAdmin
These workload groups will be belong to the default resource pool, and for this introduction I will keep it simple, by leaving it like that. It is then a matter of assigning whatever you want to the those groups by using a function like this:
CREATE FUNCTION rgclassifier_v1() RETURNS SYSNAME WITH SCHEMABINDING AS BEGIN DECLARE @grp_name AS SYSNAME IF (SUSER_NAME() = 'sa') SET @grp_name = 'groupAdmin' IF (APP_NAME() LIKE '%MANAGEMENT STUDIO%') OR (APP_NAME() LIKE '%QUERY ANALYZER%') SET @grp_name = 'groupAdhoc' IF (APP_NAME() LIKE '%REPORT SERVER%') SET @grp_name = 'groupReports' RETURN @grp_name END
Notice that you can use any rule you like to create an association with a workload group e.g. users or the application.
This function is then needs to be applied to the resource governor like this:
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION= dbo.rgclassifier_v1) You are now ready to configure which workload group gets which resources with this syntax:
ALTER WORKLOAD GROUP groupAdhoc WITH (REQUEST_MAX_CPU_TIME_SEC = 30)
WITH (MAX_CPU_PERCENT = 50)
Finally the changes need to be applied to the resource governor process running in memory
ALTER RESOURCE GOVERNOR RECONFIGURE
Changes can be applied to the Resource Governor at will and take effect immediately. The function can be changed to move objects in to different workload groups as required and all of this will affect processes already running.
To try this you will need CTP of SQL server 2008 which is available here.
Continuing my series of notes following the TechNet event last Thursday evening, I wanted to explain that in SQL Server 2008 there are two new data types for storing spatial information; geometry and geography. Unfortunately on Thursday I didn't have the final release of CTP5 so my demo didn't have the geography data type and I used latitude and longitude coordinates in a geometry data type with a Spatial Reference ID of 4326 to indicate that they were in fact geographical data.
To explain to those that weren't there...
The Geometry data type supports a flat 2D surface with XY coordinates for points. Points can be on lines, on line string and mark the edges of polygons. There are then methods like STintersects , STarea, STDistance, STTouch which work as you'd expect; the naming convention conforming to standards from the Open Geospatial Consortium (OGC).
The Geography data type uses the same methods but the data type reflects the fact the we live on a curved 2D surface popularly known as the earth. The st* functions are used against both new data types but the answers will be different as a result of the curvature.
One of the odd things about these spatial data types is the need for the aforementioned Spatial Reference IDs (SRID). Both geometry and geography data types have two parts, the coordinates of the object and the SRID indicated in orange below:
DECLARE @g geometry; SET @g = geometry::STGeomFromText('POINT (3 4)', 0);
DECLARE @g geography; SET @g = geography::STGeomFromText('POINT (3 4)', 4326);
The numbering convention for each type is set by the European Petroleum Survey Group (EPSG) standard. This standard dictates that the SRID of any geometry data is 0 and so this is the default for the geometry data type in SQL server. For Geography the default of SRID is 4326 to indicate that the data conforms to the WGS 84 standard for curved earth data.
Note that if the SRIDs of two pieces of spatial data don't match then the various spatial methods in SQL server 2008 will return null.
One puzzling thing to leave you with on spatial data is hemispheres:
I am guessing that this is also something to do with the standards?
It seems every week we are bombarded with tons of surveys, that scare us into eating more of this and generally less of everything. Surveys on database health and security are much rarer, but I did notice that David Lichfield is about to publish that latest edition of The Database Exposure Survey 2007.
I would expect that there are a few databases out there that are vulnerable but I was surprised how high this figure is generally and also that it applies more to Oracle than to SQL Server. The basic problem is that the versions in use have known vulnerabilities where the latest versions are better able to deal with threats. To counter this Oracle and Microsoft release patches and best practice advice, but customers are simply not applying the patches or following the advice.
I am not an expert on Oracle but I am sure they are just as keen as we are to help close the gaps and you should contact your reseller and crawl the extensive help on their website. Where I can help is to suggest a few pointers for SQL Server 2000:
Of course if you do want a rapid career change then please ignore this.
Like Salvador Dali I have hung up my watch. I am in Barcelona to enjoy a bit of culture now TechEd is over.
However I did notice that the next beta of SQL Server 2008 (CTP5) which has about 75% of the functionality of the final release and is now available on the connect site to download.
Some of the new and interesting stuff in there includes:
It's the little thing that make you smile. I am at TechEd this week at an excellent session by Bob Beauchemin on the T_SQL enhancements in SQL Server 2008.
He popped up a slide with the += operator which allows you add a number to itself in common with many other languages so:
@MyNumber += 2 would add to the number (this also works for minus, multiplication and divide).
Somebody asks Bob "Does that work with strings as well?"
Let's check that Bob says.
declare @myName varchar(12) = 'Bob' set @myName += ' Foo' Print @myName
declare @myName varchar(12) = 'Bob'
set @myName += ' Foo'
that returns 'Bob Foo' which is what you'd expect but good to know.
So top marks to Bob for his presentation style, and to the product guys for making the new function consistent.
I am manning the ask the experts SQL server booth at TechEd this week. One of the many interesting questions I was asked was how the reporting information from the standard reports in SQL server could be integrated into one set of data from multiple servers.
I couldn't think of anything immediately to help him, so I am posting my thoughts here.
The reports are using Reporting Services from a SQL source, but I couldn't find out what this was without running profiler - so that's what I had to do. I setup a new trace to watch for RPC complete events with a column filter on application name set to "Microsoft SQL Server Management Server". I then ran the Activity - All Session report:
and then stopped the trace and looked at the relevant event:
as you can see the SQL looks long but it's mainly columns, there are only four tables involved:
Of course in some of the other reports there are multiple charts and data regions so the underlying query will be much more complex, but this can be easily broken down and will only hit four or five table for each sub-query.
The next step is how to combine and extract this. Personally I would use Integration Services to run this query for each server with the query as the source and put this out to one table. On the way throw you would tag every row with the server it came in a new column. the final step would ne to fire this out to excel and schedule it to run as required.
Another approach would be to do the who;e thing in one stored procedure which would link to each server in turn and would run on demand.
Along time ago the BBC had just the one TV channel it's shot up to four now we are digital. Microsoft are in on the act. Originally there was only Channel 9 which is named after the radio channel you can listen to pilots on in commercial aircraft however it's really aimed at developers, developers, developers.
Unlike the BBC Microsoft channels are intended for particular audiences and the line-up goes like this:
If you have content you would like to be on TV then get back to me and if not just tune in and turn on.
Blogging is pretty new for me and it's difficult to know how interesting or valuable my stuff is. So I am pleased that my thoughts on providing a way to get the standard SQL server reports to run across multiple servers have generated some interest.
I would agree that what I wrote was a lash up that is only really appropriate for a few servers and is quite labour intensive. For the big enterprise there is System Center which can do this kind of thing, but not everyone is going to make that investment.
One of my new friends, Buck Woody in the SQL server product team has quite a few posts on this subject on his blog and he also sent me a link where the report definitions (RDL) for the standard reports can be found so you won't need to use profiler to trap the SQL used.
That's why I blog, to learn and pass it on.
I picked up a number a couple of questions from Thursday night's TechNet Road show in London including a couple on dependencies in SQL Server 2008. So here's the answers...
Firstly there are two sorts of dependencies:
Dependencies are updated when a referring object is created and rely on names so if you want to rely on this then you need to use proper four part names i.e. MyServer.MyDatabase.MyTable.MyColumn.
Note that you can get dependencies across linked servers but this will only work with the four part naming convention and not EXEC ('…') AT linked_srv1
The next question I got asked was how synonyms work with dependencies. so here's an example:
-- Create a synonym for the Product table in AdventureWorks. USE AdventureWorksDW CREATE SYNONYM dbo.MyProduct FOR AdventureWorks.Production.Product; GO
-- check the dependency for MyView SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name ,referenced_server_name AS server_name ,referenced_database_name AS database_name ,referenced_schema_name AS schema_name , referenced_entity_name FROM sys.sql_expression_dependencies WHERE referencing_id = OBJECT_ID(N'dbo.MyProc1');
All this shows is the dependency on the synonym
If a view is created against the table directly i.e.
CREATE VIEW dbo.MyView AS SELECT Name,ProductNumber FROM AdventureWorks.Production.Product;
..then the dependency reports correctly.
I have to say I don't think that's a great story and you get the same information back from the management console if you right click on an object and select view dependencies.
SQL Server 2005 was a revolution from the previous version, and while SQL Server 2008 is more of an evolution it has loads of new stuff in it.
Many of the new features such as resource governor and spatial data are generating interest for IT Professionals and there have already been several million downloads of the various CTPs of SQL Server. However some of the stuff (like spatial )can be a bit daunting and you're often so busy the download never gets properly used.
So how about some free help to get you started and support you as you start to plan how to use it and develop a proof of concept or pilot?
The answer to this is called the Momentum Program. It's designed for IT Professionals who are working in an organisation which currently doesn't have a strong relationship with Microsoft. It is by invitation only and this process starts by following this link.
As you can see the program also applies to most of the other Microsoft platform betas, and you can register for more than one. For example if you want to try SQL Server 2008 in a windows server 2008 virtual environment.
So if you are serious about looking at SQL Server 2008 for whatever reason, please read the link and contact me
Have you ever tried to explain to your mum what you did at work today? For me I had no problem today - all my new friends had a lunchtime party and we all got to make things with Lego. Our teacher Ginger gave us some instructions and James and I got to make an oil rig which rolls out oil drums if you lift the crane. Some of my other chums made a house, trees, a dam and all sorts of stuff and of course this is where the fun started as there were lots of random piles of Lego around the room which meant there was lots of frantic searching for the right bits.
As I keep saying only at Microsoft! There was a very good reason for our fun and that is that we are sponsoring the UK bit of First Lego League. The competition this year is all around energy and is played out around what we made at lunchtime
So if you have got kids get involved and have some serious fun.
The most often question I get asked about SQL Server 2008 is when is it out? If you are in marketing then there is a world wide launch event in Feb 2008 for Windows server 2008, Visual Studio 2008 and SQL Server 2008. Well that's a bit like the launch of the Mini Clubman on 10 Nov - just because it's launched doesn't mean you can get one immediately. In fact I will be waiting until the SQL Server 2008 launch in February for my Clubman to arrive,
Microsoft and the rest of the software industry is the same i.e. launch doesn't mean ship. So you won't be able to pitch up to the 2008 launch event and walk away with SQL server 2008; it won't be out until later in the year (that's a Microsoft year which runs July to June).
So we're going to have to wait a little longer, but that allows time to evaluate the new release through the beta program, white papers and other technical content on the various sites such as:
Alternatively why not register for an evening of my SQL server 2008 stuff at the TechNet road shows here
When was the last time you picked up a land line phone and there was no dial tone? BT are no doubt proud of this but it's not going to make them any headlines, it's much more entertaining and newsworthy to complain about our 3rd world broadband speeds.
The SQL Server product guys are in the same invidious position; We have had over four years with nothing on a our major vulnerabilities report and that's just not going to be a big story on the Register, Fark or Slashdot, so there's no hyperlinks for me to put in.
But it would be niaive to suggest this will continue forever, and there's is a ton of new security stuff in SQL Server 2008. This will ensure we give all our audience something to look forward to; legitimate users can more easily protect their data and terrorists and criminals can stretch their minds coming up with new ways to get it. However from my limited knowledge of security I think they will increasingly rely on the old methods like posing as cleaners and couriers, or rummaging through the bins.
The IT industry is awash with acronyms, Microsoft is drowning in them and I think we will soon need to move to FLA (Five Letter Acronyms) to keep up - I thought MSRC was the Microsoft Research Centre but Steve insists its' the Microsoft Response Center, and we're both right!
However we are not alone the regulatory and compliance space is also liberally sprinkled with terms like SOX, GLBA, HIPAA, DPA, EUPD as well as a list of international and European standards as long as your arm.
The other thing I have noticed about compliance is that everyone is an expert especially when it comes to the data protection act! Like the Highway Code everyone seems to know it but have you met anyone who has actually read it recently. So if you are wondering how to get compliant and do battle with all those abbreviations and the parts of your organisation who delight in this stuff then check out the TechNet Regulatory Planning Guide.
I was made in Scotland, my grandmother is from Newcastle and I only live in the south because my wife hates the cold! If I did live up north, I am sure I would be complaining, like many people there, that everything happens in London, such as the Olympics and the best shows including of course our TechNet road shows.
Back in September we did head north for our TechNet road shows and Steve & James got a storming reception, so we thought we'd come back for the next tour like you asked us to. However the take-up looks a bit weak, but only outside London where we are packed out. So what's going on?
Like the last road shows we promise not to bore you with endless PowerPoint decks and Marketing speak so get registering here. Failing that please comment on on what you would like to see at a TechNet event in the North.
Viral has working with and blogging about Search Server 2008 and how this can help to find stuff in the enterprise.
I'm a BI evangelist so what can a search tool like those in Search Server and SharePoint do for me?
It would be great if I could search through cubes and reports to answer questions like "How are my Sales doing this week?" So I was very pleased to discover that Guy Mounier from BA insight has written a white paper on exactly how to do that over the Microsoft BI platform by using the Excel services and the Business Data Catalog features in SharePoint 2007.
If you aren't using the Microsoft BI platform yet then BA Insight have similar solutions for the other major ERP/CRM vendors such as Oracle, SAP, PeopleSoft, Siebel.
1. Are you an IT Professional trying to get the latest Microsoft technologies to work in your organisation?
2. Are you working in a small organisation that has no direct relationship with Microsoft?
Then we have the answer for you with our Momentum program. Simply put we give you a TechNet subscription and PSS support in return for sharing how your project or evaluation is progressing via a web portal, with the product teams. We would then want to blog, or podcast about you experiences, from a technical rather than a marketing perspective.
As your use of the technology evolves, Microsoft will support you with appropriate help and advice:
Technical Content & Managed Forums
TechNet+ Direct Subscription
Pilot & Deploy
Premier Support Services (PSS) Requests
The Microsoft technologies that apply to the Momentum program are as follows:
IT Pro Evangelist Contact
Windows Server2008: Active Directory
Windows Server2008: Networking
SQL Server 2008
Windows Server 2008: NAP
Winodws Vista: Secure Deployment
Windows Server 2008: IIS7
Sharepoint Server 2007
Windows Server 2008: Powershell
System Center: Essentials
System Center: Configuration Manager
System Center: Operations Manager
Windows Compute Cluster 2003
To find out more contact the relevant IT Pro Evangelist so we can help you get the most out of latest Microsoft releases.
There is a new Microsoft Site for that's just been launched, TechNet . It's like the Channel 9 stuff but targeted specifically for IT Pro's, where Channel 9 is focused on the developer. The idea is to create a place where you can get all the stuff you need about what's happening with the latest Microsoft technologies.
So check out the site and we'll keep you posted over the next few weeks as we start filling it with as much stuff as we can lay our hands on.