Unfortunately August’s newsletter is running a little late this month, mainly as a result of Tech-Ed last week. For those who missed the opportunity to attend this year you missed a great event, especially with all of the SQL Server 2005 sessions.  If you were unable to attend Tech-Ed or are just looking to bring yourself up to speed with SQL Server 2005 then you can’t go past the great Webcasts that Kimberly Trip (http://www.sqlskills.com/blogs/kimberly/) is currently presenting.  These Webcasts can be downloaded from http://www.microsoft.com/events/series/msdnsqlserver2005.mspx.

 

There has been continual mention in the trade press about SQL Server 2005 being the BI release.  From the feedback received at Tech-Ed, many organisations who have installed the CTP’s are keen to start implementing 2005 shortly after release.  One of the features in SQL Server 2005 which a number of people are very excited about is the Report Builder; which is basically a reporting tool to allow an end user to produce an ad-hoc report from a predefined dataset http://www.devx.com/dbzone/Article/28047/0/page/1.

 

Unfortunately for anyone who has not booked on the SQLDMO course being conducted in Brisbane on the 31st of September, all the available places have been booked.  For anyone who is interested in this course let us know as we are looking to schedule another course early in the new year and include SMO the management object in SQL 2005.

 

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 Workshops

Monitor Blocking

SQL Server Intellisense

Migrating Oracle to SQL Server

SQL Server 2005 Upgrade Advisor

SQL Server Backup and Recovery

Information Schema View Diagrams

T-SQL of the Month

SQL Server Tip of the Month

 

SQL Server 2005 Workshops

If you missed out on the first round of SQL Server 2005 Workshops in Australia then don’t despair as the workshops are about to recommence around the country throughout September and October.  For more details refer to http://www.microsoft.com/australia/events/sql2005/

 

 

Monitor Blocking

I recently came across a post from Roberto Farah who works at Microsoft's PSS (Product Support Services) regarding the monitoring of blocking in SQL Server.  Roberto is the co-author of a tool called Sherlock that helps analyze blocking problems in SQL Server.  It collates information from the sp_blocker_pss80 stored procedure and presents it in an easy to read format.  Microsoft has been using the tool internally to monitor blocking and have decided to release it to the public. The download includes the program and a short PowerPoint presentation explaining its use. To download Sherlock refer to

http://www.sqlteam.com/downloads/sherlock13.zip

 

 

SQL Server Intellisense

If you had the opportunity to test some of the earlier releases of SQL Server 2005 you would have noticed that the Intellisence feature that was present has been removed in the later release of the product.  As a result of technical issues involved with implementing this functionality, the feature had to be pushed to a future version.  Until this feature is released Atadore have developed a product PromtSQL

(http://www.promptsql.com/) that adds Intellisence to Query Analyser and SQL Server Management studio

 

 

Migrating Oracle to SQL Server

One of the most popular sessions of the Database Administration Track at the recent Tech-Ed on the Gold Coast was the Migrating of Oracle to SQL Server.  Scalability Experts (http://www.scalabilityexperts.com/) have written an in-depth TechNet Whitepaper titled ‘Solution Guide for Migrating Oracle on UNIX to SQL Server on Windows’ that presents strategies and procedural guidance to convert existing applications from Oracle to SQL Server.

http://www.microsoft.com/technet/itsolutions/cits/interopmigration/unix/oracleunixtosql/default.mspx

 

 

SQL Server 2005 Upgrade Advisor

In order to assist with the migration from SQL Server 2000 to SQL Server 2005 Microsoft has developed the SQL Server 2005 Upgrade Advisor.  The Upgrade Advisor performs an analysis on the configurations of an existing database server and database applications, providing a report that identifies deprecated features and necessary configuration changes that will impact the database upgrade process.

http://www.microsoft.com/downloads/details.aspx?FamilyID=CF28DAF9-182E-4AC2-8E88-F2E936558BF2&displaylang=en

 

 

SQL Server Backup and Recovery

If you are ever wanting to ensure that your SQL Server Backup and Recovery plans covers all possible scenario’s and that your are confident in performing each type of recovery then

http://www.dbarecovery.com is a great resource.  This Website contains detailed exercises on how to recover SQL Server, ranging from the loss of a secondary data file to having to rebuild the master database.

 

 

Information Schema View Diagrams

The company behind DB Maint (an application to assist with SQL Server Database Maintenance) have a set of printable database diagrams available for the Information Schema Views.  So if you are ever trying to determine particular meta-data from SQL Server then these diagrams should assist you.

http://www.dbmaint.com/info_schema.asp

 

 

 

T-SQL of the Month:

 

A common question in T-SQL is how to return a row number or rank from a Query similar to Oracle’s ROWNUM function.  In SQL Server 2005 the ROW_NUMBER function is introduced and will attach a row number to each row returned in a result set, but until then the example below will need to be used to attach a rank.  For more information regarding attaching row numbers refer to the following article by Greg Larsen that demonstrates a variety of approaches to this problem http://www.geocities.com/sqlserverexamples/article3.htm.

 

 

CREATE TABLE #cities

    (

            state VARCHAR(20),

            city VARCHAR(20)

    )

 

INSERT #cities (state, city) SELECT 'Queensland', 'Mackay'

INSERT #cities (state, city) SELECT 'Queensland', 'Cairns'

INSERT #cities (state, city) SELECT 'Queensland', 'Brisbane'

INSERT #cities (state, city) SELECT 'Tasmania', 'Launceston'

INSERT #cities (state, city) SELECT 'Tasmania', 'Hobart'

INSERT #cities (state, city) SELECT 'South Australia', 'Adelaide'

INSERT #cities (state, city) SELECT 'West Australia', 'Albany'

INSERT #cities (state, city) SELECT 'West Australia', 'Perth'

INSERT #cities (state, city) SELECT 'Victoria', 'Melbourne'

INSERT #cities (state, city) SELECT 'Victoria', 'Sale'

INSERT #cities (state, city) SELECT 'Victoria', 'Rutherglen'

 

SELECT     state, city, rank = 

    (

           SELECT     COUNT(*)

            FROM       #cities t2

            WHERE      t2.state = t1.state

            AND   t2.city <= t1.city

    )

FROM       #cities t1

ORDER BY state, city

 

 

 

SQL Server Tip of the Month

 

On a SQL Server instance where auditing is enabled, it is very easy for the SQL Server errorlog to contain a lot of noise and to become quite large.  In order to maintain the size of the errorlog to assist in easily identifying errors that have occurred the  sp_cycle_errorlog stored procedure can be executed in order to start a new errorlog.  When the errorlog is cycled, the entry Attempting to cycle errorlog will be displayed in the previous log.  If the errorlog is cycled on a scheduled basis then you may also like to increase the number of files maintained before they are recycled so that sufficient history is kept.

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