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