IndyPass is the Indianapolis Professional Association for SQL Server.  Tonight they celebrated their 3rd Birthday Bash. The speaker was Tom Pizzato, Data Platform Architect, at Microsoft.

John Magnabosco kicked us off with the introduction.  He gave the usual announcement events, board elections, etc . John ended by asking for job announcements, but was interrupted by the famous Buck Foley.  Buck is a “Matt Foley” impersonator (the ‘van down by the river’ character played by Chris Farley on Saturday Night Live), and the actor is a ‘Database Guy’ by profession.  He brings his own flavor of humor and applies it to SQL technologies. Buck seemed to be under the impression that he was the main speaker and not Tom Pizzato who he called “Pizza-Toe”.  He winged his talk this time, but it was still humorous and set a light hearted tone for the rest of the meeting.

Tom’s talk was broken into two sections: a detailed talk about new features followed by a series of demos.  The talk mainly went over all of the new features and changes in SQL Server 2008.

Tom’s Talk

Amazing Journey

Tom has worked with SQL Server for close to 19 years.  He started his talk by taking us a down memory lane by showing some of the original disks for the first version of SQL Server back when it was just a OS2 port of Sybase.  He also  picked fun of the fact that there was a launch event for a product that has not yet been released. A few items of interest from his introduction were:

• Microsoft is still on track for a Q3 launch of SQL Server 2008, but skill couldn’t give us a more specific date yet.  The next version of SQL Server will be in 2010 (at least that is where it is planned).
• FileStream data cannot be saved to a separate machine in SQL Server 2008.  it is still limited to the machine running SQL Server 2008.  Microsoft knows this is a limitation, but they chose to just get the feature out there first.

Protect your Information

• Secure Access
  • Strong Authentication
  • Granular Authorization
  • Transparent Data Encryption (TDE)
  • External Key Management
• Protect System
  • Secure Deployment
  • Secure by default: This ones  is going to be a pain for some people.  Secure by default means everything is turned off by default.  Microsoft is taking this mind set into all of its major projects.  I have talked about this before in terms of Wcf, but we also see it in Windows Server 2008 where almost no features are installed by default.

Ensure Business Continuity

• Ensure Availability
  • Mirror your data: Automatic repair of primary from backups
  • Failover clustering
  • Replicate your data
• Minimize Downtime
  • Online Operations: More to help you keep your system up including, hot swapping CPUs while running in production
  • Add system resources
  • Reduce Recovery time
• Concurrent Access
  • Database Snapshots: Not many changes here
  • Snapshot Isolations

Predictable Response

• Optimize Performance
  • Efficient Data Storage: Finally Compression at the data and backup levels. in SP2 for SQL Server 2005, the vardecimal datatype was introduced which helped open the doors for this feature.
  • Prescriptive Guidance
• Analyze Performance
  • Data Collection: Collect reports instead of wiping them
• Predict Performance
  • Plan Freezing: Allow you to save query execution plans to xml that can be run on other machines.
  • Resource Governor: Set up resources into pools and assigned users to them.  Such as giving 70% CPU and memory to a power users and  rest to non-power users.

Reduce Management Cost

• Flexible Administration
  • Visual Management: Management Studio improvements, Powershell integration, etc.
  • Automate Operations
• Manage centrally
  • Enterprise Policies
  • Monitor Compliance
• Monitor Health
  • Management Reports
  • Performance Tuning Tools

Simplify Development

• Easy Access
  • Model Entities: Models can be built off multiple tables such as a “User” entity that spans multiple tables.  LINQ can then be used to query over this data. This is what is formally called LINQ to Entities.
  • Integrated Query: Most of the LINQ stuff is slightly slower, but it is a trade off of getting the code done faster development vs. performance.  This is also know as LINQ to SQL.
  • Comprehensive connectivity
• Program data logic
  • Time aware applications: Introduced separate Date and Time fields.
  • Pass data results
• Sync your data
  • Local data storage
  • Offline Synchronization

Store your information

• Unstructured Information
  • File Storage
  • Search Text
  • Extensible Types
• Geospatial Information
  • Spatial Standards
  • Visualize Location
• Structured information
  • Model complex hierarchies: No more need to use CTEs to recursively query data to build hierarchy trees.  The new Hierarchy data type can store these values
  • Enhanced Partitioning

Integrate and Manage data

• Build Faster
  • Connect to data
  • Cleanse Data
  • Integrate data
• Manage Efficiently
  • Efficient Data Storage
  • Mange mixed workloads
  • Streamline Aggregates
• Scalable Performance
  • DW Query Optimizations: Lots of optimizations in performance of Star joins
  • Enhanced Partitioning: Easier to align partitions with their indices.

Deliver Relevant Reports

• Author reports
  • Enhanced Designers: Licensed technology from Dundas to create better and more professional looking reports.
  • Flexible Layout: Introduced Tablixs.
• Manage reports
  • Enterprise Platform: SSRS was rewritten to not require IIS and scale better in the enterprise
  • Server-based Deployment
• Deliver reports
  • Rich Visualizations
  • Render in office
  • Share reports

Drive Actionable Insight (Analysis Services)

• Build Analytical Solutions
  • Scalable Platform
  • Prescriptive Guidance
• Extend Reach
  • Manage KPIs: How many Microsoft tools report KPIs?
  • Predictive Analysis
• Deliver Insight
  • Work in Office: Data mining add-ins for office allow you to mine and report on data directly from Office tools
  • Business view of data

Tom’s Demos

Demos were done using SQL Server 2008 CTP 6.  Tom had 9 demos to show but only got to 6 of them.  The demos were run from a custom application that had the queries and results all displayed in a custom window.  This was obviously a pre-canned set of demos or at least a pre-canned tool, but it was a great way to display these features.  I know it took a lot of time to put together,

Auditing

• Auditing performance can eat up to 10%
• Audit logs are stored in a separate file on the disk.  These files are not xml or directly human readable, but at least they can’t be queried like any other table.
• Audit logs can be maintained by setting up rollover files and file sizes

Compression

• Compression types can be set at the row or page levels

Powershell

• Only works for the relational engine
• Can right click on teh database in management studio and say “Open Powershell”
• Once in powershell you can say “dir”and get the list of database objects including being able to drill into tables, users, etc. 
• You could even run more complex Powershell queries such as the ability to get tables where row count is > 1000 like:
  • dir | where < $._RowCount -gt 1000
• You can pipe results out to HTML or other mediums

Freezing Plans

• Gives you the ability to take a query execution plan and save it to XML
• Can then specify when plan to use when executing a query

New DateTime Datatypes

• Separate Date and Time datatypes
• A new DateTimeOffset datatype supports years 0001 to 9999 as well as storing time zone information
• A new DateTime2 datatype supports years 0001 to 9999

Table Value Parameters and Merge

• This was the last feature dropped from SQL Server 2005
• A new MERGE statement allows merging of rows from multiple tables. You can choose to update matched rows, insert unmatched rows, or delete unmatched rows from the primary table.

Prizes

I know a lot of people probably only came here for the prizes, and there were some great giveaways again this year.  There was an Xbox, a 30 gig Zune, two 8 gig Zunes, and a slew of books and other swag.  As usual I didn’t win anything, even though the odds were pretty good.  But I came here for the event more than the prizes

Final Thoughts

I do miss having Bill Baker at the Birthday Bashes, but Tom has been filling in nicely. He is a good speaker, is able to answer most questions, and is very competent in his skills. He was asked some detailed questions about LINQ to SQL and LINQ to Entities that he couldn’t answer, but he wasn’t afraid to punt on them and commit to getting Joe Healy to come to talk about LINQ at a future meeting.  I am looking forward to future events at IndyPASS.

We prefix all of our database objects with a namespace scope.  Objects begin with "ININ_" followed by an application prefix.  So all database objects relating to to an application called "ABC" will begin with the prefix "ININ_ABC_".  This namespacing really helps out when multiple applications share the same database.

Advantages of this approach include:

1. Identifying what application an object belongs to
2. Finding all objects for an application in the sysobject tables

But, this post isn’t about how to name database objects…

To test our database scripts, we normally drop a database and recreate through our build process. However, when multiple applications share the same database, we need to only drop the database objects relating to that application. 

The stored procedure below takes in an application prefix and drops all database objects whose name starts with that prefix.  This script will work for any prefix you might use to segregate your objects. 

This was written for SQL Server 2005, but it could be easily modified to work with older versions.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N‘DropApplication’) AND OBJECTPROPERTY(id, N‘IsProcedure’) = 1)
    DROP PROCEDURE [dbo].[DropApplication]
GO

CREATE PROCEDURE [dbo].[DropApplication]
(
    @ApplicationPrefix varchar(64)
)
/************************************************************/
– DropApplication
– 
– Date Created: 8/19/2007
– Created By:     Dan Rigsby (www.danrigsby.com)
– 
– Purpose:         Deletes all of the sysobjects in the database
–                 for an application based on sysobject name prefix
–
– Example:         To delete all sysobjects for an application called
–                 ISupport whose object are prefixed with ‘ININ_ABC’ call: 
–                 EXEC DropApplication ‘ININ_ABC’
/************************************************************/

AS
BEGIN
    DECLARE @Statement AS varchar(4000)
    DECLARE @ParentID AS int
    DECLARE @ReferenceID AS int
    DECLARE @ParentName AS varchar(256)
    DECLARE @ObjectName AS varchar(256)
    DECLARE @ObjectType AS varchar(5)

    – Step 1: Delete procedures
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + ‘%’ AND [type] in (N‘P’, N‘PC’, N‘X’, N‘RF’)
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC (‘DROP PROCEDURE ‘ + @ParentName)
        FETCH NEXT FROM cr1 INTO @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    – Step 2: Delete functions
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + ‘%’ AND [type] in (N‘IF’, N‘FN’, N‘TF’)
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC (‘DROP FUNCTION ‘ + @ParentName)
        FETCH NEXT FROM cr1 INTO @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    – Step 3: Delete views
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + ‘%’ AND [type] in (N‘V’)
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC (‘DROP VIEW ‘ + @ParentName)
        FETCH NEXT FROM cr1 INTO @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    – Step 4: Delete FKs
    DECLARE cr1 CURSOR FOR
    (
        SELECT [id], [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + ‘%’ AND [type] in (N‘U’)
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentID, @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name]
            FROM sysobjects
            WHERE [parent_obj] = @ParentID AND [type] IN (N‘F’)
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName
        WHILE (@@fetch_status = 0)
        BEGIN
            EXEC (‘ ALTER TABLE ‘ + @ParentName +
                ‘ DROP CONSTRAINT ‘ + @ObjectName)

            FETCH NEXT FROM cr2 INTO @ObjectName
        END
        CLOSE cr2
        DEALLOCATE cr2

        FETCH NEXT FROM cr1 INTO @ParentID, @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    – Step 5: Delete any FK constraints in other tables that reference our tables
    DECLARE cr1 CURSOR FOR
    (
        SELECT [id]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + ‘%’ AND [type] in (N‘U’)
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ReferenceID
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name], [type], [parent_object_id]
            FROM sys.foreign_keys
            WHERE [referenced_object_id] = @ReferenceID
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName, @ObjectType, @ParentID
        WHILE (@@fetch_status = 0)
        BEGIN
            – Get parent name
            SELECT @ParentName = [name]
            FROM sysobjects
            WHERE [id] = @ParentID

            SELECT @Statement =
                CASE (@ObjectType)
                    WHEN ‘F’ THEN
                        ‘ ALTER TABLE ‘ + @ParentName +
                        ‘ DROP CONSTRAINT ‘ + @ObjectName
                END
            EXEC (@Statement)

            FETCH NEXT FROM cr2 INTO @ObjectName, @ObjectType, @ParentID
        END
        CLOSE cr2
        DEALLOCATE cr2

        FETCH NEXT FROM cr1 INTO @ReferenceID
    END
    CLOSE cr1
    DEALLOCATE cr1

    – Step 6: Delete tables, triggers, FKs, and constraints
    DECLARE cr1 CURSOR FOR
    (
        SELECT [id], [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + ‘%’ AND [type] in (N‘U’)
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentID, @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name], [type]
            FROM sysobjects
            WHERE [parent_obj] = @ParentID
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName, @ObjectType
        WHILE (@@fetch_status = 0)
        BEGIN
            SELECT @Statement =
                CASE (@ObjectType)
                    WHEN ‘TR’ THEN
                        ‘ DROP TRIGGER ‘ + @ObjectName
                    ELSE
                        ‘ ALTER TABLE ‘ + @ParentName +
                        ‘ DROP CONSTRAINT ‘ + @ObjectName
                END
            EXEC (@Statement)

            FETCH NEXT FROM cr2 INTO @ObjectName, @ObjectType
        END
        CLOSE cr2
        DEALLOCATE cr2

        EXEC (‘ DROP TABLE ‘ + @ParentName)

        FETCH NEXT FROM cr1 INTO @ParentID, @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    – Step 7: Drop users
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sys.database_principals
        WHERE [name] LIKE @ApplicationPrefix + ‘%’
            AND [type] IN (N‘S’)
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ObjectName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC (‘DROP USER ‘ + @ObjectName)
        FETCH NEXT FROM cr1 INTO @ObjectName
    END
    CLOSE cr1
    DEALLOCATE cr1

    – Step 8: Drop roles
    DECLARE cr1 CURSOR FOR
    (
        SELECT [principal_id], [name]
        FROM sys.database_principals
        WHERE [name] LIKE @ApplicationPrefix + ‘%’
            AND [type] in (N‘R’)
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentID, @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name]
            FROM sys.database_role_members DRM
                INNER JOIN sys.database_principals DP
                    ON DRM.[member_principal_id] = DP.[principal_id]
            WHERE [role_principal_id] = @ParentID
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName
        WHILE (@@fetch_status = 0)
        BEGIN
            EXEC
            (‘
                EXEC dbo.sp_droprolemember ‘” + @ParentName + ”‘, ‘” + @ObjectName + ”‘
            ‘)
            FETCH NEXT FROM cr2 INTO @ObjectName
        END
        CLOSE cr2
        DEALLOCATE cr2

        EXEC (‘DROP ROLE ‘ + @ParentName)

        FETCH NEXT FROM cr1 INTO @ParentID, @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1
END
GO

Database objects are dropped in the following order:

1. Procedures
2. Functions
3. Views
4. Foreign Keys
5. Foreign Key constraints in other tables
6. Triggers and Constraints
7. Tables
8. Users
9. Roles