So what is available in Denali, random thoughts while I tour the UK

Testas — Thu, 19 May 2011 12:51:00 GMT

I am at the beginning of touring user groups around the UK so on train journeys back from a user group meetings I have had time to write out my thoughts on some of the new features that are available in SQL Server C TP 1.

The vision from Microsoft regarding this release looks the same as it was in SQL Server 2008 R2

Empowering IT – Making it easier for IT departments to manage the scalability and availability of SQL Server

Dynamic Development - Making it easier for database developers to develop SQL Server databases in a consistent application

Pervasive Insight - extending the reach of BI to include business users as well as IT professionals and developers

What is notable is the fact that Microsoft will scale SQL Server to different platforms including

Appliances - SQL Server editions including Parallel Data Warehouse and Fast track that have a dedicated purpose using SQL Server

Box - the traditional implementation that most will be familiar with running a specific edition of SQL Server on a server.

Cloud - SQL Azure, a cloud-based service offering data storage capabilities

The capabilities that are available on each platform will vary, in this blog I will concentrate on the new features that will be available on the box with the release of SQL Server Denali CTP ( Community Technical Preview) 1 available from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6a04f16f-f6be-4f92-9c92-f7e5677d91f9

In no particular order, the following are a summary on some features of SQL Server Denali CTP 1

HADRON

One features that addresses the scalability and availability vision is HADRON or High Availability Disaster Recovery Always ON. This is a technology that tightly integrates the database mirroring architecture with Windows Clustering. The key benefit that this has over database mirroring is that up to 5 secondary servers can be defined, which are readable and can be queried. Like database mirroring, endpoints are used to connect between primary /secondary instances like in Database mirroring. Furthermore as the architecture is very similar to database mirroring, Database Mirroring configuration from SQL Server 2005 onwards can be upgraded to HADR.

CREATE SERVER ROLE

You now have the ability to create user defined server level role on an instance of SQL Server. This provides a greater degree of granularity when setting security at an instance level. As a result the sp_addsrvrolemember and sp_dropsrvrolemember are being deprecated and being replaced with the ALTER SERVER ROLE instead. Server roles can be created using Transact-SQL or within SQL Server Management Studio.

Startup Options

You can now graphically configure SQL Server startup options within the instance properties in SQL Server Configuration Manager

Contained databases

Contained databases are databases that contain all the objects and metadata for a database without having dependencies on the SQL Server instance. This makes it easier to move databases between different instances of SQL Server. Contained databases can be defined in one of three configurations:

NONE – This means that the database does have dependencies on the SQL Server instance. This is how all databases created on a SQL Server instance are defined by default
FULL – As yet not available in Denali CTP 1, my understanding is that objects defined in these databases do not have dependencies on the SQL Server Instance
PARTIAL – Some objects have dependencies on the SQL Server instance, other objects are contained within the database

Transact-SQL additions and enhancements

There are a number of T-SQL additions and enhancements in this release of SQL Server including Paging, SEQUENCE and THROW

Paging

The ORDER BY clause has been enhanced to include the ability to page results. Example from Books Online include:

USE AdventureWorks2008R2;
GO
--Using Order by to return all rows sorted by the column DepartmentID.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID;

-- Use OFFSET to skip the first 5 rows from the sorted result set and return all remaining rows.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 5 ROWS;

-- Use OFFSET to skip 0 rows and return only the first 10 rows from the sorted result set using FETCH --NEXT.

SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID     OFFSET 0 ROWS    FETCH NEXT 10 ROWS ONLY;

-- OFFSET and FETCH values can be defined within variables

DECLARE @StartingRowNumber tinyint = 1      ,
@FetchRows tinyint = 8;
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID ASC     OFFSET @StartingRowNumber ROWS FETCH NEXT @FetchRows ROWS ONLY;
GO

SEQUENCE

A feature available to ORACLE database professionals is now available to SQL Server database professionals. SEQUENCE enables you to define a sequence of numbers which is stored and retained in memory. My good friend Aaron Bertrand has two excellent blog pieces on how to implement SEQUENCE and the performance impact that I would recommend:

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/11/sql-server-11-denali-using-sequence.aspx

http://sqlblog.com/blogs/aaron_bertrand/archive/2010/12/13/sql-server-v-next-denali-take-one-part-sequence-and-one-part-set.aspx

THROW

You now have greater flexibility with the TRY…CATCH Transact SQL statements with THROW being introduced in SQL Server Denali. This raises an exception and then transfers execution to a CATCH block.

SSIS enhancements

I will be blogging more on these changes in the future however to areas stand out regarding SSIS which will make life a lot easier

Usability

The usability of SSIS has been improved within Business Intelligence Development Studio. The ability to undo and redo changes in the designer is a small change, but of massive benefit while developing SSIS packages. Copy objects between packages is a lot easier and you can customise the toolbox more easily.

Deployment

There is a new project deployment methodology that will deploy SSIS at a project level rather than the package level that we have been previously used to. The package method of deployment still exists and is referred to as the legacy deployment model. The following tables is taken form Technet Wiki
http://social.technet.microsoft.com/wiki/contents/articles/project-deployment-overview-in-sql-server-quot-denali-quot-ctp1-ssis.aspx
that shows the differences and similarity between the project deployment methodology and the legacy deployment methodology

When Using the Project Deployment Model...	When Using the Legacy Deployment Model...
A project is the unit of deployment.	A package is the unit of deployment.
Parameters are used to assign values to package properties.	Configurations are used to assign values to package properties.
A project, containing packages and parameters, is built to a project deployment file (.ispac extension).	Packages (.dtsx extension) and configurations (.dtsConfig extension) are saved individually to the file system.
A project, containing packages and parameters, is deployed to the Integration Services catalog on an instance of SQL Server.	Packages and configurations are copied to the file system on another computer. Packages can also be saved to the MSDB database on an instance of SQL Server.
CLR integration is required on the database engine.	CLR integration is not required on the database engine.
Environment-specific parameter values are stored in environment variables.	Environment-specific configuration values are stored in configuration files.
Projects and packages in the catalog can be validated on the server before execution. You can use SQL Server Management Studio, stored procedures, or managed code to perform the validation.	Packages are validated just before execution. You can also validate a package with dtExec or managed code.
Packages are executed by starting an execution on the database engine. A project identifier, explicit parameter values (optional), and environment references (optional) are assigned to an execution before it is started.	Packages are executed with the dtExec and DTExecUI execution utilities. Applicable configurations are identified by command-prompt arguments (optional).
During exeuction, events that are produced by the package are captured automatically and saved to the catalog. You can query these events with Transact-SQL views.	During execution, events that are produced by a package are not captured automatically. A log provider must be added to the package to capture events.
Packages are run in a separate Windows process.	Packages are run in a separate Windows process.
SQL Server Agent is used to schedule package execution.	SQL Server Agent is used to schedule package execution.

BISM

BISM or Business Intelligence Semantic Model is one of the major additions of SQL Server Denali that will change the way we potentially work with Business Intelligence. The most common misconception of BISM is that people think that it is specific to Analysis Services. It is not, it is hosted by SQL Server Analysis Service, however Crescent (the new adhoc reporting tool complementing SSRS) must have a BISM model in order to work. A second misconception is that the introduction of BISM marks the end of traditional Analysis Services solutions that uses UDM. Again this is not the case. UDM will still play an important role that can be summarised by the following graphic which I do not know who created but I believe came from a TechED session

The BISM is designed to combine the UDM model used by SSAS with the SMDL model used by previous Report Builder applications, representing the model in a relational manner while getting the BI sophistication from UDM. However BISM will evolve over time and as UDM is a more mature model you will still see the relevance of UDM within organisations for years to come. Specifically if you require advance calculation that cannot be currently handled by DAX, (the new querying language for PowerPivot that extends to BISM). Then you will be likely to use UDM. BISM is designed to store the data in the VertiPaq in-memory column store. VertiPaq uses compression algorithms along with multi-threaded query processing that delivers fast performance in retrieving huge data volumes. So much so that this technology is also being introduced in the Database Engine in SQL Server Denali with the new feature of columnar indexes - this is Vetipaq again. So if you have data that cannot fit into the memory, then UDM becomes a consideration. I say that as Vertipaq can be configured to query against data sources therefore preserving the memory that vertipaq uses, but that is for another blog.

To quote the SSAS team from the following blog http://blogs.technet.com/b/dataplatforminsider/archive/2010/11/12/analysis-services-roadmap-for-sql-server-denali-and-beyond.aspx

“You might ask – Why do we have two types of models in Analysis Services? Which one should I use? Is the UDM going to be deprecated now that we have the BISM? The answer is NO! Analysis Services just got a lot better in SQL Server “Denali” with the BISM and VertiPaq and DAX! However the UDM is a mature and industry leading technology and is here to stay. Let me repeat – UDM (OLAP) models are not being deprecated!”

So there are exciting times in this area and I watch and partake with interest as it develops through time

Crescent

In February 2010, I was sat in a room with two other MVPs (Microsoft Valuable Professionals) in Redmond as 5 members of the SSRS team wanted to show us the future of SSRS adhoc reporting . While the demonstration was being given, the MVPs where like kids in a sweetie shop. I don’t mind admitting I was drooling, and it has been very difficult to keep quiet about it since then. Like BISM, Project Crescent is designed to complement the existing report designer in Business Intelligence Development Studio and Report Builder applications that is provided by SQL Server Reporting Services. It relies on a BISM model to work, but its compelling selling point is that if present reports in a Silverlight front end browser that is extremely dynamic. For example, rather than looking at a static chart, the chart can be replayed like a DVD over time to see the result grow within the chart. This is certainly a very useful tool that can be stored in a SharePoint gallery, and while it may be touted as an adhoc reporting tool, I envisage BI professionals using it to create some standard reports that are currently created in Business Intelligence Development Studio. Check out http://www.youtube.com/watch?v=FfRpfCav9hg for your first taster…its awesome!

So there are some of my thoughts on what is available in SQL Server Denali CTP1, I know that I have missed out some feature but I hope that future blogs will give you more information about specific topics

Thanks

Chris

Chris Testa-O'Neill : Crescent, HADRON

So what is available in Denali, random thoughts while I tour the UK