Well what can I say!  After five years of waiting SQL Server 2005 was officially launched on the 7th of November.  All versions of SQL Server 2005 are now available for download from MSDN and all resale channels should have the product available as of the 30th of November.  To compare the features of the various editions of SQL Server 2005, refer to http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx.

 

SQL Server 2005 has undergone 40 times more stress-testing hours than SQL Server 2000,  During this testing over 1,000 customer databases have been upgraded successfully to SQL Serve 2005 and over 1 million people have downloaded the betas and Community Technology Preview editions of SQL Server making SQL Server 2005 one of the most tested and robust DBMS platforms in the market place today.  The following customer testimonials from some of the largest organizations in the world backup the robustness of this release http://members.microsoft.com/CustomerEvidence/Search/AdvancedSearchResults.aspx?Flag=0&AndTaxID=20363.

 

To help more organizations take advantage of SQL Server 2005’s enterprise-class scalability ,reliability and Business Intelligence functionality Microsoft have release the Competitive Migration Promotion. This promotion is open to customers (until the 30th of June 2006) who want to migrate to SQL Server 2005 from costly platforms such as Oracle and DB2.  The promotional pricing includes the following:

 

• 50% discount on SQL Server 2005 Enterprise Edition license with the purchase of the regularly priced Software Assurance license

• 50% discount on SQL Server client access licenses with the purchase of the regularly priced Software Assurance license

• 25% discount on SQL Server Standard Edition license with the purchase of the regularly priced Software Assurance license

 

So now is the perfect opportunity to review your current DBMS platform against SQL Server’s price performance metrics and ability to scale to in excess of 1, 000, 000 transactions per minute.  If you wish to learn more about the Competitive Migration Promotion or any of the other features of SQL Server 2005 then WARDY IT Solutions, as an early adopter and Registered Microsoft Partner, will be able to assist.

 

 

Should you have any suggestions or feedback on how we can improve the newsletter or content that you would like covered, please do not hesitate to email newsletter@wardyit.com.

 

Happy Reading

 

 

Peter Ward

WARDY IT Solutions Chief Technical Architect

Phone: +61 7 3856 5411     Mobile: +61 0 403 177 761

In this Issue:

SQL Server 2005 Feature Pack

SQL Server 2005 Skills Assessment

MSDE Query Governor

Service Control Manager

Scalable Shared Databases

SQLDownUnder Podcast 9

SQL Server TechNet Presentations

XML Indexes

Implementing Row and Cell Level Security

DMO of the Month

T-SQL of the Month

SQL Server Tip of the Month

 

SQL Server 2005 Feature Pack

With the release of SQL Server 2005 ,Microsoft has also released the SQL Server Feature Pack.  The Feature Pack is a collection of components that compliment SQL Server 2005 and includes some new functionality not shipped with the product such as data providers for DB2 and SAP and some additional backward compatibility tools for SQL Sever 2000.  The Feature Pack can be download from http://go.microsoft.com/fwlink/?LinkId=54583.

 

 

SQL Server 2005 Skills Assessment

If you are looking to identify the areas that you need to get up to speed with in order to make the transition to SQL Server 2005 as smooth as possible then the Microsoft Skills Assessment is the perfect place to start.  By completing a Skills Assessment you will receive a personalized Learning Plan including Microsoft Official Curriculum courses, Microsoft Press books and Microsoft TechNet resources to help bring your existing SQL Serer skills up to speed.

 

 

MSDE Query Governor

One of the most misunderstood components of MSDE is the MSDE Query Governor.  The MSDE Query Governor kicks in at above eight concurrent operations and not five connections as is commonly stated in many publications including Microsoft articles.  The reason five connections are quoted, is to allow for three background processes to be running such as an Auto Shrink Operation.   When more than eight connections are detected, a delay is inserted in each logical page read or write on the data files to create a performance ceiling.  For more details regarding the MSDE Query Governor refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa2_0ciq.asp.

 

 

Service Control Manager

One of the little features of SQL Server 2000 which is missing from SQL Server 2005 is the SQL Server Service Control Manager.  The Service Control Manager is the SQL Server icon in the task tray that allows you to view and control the SQL Server services.  Jasper Smith who developed Reporting Services Scripter (http://www.sqldbatips.com/showarticle.asp?ID=62) has developed a Service Control Manager similar to the one included with SQL Server 2000 which can be downloaded from http://www.sqldbatips.com/showarticle.asp?ID=62.

 

 

Scalable Shared Databases

Scalable Shared Databases is a feature of SQL Server 2005 Enterprise Edition that has not been heavily publicized as a result of being a late-breaking feature.  The scalable shared database feature allows you to attach a read-only reporting database to multiple server instances over a storage area network (SAN).   A reporting database is a read-only database that is built from one or more production databases that are used exclusively for reporting purposes.  For more details regarding the scaling-out of reporting databases refer to the following Knowledge Base article  http://support.microsoft.com/?kbid=910378.

 

 

SQLDownUnder PodCast 9

Greg Low (http://msmvps.com/greglow/) recently interviewed Jim Gray (http://research.microsoft.com/~Gray/) for what is becoming a series of PodCasts with some of the most influential figures in the SQL Server community.  Jim Gray has been a researcher at Microsoft since 1995 and has worked on various projects that have influenced the features and architecture of SQL Server.  In this interview there are some great questions about SQL Server and the future of SQL server including the CLR in SQL Server and LINQ.  All of the SQLDownUnder products including this show can be downloaded from http://www.sqldownunder.com/.

 

 

SQL Server TechNet Presentations

As part of the release of SQL Server 2005 a series of TechNet sessions have been made available online.  These presentations are from some of the best SQL Server Speakers including Kimberly Tripp and Jesper Johansson.  If you have not seen Jesper present before on SQL Server security then you should not miss this presentation.  All of the ShowTime Sessions can be downloaded or viewed online from http://www.microsoft.com/australia/ShOwtime/result_search.aspx?track=3.

 

 

XML Indexes

Bob Beauchemin (http://www.sqlskills.com/blogs/bobb/) has written an excellent whitepaper on XML indexes in SQL Server 2005.  The article illustrates using the relational query engine in SQL Server 2005 to make a single query plan for the SQL and XQuery parts of a query as well as how to make the implementation of XML queries faster and easier to predict and tune.  The whitepaper can be found at:

http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsql90/html/xmlindexes.asp

 

 

Implementing Row and Cell Level Security

The following Microsoft whitepaper details how SQL Server 2005 can be used in order to achieve Row and Cell Security using SQL Server 2005.  The article details the processes that can be used to allow data access in environments where certain data must always remain classified.  The whitepaper can be downloaded from

http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx#EFAA.

 

 

 

DMO of the Month:

 

The following VBScript can be used to determine the account that the MSSQLServer service is running under the context of.  This will assist in auditing all you SQL Server service accounts to ensure that they are running under the context of an account with minimal privileges:

 

Option Explicit

 

' File containing list of servers to check

Const SERVER_LIST = "c:\serverlist.txt"

' File to write the results to

Const OUTPUT = "c:\output.txt"

Const ForReading = 1

Const ForWriting = 2

 

Dim oFSO

Set oFSO = CreateObject("Scripting.FileSystemObject")

 

Dim oSQLServer

Set oSQLServer = CreateObject("SQLDMO.SQLServer2")

oSQLServer.LoginSecure = True

 

Dim oFSORead

Set oFSORead = oFSO.OpenTextFile(SERVER_LIST, ForReading, False)

 

Dim oFSOWrite

Set oFSOWrite = oFSO.OpenTextFile(OUTPUT, ForWriting, True)

 

Dim server

Dim result

 

' Loop through the Serverlist file and read each server in the file

Do While Not oFSORead.AtEndOfStream

      server = oFSORead.ReadLine

    oSQLServer.Connect server

     

      ' Output in a commaa deleimted format the SQL Server instance and the Startup Account

      ' ie. the MSSQLServer Service Account

      result = server& ", " & oSQLServer.StartupAccount

      oFSOWrite.WriteLine result

    

    oSQLServer.DisConnect

Loop

 

oFSOWrite.Close

oFSORead.Close

 

Set oFSO = Nothing

Set oSQLServer = Nothing

 

 

T-SQL of the Month:

 

Although typically the pivoting of data should be performed by the presentation layer there is sometimes a need to pivot the data at the data-tier.  The example below illustrates how data can be pivoted using TSQL:

 

CREATE TABLE #Sales

      (

            SaleYear SMALLINT,

            Quarter  TINYINT,

            Sale  NUMERIC(7, 2)

      )

 

INSERT #Sales SELECT 2004, 4, 19000

INSERT #Sales SELECT 2004, 4, 1000

INSERT #Sales SELECT 2005, 1, 5000

INSERT #Sales SELECT 2005, 2, 10000

INSERT #Sales SELECT 2005, 2, 5000

INSERT #Sales SELECT 2005, 3, 2000

INSERT #Sales SELECT 2005, 3, 2000

INSERT #Sales SELECT 2005, 4, 10000

 

           

SELECT     SaleYear = s.SaleYear,

            SUM(CASE quarter WHEN 1 THEN sale ELSE 0 END) AS Q1,

            SUM(CASE quarter WHEN 2 THEN sale ELSE 0 END) AS Q2,

            SUM(CASE quarter WHEN 3 THEN sale ELSE 0 END) AS Q3,

            SUM(CASE quarter WHEN 4 THEN sale ELSE 0 END) AS Q4

FROM        #Sales s

GROUP BY saleyear

 

Returns:

 

SaleYear   Q1          Q2          Q3          Q4

--------   --------   --------   --------   --------

2004        0.00        0.00        0.00        20000.00

2005        5000.00    15000.00   4000.00    10000.00

 

(2 row(s) affected)

 

 

SQL Server 2005 has introduced the new PIVOT keyword and below is an example of the same Pivot query using the SQL Server 2005 PIVOT transformation:

 

SELECT     SaleYear, 1 AS Q1, 2 AS Q2, 3 AS Q3, 4 AS Q4

FROM        (SELECT SaleYear, Quarter, Sale

             FROM      #Sales) s

PIVOT      (SUM(Sale) FOR Quarter IN ([1], [2], [3], [4])) p

ORDER BY saleyear

 

 

SQL Server Tip of the Month

 

A commonly asked question with versions of SQL Server prior to 2005 was if the sa login could be renamed similar to the way that the Windows Administrator account can be renamed.  SQL Server 2005 has introduced a new security model which and apart from having more granular grantable permissions has also introduced the ALTER LOGIN command.  The ALTER LOGIN statement in SQL Server 2005 allows you to not only disable the sa login but also to rename it.  Below is an example of the statements to rename and disable the sa account:

 

-- Disable the sa Login

ALTER LOGIN sa DISABLE

 

-- Rename the sa login to notsa

ALTER LOGIN sa WITH NAME = notsa

If you are unable to view this document it can be downloaded from http://www.wardyit.com/newsletters/nov2005.htm

WARDY IT Solutions is a highly regarded IT Solutions provider specialising in Microsoft SQL Server.  You may not be aware though that WARDY IT Solutions is also a registered Microsoft Partner and has an established relationship with numerous hardware and software vendors, enabling us to provide competitive pricing on your next hardware or software acquisition.  For more information on how WARDY IT Solutions can assist with your next acquisition email: contact@wardyit.com.  WARDY IT Solutions has also recently partnered with Web Central in order to add web hosting and DSL plans backed by guaranteed Service Levels to the extensive list of services that we already provide.

You are receiving this monthly newsletter as a valuable associate of WARDY IT Solutions. Should you wish to unsubscribe then send a blank e-mail to newsletter_remove@wardyit.com. Alternatively if you have been forwarded this newsletter and would like to subscribe than send a blank email to newsletter_add@wardyit.com.