If you have only recently joined the WARDY IT mailing list you may not be aware that WARDY IT Solutions has recently started publishing a Weblog (aka Blog).  We will be using this Weblog to publish SQL Server news on a more frequent basis in order to compliment the existing monthly newsletter.  For more details regarding the Weblog refer to http://www.wardyit.com/blog/blog/archive/2005/04/03/1.aspx.

 

For those who are not already aware SQL Server Reporting Services Service Pack 2 has been released, for a complete list of fixes that this SP addresses refer to http://support.microsoft.com/?kbid=889640.  Also the April CTP for SQL Server 2005 has also been released in the last fortnight.  The excitement of this release continues to grow as the feature set continues to be refined.  One of the most exciting features in this release is Database Mirroring.  You will no longer need to purchase an Enterprise Edition licence in order to obtain the advanced recovery features of SQL Server, as Database Mirroring will be available in SQL Server 2005 Standard Edition.  Database Mirroring allows continuous streaming of the transaction log from a source server to a single destination server.  In the event of a failure of the primary system, applications can reconnect to the database on the secondary server almost immediately, without waiting for the recovery to finish.  Unlike failover clustering, the mirrored server is fully cached and ready to accept workloads because of its synchronized state.

 

 

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:

 

Solid Quality Learning Seminars

April MSDN Update

SQL 2000 Operations Guide

SQL Server 2005 Public News Group

Cache-Miss Events

SQL Server Documentation

SQL Server 2005 April CTP

CLR in SQL Server 2005

DMO of the Month

T-SQL of the Month

SQL Server Tip of the Month

Reporting Services Tip of the Month

Solid Quality Learning Seminars

For those who are living in the eastern states of Australia Itzik Ben-Gan and Kalen Delaney from Solid Quality Learning will be presenting two 5 day SQL Server Seminars latter this year.  If these names sound familiar, both of these presenters are regular contributors to the SQL Server Magazine (www.sqlmag.com) and Kalen authored Inside SQL Server 7 and 2000.  Itzik Ben-Gan will be presenting Advanced Transact-SQL Programming for SQL Server 2000 in Melbourne, Brisbane and Sydney in early May.  Kalen Delaney will be presenting SQL Server 2000 Internals and Performance Tuning in mid July in Melbourne and Sydney. http://www.solidqualitylearning.com/courses.aspx

 

 

April MSDN Update

If you are looking to get a head start on SQL Server 2005 then the complimentary April MSDN update should not be missed.  For attending the update you will receive a SQL Server 2005 Beta Resource Kit including web casts and hands on labs.  Greg Low (http://msmvps.com/greglow/) will be presenting on SQL Server 2005 security enhancements and Dan Glover will be presenting on SQL Server 2005 Integration Services (DTS) and Report Builder (Reporting Service).  For developers Dr Neil Rodyn will be presenting an Introduction to .Net Extreme Programming.  To register refer to http://www.microsoft.com/australia/msdn/training/aprilupdate.aspx

 

 

SQL 2000 Operations Guide

If you are looking for an Operation Guide for your SQL Server environment then the one produced by the Patterns and Practice Team is a great starting point, as it includes a set of SQL Server management practices based on current best practices ranging from Security Administration to Change Management:

http://www.microsoft.com/TechNet/prodtechnol/sql/2000/maintain/sqlops0.mspx

 

 

SQL Server 2005 Public News Group

If you are currently using SQL Server 2005 and are not an MSDN or BetaPlace member and are looking to post a question regarding the SQL Server 2005 Beta’s you can use the Public news group below to interact with the SQL Server product team members:

http://communities.microsoft.com/newsgroups/default.asp?ICP=sqlserver2005

 

 

Cache-Miss Events

As SQL Server professionals I am sure you are all aware of the impact of executing a Stored Procedure without the two part name <owner>.<object name>.  But what you may not be aware of is that a Cache-Miss will also occur when a Stored Procedure is executed with the case of the Stored Procedure being different to the case of the Stored Procedure name ie. Executing SP_ORDERS instead of sp_Orders causes a cache-miss.  Mike Metcalf has recently written a great article illustrating this little known fact http://www.sqlservercentral.com/columnists/mmetcalf/hitsandmisses.asp.

 

 

SQL Server Documentation

If you need to document a SQL Server Database then ApexSQL Doc is a great tool to assist with automatically generating professional looking documentation.  ApexSQL Doc generates a complied HTML help file with a contents, index and search functionality just like SQL Server Books On Line.  The tool can even be scheduled so that it can be run to capture schema changes and at US$249 a licence it will save many hours of documenting by hand.  http://www.apexsql.com/frame_purchase.htm

 

 

SQL Server 2005 April CTP

If you have not already heard the April CTP of SQL Server 2005 has been released.  Paul Flessner, Senior Vice President of the Server Applications at Microsoft has advised that the April CTP is 'feature complete', meaning that the features that are seen in the April CTP are more then likely those that will be shipped with the RTM of SQL Server 2005.  Leaving the remaining releases to concentrate on performance enhancements and bug fixes.  http://www.microsoft.com/sql/2005/productinfo/letter.asp

 

 

CLR in SQL Server 2005

There still seems to be a lot of confusion surrounding the ability to create user-defined types, functions, stored procedures, and triggers using .NET Framework programming languages.  This article attempts to clarify how SQL Common Language Runtime integration works and when it is appropriate to be used:

http://msdn.microsoft.com/vstudio/using/columns/realworld/default.aspx?pull=/library/en-us/dnreal/html/realworld03112005.asp

 

 

 

DMO of the Month:

 

In a previous issue of the newsletter we showed how it is possible to identify any SQL Server authenticated logins that have a blank password.  There was a lot of interest regrading the T-SQL script and a common request was how this script can be run across multiple instances.  This could be achieved by creating a command file that launched osql against each SQL Server instance, or using the DMO example below:

 

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.SQLServer")

 

oSQLServer.LoginSecure = True

 

Dim oFSORead

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

 

Dim oFSOWrite

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

 

Dim i

Dim sql

Dim srvRole

Dim server

Dim results

 

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

Do While Not oFSORead.AtEndOfStream

      server = oFSORead.ReadLine

     

      oSQLServer.Connect server

     

      ' The SQL statement to identify any logins that have a blank password

      sql = "SELECT name FROM master.dbo.syslogins WHERE isntname = 0 AND password IS NULL"

 

      Set results = oSQLServer.ExecuteWithResults(sql)

     

      ' Create a comma delimited file in the format ServerName, Login that identified

      ' ant logins that are blank

      For i = 1 to results.rows

            oFSOWrite.WriteLine server & ", " & results.GetColumnString(i, 1)

      Next

     

      oSQLServer.DisConnect

Loop

 

oFSORead.Close

 

Set oFSO = Nothing

Set oSQLServer = Nothing

 

 

T-SQL of the Month:

 

Instead of a pure T-SQL script this month we have decided to expand this section to also include T-SQL tips.  We will leave the title the same but may at times interchange between a Tip and interesting T-SQL applications.

 

One thing we have noticed of late whilst reviewing code is the use of SELECT COUNT(*) when testing for the existence of a value in a table.  If you need to verify the existence of a record in a table rather then use SELECT COUNT(*) you should use IF EXITS to determine if the record in question exits.  IF EXISTS is faster than SELECT COUNT(*), as the query can end immediately when the test is proven true, whilst SELECT COUNT(*) must count every record no matter if there is one or one billion records in the table before it can be found to be true or false.

 

eg.

 

Using SELECT COUNT(*):

IF (SELECT COUNT(*) FROM table_name WHERE column_name = '?')

 

Using the more efficient IF EXISTS:

IF EXISTS (SELECT * FROM table_name WHERE column_name = '?')

 

 

SQL Server Tip of the Month:

 

There are times when a Windows Group has been granted access to SQL Server and you need to determine the members of the Windows Group.  If the Group is a local group then the Local User and Groups can be queried.  Otherwise Active Directory can be queried via various means.  However the easiest way is to run the following T-SQL command:

 

xp_logininfo 'domain\group', 'members'

 

ie.

 

xp_logininfo 'BUILTIN\Administrators', 'members'

 

 

Reporting Services Tip of the Month:

 

A common question that is often asked regarding Reporting Services is, is it possible to reduce how long it takes to start Report Manager after a period of inactivity.  The most common reason for the extended length in time to load Reporting Services is that it actually needs to initialize and load two applications, Report Manager (an ASP.NET Web Application) and Report Server (the backend web service).  In order to keep both of these applications loaded in Windows 2003 Server you can modify the application pool settings and uncheck “Shutdown worker process after being idle for...”.   Once you have performed this change any web applications that uses the application pool will remain loaded, even after a period of inactivity.

If you are unable to view this document it can be downloaded from http://www.wardyit.com/newsletters/apr2005.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.