WARDY IT Solutions Jun 2006 Newsletter

There have been some big news stories in the SQL Server space this month. The biggest news by far is the announcement of Visual Studio 2005 Team Edition for Database Professionals (aka Data Dude). With this product, database professionals will finally be ‘first class’ citizens in Visual Studio. This product contains many features such as schema and data comparison and Source Control integration that previously required the use of third-party tools. For more information and to download the CTP of Data Dude refer to http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro. Also of significant note was the release of the CTP for SQL Server Everywhere. SQL Server Everywhere is a compact version of SQL Server 2005 designed to bring the features of SQL Server to any device from the desktop to mobile devices. The CTP can be downloaded from http://shrinkster.com/g8j.

I will be in Canberra on the 8th of August 2006 presenting at the Canberra SQL Server Users Group. So if you are in Canberra and would like to learn more about SQL Server Automation then why not attend this event. For more details refer to http://www.sqlserver.com.au/Events/ViewEvent.aspx?EventId=176.

I will also be presenting at the 2006 PASS Community Summit which is being held in Seattle from the 14^th to 17^th of November 2006. The PASS Community Summit is the worlds largest event dedicated exclusively to SQL Server. For more details on this conference including registration refer to http://www.sqlpass.org/events/summit06/index.cfm.

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 WARDY IT Community Dinner is held on the second Monday of each month. The next dinner is on Monday the 10th of July at the Story Bridge Hotel - Deery's Restaurant. Add this to my Outlook Calendar.

Also hot off the press is the announcement of the SQL Down Under Code Camp to be held at Charles Sturt University in Wagga Wagga on the weekend of the 7^th and 8^th of October. Fore more details on what should be a fantastic event refer to http://www.sqldownunder.com/CodeCamp/tabid/53/Default.aspx.

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:

SQL Server Consolidation

SQL Server 2005 Upgrade Labs

SQL Server 2005 BI Training

Storage Engine Blog

Virtual TechEd

SQL Server Always On

SQL Server Express Maintenance Plans

Fix Function

SMO of the Month

SQL Server Tip of the Month

TSQL Tip of the Month

Reporting Services Tip of the Month

SQL Server Consolidation

Two hot SQL Server topics at the moment are server consolidation and the x64 platform.

The following whitepaper identifies the considerations that need to be evaluated when determining how to approach the consolidation of many SQL Server databases on a single server and how to achieve optimal performance as a result of this consolidation. The whitepaper can be found using the following link

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/64bitconsolidation.mspx.

SQL Server 2005 Upgrade Labs

If you are looking to upgrade from SQL Server 7 or 2000 to SQL Server 2005 then you should take advantage of the ability to work through each of the SQL Server Upgrade Virtual Labs that Microsoft has made available. These labs will allow you to gain experience in performing upgrades involving both the relational database engine and business intelligence components. The Virtual Labs are available at http://www.microsoft.com/technet/traincert/virtuallab/sqlupgrade.mspx.

SQL Server 2005 BI Training

Peter Myers who is recognised as a leader in SQL Server Business Intelligence, will be delivering courses throughout July on BI in SQL Server 2005. If your are looking for an opportunity to learn about all of the changes and new features in the BI components of SQL Server 2005 from Reporting Services and SSIS to Analysis Services then you should not miss this opportunity. For more information regarding these courses, refer to http://www.solidqualitylearning.com.au/.

Storage Engine Blog

Paul Randal who is the Lead Program Manager for Microsoft SQL Server Storage Engine and SQL Express has started a new blog on the SQL Server storage engine. This blog is definitely one to aggregate as there are some really in-depth postings on the internals of the storage engine ranging from IAM chains and allocation to fragmentation. The URL for this blog is https://blogs.msdn.com/sqlserverstorageengine/default.aspx.

Virtual TechEd

TechEd has only just wrapped up in Boston and as always there were numerous announcements about upcoming technologies and almost infinite content on current technologies. Virtual TechEd is a site dedicated to making a large amount of the content that was presented at TechEd available to those that were unable to attend. The virtual TechEd site is http://virtualteched.com/.

SQL Server Always On

A common reason for the poor performance or even failure of SQL Server is the choice of hardware platform that SQL Server runs on. In order to make it easier to make the right hardware decision Microsoft have published the requirements of the SQL Server IO system allowing hardware vendors to verify their hardware solutions against the requirements of SQL Server. For more details on how SQL Server can be used for mission critical business refer to http://www.microsoft.com/sql/alwayson/default.mspx.

SQL Server Express Maintenance Plans

One of the limitations of SQL Server Express is the lack of maintenance plans. This means that you need to come up with another solution for performing routine maintenance tasks such as database backups and index rebuilds. Jasper Smith has released an updated version of the ExpressMaint Utility which provides a maintenance plan replacement for SQL Server Express. The utility can be downloaded from http://www.sqldbatips.com/showarticle.asp?ID=29.

Fix Function

Rob Farley recently had a requirement for a FIX function in SQL Server that would round a number to specified number of significant digits. Not only did Rob come up with an elegant solution, but he has also written a great article on the process that he went through to reach this solution. The article and the FIX function can be found at http://sqlblogcasts.com/blogs/rob_farley/articles/829.aspx.

SMO of the Month

The following example illustrates how using VB.Net and SMO you could loop through all tables in the Northwind database and change the schema to guest. 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("WARDYIT01")

Dim db As Database = SMOServer.Databases("northwind")

Dim Tables As List(Of Table) = New List(Of Table)

For Each tbl As Table In db.Tables

Tables.Add(tbl)

For Each tbl As Table In Tables

tbl.ChangeSchema("guest")

SQL Server Tip of the Month

Often the sp_help_revlogin (http://support.microsoft.com/kb/246133/) Stored Procedure is used to transfer logins from one server to another. But the one thing that would be nice is if this script also generated the Server Roles for each login. The TSQL script below can be used to generate a script to add each login into the correct Server Role. The script also illustrates how a table can be unpivoted in SQL Server.

SELECT 'EXEC sp_addsrvrolemember ''' + name + ''', ' + ServerRole

FROM (

SELECT name, T1.role AS ServerRole,

CASE T1.role

WHEN 'sysadmin' THEN sysadmin

WHEN 'securityadmin' THEN securityadmin

WHEN 'serveradmin' THEN serveradmin

WHEN 'setupadmin' THEN setupadmin

WHEN 'processadmin' THEN processadmin

WHEN 'diskadmin' THEN diskadmin

WHEN 'dbcreator' THEN dbcreator

WHEN 'bulkadmin' THEN bulkadmin

END AS Member

FROM master.dbo.syslogins

CROSS JOIN

(

SELECT 'sysadmin'

UNION

SELECT 'securityadmin'

UNION

SELECT 'serveradmin'

UNION

SELECT 'setupadmin'

UNION

SELECT 'processadmin'

UNION

SELECT 'diskadmin'

UNION

SELECT 'dbcreator'

UNION

SELECT 'bulkadmin'

) T1 (role)

WHERE name <> 'sa') AS T2

WHERE T2.Member = 1

TSQL Tip of the Month

The OUTPUT clause has been introduced in SQL Server 2005 to return each row affected by an INSERT, UPDATE or DELETE statement. The behavior of the OUTPUT clause is very close to that of the deleted and inserted conceptual tables in a Trigger. Below is an example of how the OUTPUT clause can be used to return each row affected by a DELETE statement:

-- Create a new orders table called orders_output so that we do not need

-- to worry about the Foreign Key constraint

SELECT *

INTO northwind.dbo.orders_output

FROM northwind.dbo.orders

-- Delete the Order ID's 10248 and 10249 and return the details of the rows

-- affected by this delete statement by using the OUTPUT clause

DELETE northwind.dbo.orders_output OUTPUT DELETED.*

WHERE orderid IN (10248, 10249)

Returns:

OrderID CustomerID EmployeeID ... ShipPostalCode ShipCountry

----------- ---------- ---------- ... -------------- ---------------

10248 VINET 5 ... 51100 France

10249 TOMSP 6 ... 44087 Germany

Reporting Services Tip of the Month

When using Reporting Services it can be cumbersome and time consuming to manually deploy multiple reports. The following code sample illustrates how the report deployment process can be automated using the web service that is exposed by the SQL Server 2005 Reporting Services http://www.codeproject.com/cs/database/SQLReportDeploy.asp

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