WARDY IT Solutions Mar 2005 Newsletter

It was great to hear so much positive feedback regarding the inclusion of the Reporting Services Tips of the month. Reporting Services is definitely starting to make in-roads into the reporting market as it seems to be the reporting platform of choice for new reporting deployments. I am sure that this penetration will continue, especially with the rich feature set that Reporting Services will bring to the table with SQL Server 2005.

Speaking of SQL Server 2005, if you have not installed any of the pre-releases yet I would strongly suggest that you seriously look at allocating some time to familiarise yourself with the new IDE. Between SQL Server 6.5 and 7.0 there were significant changes to Enterprise Manager which were further tweaked with the release of SQL Server 2000. However SQL Server Management Studio (the new Enterprise Manager) resembles the Visual Studio IDE as apposed the current MMC snap-in, and really is an evolution away from the Enterprise Manager that we are all familiar with.

Lately more and more organisations seem to be taking advantage of the ability for multiple instances of SQL Server 2000 to be installed on the same server, but there seams to be some confusion regarding the licensing requirements. Basically with Standard Edition, using the Server/CAL licensing model, you must have a separate license for each instance, where as Enterprise Edition allows multiple instances of SQL Server on the same server. So in some cases it may be cheaper to purchase Enterprise Edition if you plan on installing multiple instances of SQL Server on the same server.

On behalf of WARDY IT Solutions I would like to take this opportunity to wish all of you who subscribe to this newsletter along with your familles a Happy Easter and we look forward to your continued support after the break.

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:

Expanded SQL Server Product Line

Reporting Services Scripter

SQL Server 2005 CTP 3

February Security Summit PowerPoint Decks

SQL Server Mailing List

SQL Server 2005 Training

Another reason to use Windows Authentication

Tech.Ed 2005

SMTP Mail Task for DTS

DMO of the Month

T-SQL of the Month

SQL Server Tip of the Month

Reporting Services Tip of the Month

Expanded SQL Server Product Line

Tom Rizzo has announced a new product in the existing SQL Server family called SQL Server Workgroup Edition. Workgroup Edition is an entry-level database that scales beyond MSDE but at the same time priced less than SQL Server Standard Edition. SQL Server 2000 Workgroup Edition will support up to 2 CPUs, 2 gigabytes of memory, and an unlimited database size (note that SQL Server 2005 Workgroup Edition increases the memory limitation to 3 gigabytes). For more information refer to

http://www.microsoft.com/sql/spotlight/expandsqlserver.asp

Reporting Services Scripter

Jasper Smith from www.sqldbatips.com has developed a .Net application that enables scripting of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options and server side report parameters. http://www.sqldbatips.com/showarticle.asp?ID=62

SQL Server 2005 CTP 3

Microsoft has released the third Community Technical Preview (CTP) for SQL Server 2005. The latest CTP includes a number of new features such as enhanced integration with Visual Studio 2005, performance improvements in Management Studio, a SQL Server 2000 to SQL Server 2005 upgrade tool, and 64-bit support for Reporting Services, Notification Services and the management, development and configuration tools. The CTP is available to download for all MSDN and Betaplace subscribers.

February Security Summit PowerPoint Decks

For those of you who were unable to attend the February Security Summit you missed out on three fantastic presentations from Greg Low (http://msmvps.com/greglow/) on SQL Server 2005. Greg has kindly posted the PowerPoint deck from each of the presentations on the QLD SQL Server User Group website (http://www.qssug.org).

SQL Server 2005 Overview

http://www.qssug.org/SQL2005Overview.ppt

SQL Server 2005 High Availability Scalability and Reliability

http://www.qssug.org/SQL2005HighAvailability.ppt

SQL Server 2005 Introduction for Developers

http://www.qssug.org/SQL2005Developer.ppt

For a video of the SQL Introduction for Developers Greg has recorded the session in his kitchen and it can be viewed using live meeting from https://www119.livemeeting.com/rm/microsoft/view?id=acoat458839

SQL Server Mailing List

For those of you who like the “in your face” nature of mailing lists a new Australian SQL Server Mailing list has been established. The list allows you to send a question to other SQL Server Professionals as well keeping you abreast of what others are doing with SQL Server. To subscribe to the list send an e-mail to SQLDownUnder@listserver.readify.net including “subscribe” as the subject.

SQL Server 2005 Training

Microsoft has released a series of Webcasts for SQL Server 2005 http://www.microsoft.com/events/series/technetsqlserver2005.mspx plus for those who prefer instructor led training there is a series of two day workshops running throughout Australia during April to bring you up to speed with all the new features of SQL Server 2005 including Database Infrastructure, Business Intelligence and Database development.

http://www.microsoft.com/australia/events/sql2005/default.aspx

Another reason to use Windows Authentication

Martin Rakhmanov has identified a way in which to retrieve the connection passwords used for any connections in a DTS package. So this is just one more of a long list of reasons that SQL Server authentication should not be used and that your DTS Packages should be secured. For more information regarding this refer to http://jimmers.russia.webmatrixhosting.net/dtspackage2.aspx

Tech.Ed 2005

It must be that time of the year were organisations are starting to look at their development plans for the next year as there has started to be interest around Tech.Ed 2005. For those of you who have been looking for a free trip to the Gold Coast you are in luck, as Tech.Ed 2005 will be held at the Gold Coast from Wednesday August 31 to Friday September 2 allowing for an opportunity to spend the weekend in Queensland. For more information regarding proposed sessions refer to http://blogs.msdn.com/charles_sterling/.

SMTP Mail Task for DTS

From the guys at www.sqldev.net who brought you the xp_smtp_sendmail Extended Stored Procedure as a MAPI replacement for xp_sendmail comes a custom DTS Mail Task. This task can be used in place of the Send Mail Task in DTS by providing the same functionality but without relying on MAPI or the requirement to have Microsoft Outlook installed. Beta 1 can be downloaded from http://www.sqldev.net/dts/SMTPTask.htm.

DMO of the Month:

One thing that is missing from Enterprise Manager is the ability to run a report that allows you to display the roles that each user is a member of in each databases. ie. In the Northwind database jsmith is a member of the db_ddladmin and db_securityadmin and in Pubs database is a member of db_datareader.

The following DMO code snippet runs a report for each user in each database and shows all the roles they belong to. This a great way to identify all the user who are member of the db_owner database role. You do know who is a member of dbo don’t you.

Option Explicit

Const SQLSERVER = "(local)"

Dim oSQLServer

Dim i

Dim db

Dim role

Dim user

Dim members

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

oSQLServer.LoginSecure = True

oSQLServer.Connect SQLSERVER

' For each user in each database list any roles that each user is a

' member of

For Each db In oSQLServer.Databases

For Each user in db.Users

' Do not show the role membership for the dbo user

If user.name <> "dbo" Then

Set members = user.ListMembers

role = ""

For i = 1 To members.Count

' Do not show membership to the public role

' as all users are members of the public role and a user cannot be ' removed from this role

If members.Item(i) <> "public" Then

role = role & Members.Item(i) & ", "

End If

' If role is empty the the user is only a member of the Public role

' Hence show a message box advising ' of this

If role <> "" Then

role = Mid(role, 1, len(role) - 2)

' Remove the last two characters of ", " from the role namedlist. ' Display a message box with the roles membership for each user

Msgbox "The user " & user.name & " belongs to the following " & _

"roles in the " & db.name & " Database" & vbCrLf & role

Else

Msgbox "The user " & user.name & " ONLY belongs to the PUBLIC " & _

"role in the the " & db.name & " Database"

End If

oSQLServer.DisConnect

Set oSQLServer = Nothing

T-SQL of the Month:

One of the most under utilised features of SQL Server is that of meta-data management. Meta-data management is the storage of data that describes data ie. data that describes SQL Sever Objects. This data can be added to not only Tables and Stored Procedures but also individual columns in a table. Allowing an inbuilt data dictionary to be generated for each column in each table.

Below is an example illustrating the creation of a new table and the addition of meta-data to the OrderID column using the sp_addextendedproperty Stored Procedure. The MS_ Description Property name refers the Description Property of the Column.

USE TEMPDB

CREATE TABLE dbo.meta_data_example

(

OrderID INT IDENTITY(1, 1) NOT NULL,

CustomerID NCHAR(5) NULL,

EmployeeID INT NULL

)

EXEC sp_addextendedproperty 'MS_Description', 'meta data value', 'user', dbo, 'table', meta_data_example, 'column', OrderID

The meta-data can be queried using the fn_listextendedproperty function as illustrated below:

SELECT *

FROM ::fn_listextendedproperty (NULL, 'user', 'dbo', 'table', 'meta_data_example', 'column', 'OrderID')

Returns:

objtype objname name value

--------- -------- --------------- ----------------

COLUMN OrderID MS_Description meta data value

(1 row(s) affected)

SQL Server Tip of the Month:

One activity that should be included in the SQL Server Operation Guide for each environment is as a monthly task to Audit any SQL Logins for blank passwords. Even if the authentication mode is Windows only it is essential that all SQL Logins are checked to ensure that there are no opportunities to gain unauthorised access.

SQL Logins that have blank passwords can be identified by executing the T-SQL statement below:

SELECT name

FROM master.dbo.syslogins

WHERE isntname = 0

AND password IS NULL

Reporting Services Tip of the Month:

Last month there was plenty of feedback regarding the inclusion of the Reporting Services Tips of the Month. However one of the things that was apparent is that many of you are only just starting to investigate how you can utilise Reporting Services in your environment. Hence instead of a Tip I have included a link to an article of Reporting Services Tips and Tricks learned in the fields from deployment to development; as well as a step-by-step guide on developing and deploying your first Reporting Services report. http://www.eggheadcafe.com/articles/20040823.asp

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