Over the last month since the release of the September newsletter there has been plenty happening.  One of the things that WARDY IT Solutions is proud to introduce is our new Client Referral program.  This program is a way for us to say thank you to our valuable clients for showing the faith in us to continually deliver world class SQL Server solutions by referring new business leads to us.  So if you know of anyone who you think would benefit from the SQL Server solutions that WARDY IT is renowned for, we invite you to introduce them to us and take advantage of the Client Referral program.

 

On the subject of new introductions to WARDY IT Solutions, we are delighted to announce that we have recently partnered with a talented emerging Brisbane designer Esther Cole to assist us with several website projects that we are currently working on.  So if you are looking for a team that can assist you to deliver a leading website solution then why not contact us today.

 

Also, the WARDY IT Solutions website has recently been updated to reflect our strengths as one of the leading SQL Server consulting providers in Queensland and to provide ready access to SQL Server information and resources.  So why not have a look at the new design and let us know what you think.

 

WARDY IT Solutions is pleased to introduce a new, innovative and cost-effective way for organisations to license Microsoft software without the upfront capital outlay.  As a Service Provider License Agreement (SPLA) Partner, WARDY IT Solutions is now able to reduce your Microsoft licensing costs so that you only pay for what you use as an operational expense. 

 

WARDY IT Solutions is the proud sponsor of the Seduction Sedation Sedition print exhibition being shown in Brisbane from the 10th to 26th of November at the Circle Gallery, 274 Montague Road, West End.  For more details on the exhibition refer to http://www.impress.org.au/exhibitions.htm.

 

The dates for SQL Down Under Code Camp 2007 have been announced.  The event will be held once again at Charles Sturt University in Wagga Wagga on the weekend of the 6th and 7th of October 2007.

 

If you missed attending the SQL Down Under Code Camp 2006 I will be re-presenting my session at the Perth SQL Server User Group on the 14th of December.  So, if you are in Perth and would like to learn how to optimise the I/O subsystem to improve the performance of SQL Server as well as see the ‘legendary’ USB Key RAID array, then why not attend this event.

 

If you are looking for an opportunity to share knowledge, information and experiences with other IT professionals in South East Queensland then why not attend the next WARDY IT Community Dinner?  The next dinner is on Monday the 20th of November at the Mecca Bah, 19-21/1000 Ann Street, Fortitude Valley.  The November Community Dinner is one week later than usual as I will be in Seattle presenting at the PASS Community Summit on the second Monday in November.  Add this event to your Outlook Calendar

 

If you are looking for additional SQL Server information between the WARDY IT newsletters then don’t forget to subscribe to the RSS feed for our SQL Server Blog http://www.wardyit.com/blog.

 

 

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:

November Queensland SQL Server User Group

Data Dude CTP6

SQL Code Camp Oz Presentations

BI Documentor

Data Modeling:Understanding and Being Understood

SQL Server Script Repository

Query Processing Blog

SQL Server Publishing Wizard

SQLQueryStress

SQL Everywhere

SQL Server Tip of the Month

TSQL Tip of the Month

SMO Tip of the Month

November Queensland SQL Server User Group

Mark Morton will be presenting at the November QLD SQL Server User Group on SQL Server 2005 Cryptography.  Note this meeting is on Wednesday the 22nd of November instead of Thursday the 23rd of November.  For more details regarding this event refer to http://www.sqlserver.org.au/Events/ViewEvent.aspx?EventId=219.

 

 

Data Dude CTP6

Community Technology Preview (CTP) 6 of Visual Studio 2005 Team Edition for Database Professionals has been released.  This version now provides full support for SQL Server 2000 and 2005 objects.  For a wealth of information regarding this product refer to Gert Drapers blog http://blogs.msdn.com/gertd/.

 

 

SQL Code Camp Oz Presentations

At the recent SQL Down Under Code Camp some of the most knowledgeable SQL Server experts in Australia presented.  The audio for the majority of the presentations was recorded and has been made available as an mp3.  The following blog post contains a list of all of the presentations that are available for download http://www.wardyit.com/blog/blog/archive/2006/10/31/162.aspx.

 

 

BI Documentor

BI Documentor is a SQL Server documentation solution that goes well beyond many of the other SQL Server documentation tools in the market place.  Not only does BI Documentor generate complete SQL Server documentation at the push of a button, but it also provides MSDN style documentation for all of the SQL Server Business Intelligence components including Analysis Services, Integration Services and Reporting Services.  To find out more about BI Documentor or to download a trial version refer to http://www.bidocumenter.com.

 

 

Data Modeling:Understanding and Being Understood

Graeme Simsion has written a great article on how to convince business users of the importance of the data model and how to ensure that all the stakeholders provide the required contributions whilst understating the implications of their decisions.  The article can be found at http://www.tdan.com/i038fe04.htm.

 

 

SQL Server Script Repository

The following TechNet script repository contains an assortment of sample scripts for managing SQL Server.  The scripts range from listing the Top Wait Types for a Workload to comparing Locking and Repeatable Reads.  The script repository can be found at http://shrinkster.com/jlo.

 

 

Query Processing Blog

The following blog by Craig Freeman provides a great resource for understanding exactly what happens when a SQL Query is executed.   The blog articles explains how to read showplan and how some of the most common operators such as index scans and seeks, joins and aggregations actually work.  The blog can be found at http://blogs.msdn.com/craigfr/.

 

 

SQL Server Publishing Wizard

CTP1 of the SQL Server Database Publishing Wizard has been made available for download.  This new product can be used to generate the TSQL scripts for both schema and data, allowing tables to be easily transported between environments.  It can be downloaded from http://shrinkster.com/je8. 

 

 

SQLQueryStress

Adam Machanic has developed and released a free tool to assist with performance and stress testing of T-SQL queries.  The tool automatically collects metrics to help you determine whether your queries will perform under load and what kind of resource strain they put on your server.  The tool and documentation can be downloaded from http://www.datamanipulation.net/SQLQueryStress/.

 

 

SQL Everywhere

In episode 199 of .Net Rocks, Carl and Richard talk to Josh Holes on SQL Server Everywhere and how this new SQL Server offering can be used to overcome some of the existing issues with deploying SQL Server to mobile devices.  This episode can be downloaded from http://www.dotnetrocks.com/default.aspx?showID=202.

 

 

 

SQL Server Tip of the Month

Ideally SQL Server I/O should be spread out among the various files for the best overall performance.  An easy way to determine the statistical information on the I/O activity of SQL Server since it was last started is to use the fn_virtualfilestats function.  The fn_virtualfilestats function returns I/O statistics for both database files and log files since SQL Server was started. In SQL Server 2005, this information is also available from the sys.dm_io_virtual_file_stats dynamic management view.

 

The usage of fn_virtualfilestats is:

sys.dm_io_virtual_file_stats(

{ database_id | NULL }

, { file_id | NULL }

)

 

-- SQL Server 2005 - Return statistics for fileid 1 for the AdventureWorks database

SELECT * FROM sys.dm_io_virtual_file_stats(DB_ID(N'AdventureWorks'), 1)

 

-- SQL Server 2005 - Return statistics for all databases and files

SELECT * FROM sys.dm_io_virtual_file_stats(NULL, NULL)

 

-- SQL Server 2000 - Return statistics for all databases and files

SELECT * FROM ::fn_virtualfilestats(-1, -1)

 

 

T-SQL of the Month

In the November 2005 edition of the WARDY IT Solutions SQL Server newsletter, we demonstrated how a table can be pivoted using CASE statements.  The following example illustrates the same pivot being performed using the SQL Server 2005 Pivot operator.

 

SELECT     SaleYear,

            [1], [2], [3], [4]

FROM  (SELECT SaleYear, Sale, Quarter    

             FROM #Sales) AS SourceTable

PIVOT

(

      SUM(Sale)

      FOR Quarter IN ([1], [2], [3], [4])

) AS PivotTable

 

 

SMO Tip of the Month

The following example illustrates how VB.Net  and SMO could be used to loop through all databases and remove a user from a database role.  You will need to add a reference to you project for Microsoft.SqlServer.Smo and Microsoft.SqlServer.ConnectionInfo.

 

Imports Microsoft.SqlServer.Management.Smo

…

Dim SMOserver As Server = New Server

For Each db As Database In SMOserver.Databases

Dim dbRole As DatabaseRole

dbRole = db.Roles("db_owner")

If dbRole.EnumMembers.Contains("usertodrop") Then

dbRole.DropMember("usertodrop")

End If

Next

 

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

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.