Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

November, 2007

  • SQL Server 2008 - Transparent Data Encryption

    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

    Technorati Tags: ,
  • SQL Server 2008- Backup Compression

    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!

  • SQL Server 2008 Resource Governor

    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)

    or

    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.

  • SQL Server 2008 Spatial Data Types

    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:

    • If you try and stuff a polygon into SQL server 2008 that is larger than a hemisphere you'll get and ArgumentException error.
    • If the result of one of the methods returns a result that is larger than a hemisphere then the method will return NULL.

    I am guessing that this is also something to do with the standards?

  • Depend on Dependencies in SQL Server 2008

    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:

    • Schema Bound, where the object A cannot be deleted because object B depends on it.
    • Non-Schema Bound, where Object A can be deleted or may not even have been created, however Object B still depends on it.

    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

    -- Create MyView over the synonym
    CREATE VIEW dbo.MyView AS
        SELECT Name,ProductNumber FROM dbo.MyProduct

    -- 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

    image 

    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. 
    image

    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 - monitoring reports

    imageI 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:

    image

    and then stopped the trace and looked at the relevant event:

    image

    as you can see the SQL looks long but it's mainly columns, there are only four tables involved:

    • sys.dm_exec_sessions
    • sys.dm_exec_connections
    • sys.dm_exec_requests

    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.

    Technorati Tags: ,
  • SQL Server 2008 T-SQL - you learn something new everyday

    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

    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.

    Technorati Tags: ,
  • Time for a look at SQL Server 2008 CTP5

    barcelona 07 020

    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:

    • The new spatial data,type indexes and functions to enable location intelligence.
    • Database encryption to protect the database 'at rest'.
    • The new filestream data type which extends allows for flexible storage of unstructured data.
    Technorati Tags: ,
  • SQL Server 2000 security

    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:

    • Upgrade to SQL Server 2005 if possible. The current version is secure by design, and is pretty well locked down by default.
    • Make sure your patches are up to date. You should be running SQL Server 2000 SP4 (8.0.2039). If you don't know hot to tell what service pack is applied then follow this link.
    • Network administrators should ensure that perimeter access is configured properly, and that interior hosts are not exposed to unwanted traffic. In most cases, that means blocking access to port 1433/TCP from outside the network perimeter.
    • Apply the advice SQL Server 2000 – Security Best Practices Checklist (refer to Firewalls and Strong passwords section).   [Note: The SQL Server 2000 SP3 best practices are valid for SQL Server 2000 SP4].

    Of course if you do want a rapid career change then please ignore this.

    Technorati Tags: ,
  • Evaluating SQL Server 2008 Betas

    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  

    Technorati Tags: ,,
  • SQL Server - Monitoring Reports take 2

    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.

  • What's on the telly? - Microsoft TV

    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:

    imageimage

    image image

    imageIf you have content you would like to be on TV then get back to me and if not just tune in and turn on.

  • IT Pro Momentum

    IT Pro Momentum 

    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:

    Usage

    Microsoft Support

    Evaluation

     Technical Content & Managed Forums

    Planning

     TechNet+ Direct Subscription

    Pilot & Deploy

     Premier Support Services (PSS) Requests

    The Microsoft technologies that apply to the Momentum program are as follows:

    Tracks

    Technologies

    IT Pro Evangelist Contact

    Server OS

     Windows Server2008: Active Directory

    Windows Server2008:Virtualization

    Windows Server2008: Networking

    James O’Neil

    Database

    SQL Server 2008

    Andrew Fryer

    Security

    ForeFront: Client

    ForeFront: Server

    ForeFront: Edge

    Windows Server 2008: NAP

    Winodws Vista: Secure Deployment

    Stephen Lamb

    Web

    Windows Server 2008: IIS7

    James O’Neil

    Web

    Sharepoint Server 2007

    Viral Tarpara

    Management

    Windows Server 2008: Powershell

    James O’Neil

    Management

    System Center:  Essentials

    System Center:  Configuration Manager

    System Center:  Operations Manager

    Stephen Lamb

    Specialized Server

    Windows Compute Cluster 2003

    James O’Neil

    To find out more contact the relevant IT Pro Evangelist so we can help you get the most out of latest Microsoft releases.

     

  • BI and Enterprise Search

    Star TrekViral 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.

  • SQL Server 2008 Launch & Ship

    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,

    mini warthog   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

    Technorati tags:
  • What we did in skool today

    image

    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.

    Technorati tags: ,
  • TechNet Edge Takes Off

    image

    There is a new Microsoft Site for that's just been launched, TechNet EdgeBug . 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. 

    Technorati Tags:
  • Compliance

    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!imageclip_image001

     

     

    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.  

    Technorati tags: ,
  • Another Quiet Day for SQL Server DBA's

    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.

    Technorati tags: ,
  • The North South Divide

    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.

    Technorati Tags: