On the 14th of September the release of the September CTP for SQL Server 2005 was announced.  One significant exclusion in this release was Database Mirroring.  Database Mirroring is a feature that many people were looking forward to in order to assist with their business continuity strategies.  Database Mirroring transfers transaction log records directly from one server to another standby server in order to allow the principal server to quickly fail over to the standby server (http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx). Despite the feature being complete, field testing will not be completed by the time of launch. As a result it is expected that Database Mirroring will generally be available in the first half of 2006.

 

Another significant announcement which was made at the release of the September CTP was that a scaled-down version of the SQL Server 2005 Management Studio for SQL Server 2005 Express Edition would be released. This will provide the ability to more efficiently manage SQL Server 2005 Express Edition with increased automation and lower complexity. The final delivery of this technology will depend on additional feedback from customers, but it is anticipated that it will also be delivered in the first half of 2006.

 

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:

SSW SQL Auditor

SQL Server 2005 Beta Exams

Migrating Oracle to SQL Server

Project Real

SQL Server Batch Compilations

SQL Wish

SQL IO Disk Subsystem Benchmark Tool

SQL Dependency View Beta 1

DMO of the Month

T-SQL of the Month

 

SSW SQL Auditor

A toolset that is commonly being asked for is the ability to run checks over a SQL Server instance to verify that development standards and administration practices have been implemented in a manner consistent with a set of common standards and principles.  Although Microsoft has released the Best Practices Analyzer (BPA) for SQL Server to assist in performing this task, it is largely focused around administrative practices.  Superior Software for Windows have developed SSW SQL Auditor to focus on SQL Server development practices and check that a wide range of performance and coding practices have been correctly adopted.  SQL Auditor provides the ability to identify issues with database design and development standards prior to the performance of SQL Server being reduced as a result of these bad-practices not being identified.  The checks that SQL Auditor performs range from checking that the maximum row size for a table is less then SQL Servers data page size, to checking that all Stored Procedures contain a standard comment block.  One of the great features of SQL Auditor is not only the ability to report on issues, but to also generate the SQL Scripts that will correct the issues. 

 

 

I think the best thing that I found out about SQL Auditor (apart from the ability to customise the checks) is the rapid way that the developers from SSW respond to feedback for their product.  There were several improvements that I thought could be made to the product, such as when validating dates to include the smalldatetime datatype.  The team at SSW were more then happy to listen to this feedback and implemented the improvements in the next version of the product that was released later the same week.  So if you are looking for a tool to assist with verifying that development standards are being adopted by all of the people developing SQL Server solutions in your organisation then SSW SQL Auditor is a worthy tool to purchase and add to your toolkit.  For a free trial version of SSW SQL Auditor refer to http://www.ssw.com.au/ssw/SQLAuditor/.

 

 

SQL Server 2005 Beta Exams

For any one that is interested in performing the SQL Server 2005 Beta Exam, you can sign up for them at http://www.zoomerang.com/recipient/survey-intro.zgi?p=WEB224MR5DUMAT.  At this stage four exams are being offered as Beta’s; ‘70-441: Designing Database Solutions by Using Microsoft SQL Server 2005’ (Beta in November, 2005), ‘70-442: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005’ (Beta in February, 2006), ‘70-443: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005’ (Beta in November, 2005) and ‘70-444: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005’ (Beta in December, 2005).

 

 

Migrating Oracle to SQL Server

Microsoft have release version 2.0 Beta of the SQL Server Migration Assistance for Oracle (SSMA) to assist with automating the migration from Oracle to SQL Server.  SSMA automates almost every aspect of the migration process including assessment, schema and data migration, business logic conversion and validation.  To download SSMA refer to http://www.microsoft.com/downloads/details.aspx?FamilyID=E35CEE88-C919-463F-B020-81468CD231DA&displaylang=en.

 

 

Project Real

Recently Microsoft and a number of technology partners in the business intelligence (BI) industry undertook a BI project using SQL Server 2005 for Barnes and Noble.  As part of this undertaking, a series of White Papers were written to help assist other organisations who are adopting SQL Server 2005 for their BI solutions so that they are able to learn the lessons and best-practices identified during this large-scale implementation.  http://www.microsoft.com/technet/prodtechnol/sql/2005/projreal.mspx.

 

 

SQL Server Batch Compilations

Recently there was a lot of discussion on the SQLDownUnder mailing list regarding batch compilations.  There is a White Paper available that explains how batches are cached and reused in SQL Server 2005.  The white Paper also provides best practices on maximizing the reuse of cached plans and explains scenarios in which batches are recompiled and gives best practices for reducing or eliminating unnecessary recompilations to ensure maximum performance. http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx.

 

 

SQL Wish

As SQL Server 2005 is going to be released on 7th November, the SQL Server Development team have started work on defining the next release of SQL Server. Tom Rizzo (Director of Product Management for SQL Server) has recently announced via his blog (http://sqljunkies.com/WebLog/tom_rizzo/default.aspx) that the development team is keen for the community to be involved in this process.  So if there is an enhancement that you would like to see in the next release of the product, now is your opportunity to have your idea captured by sending an email with your idea to sqlwish@microsoft.com.

 

 

SQL IO Disk Subsystem Benchmark Tool

Lately there has been a lot of discussion surrounding the best way to determine the I/O capacity for a given disk configuration.  SQL IO is a tool that can be used to determine the I/O capacity of a given hardware configuration.  The purpose of SQL IO is not just to simulate I/O patterns but rather to test a variety of I/O types and sizes and determine the capacity of an I/O subsystem.  SQL IO can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=9a8b005b-84e4-4f24-8d65-cb53442d9e19&DisplayLang=en.

 

 

SQL Dependency Viewer Beta 1

Red-Gate Software, the company behind SQL Data Compare (amongst other SQL Server Tools), has released a beta of the Dependency Viewer product.  Dependency Viewer analyses a selected database and then generates an interactive diagram to assist in determining which objects have a dependency to another object.  The free beta can be downloaded from http://www.red-gate.com/products/sql_dependency_viewer/.

 

 

 

DMO of the Month:

 

In order to determine the collation of each database for a SQL Server instance the VBScript code snippet below can be used:

 

Dim oSQLServer

Dim oDatabase

 

Set oSQLServer = CreateObject("SQLDMO.SQLServer")

Set oDatabase = CreateObject("SQLDMO.Database2")

 

oSQLServer.LoginSecure = True

oSQLserver.Connect("(local)")

 

For Each oDatabase In oSQLServer.Databases

      Msgbox oDatabase.Name & " = " & oDatabase.Collation

Next

 

oSQLServer.DisConnect

 

Set oDatabase = Nothing

Set oSQLServer = Nothing

 

 

SQL Server Tip of the Month:

 

If you have ever had the need to determine when a SQL Server Agent job completed using a SQL query then you will know that it is not as straight forward as it sounds.  The start time and execution duration for SQL Server Agent jobs are stored in the sysjobhistory table in the MSDB Database.  Unfortunately, the sysjobhistory table does not contain a field called end_datetime, it however does contain the run_date and run_time fields to determine when the job started and the run_duration field to determine the elapsed time for the execution of the job or step in HHMMSS format.  The T-SQL script below can be used to determine the datetime that each SQL Server Job completed.  For more datetime functions that can be used to determine various information regarding SLQ Server Jobs refer to the following article http://www.sqldev.net/sqlagent/SQLAgentDateTime.htm.

 

SELECT    

    j.name,

    endTime =   CONVERT(DATETIME, RTRIM(run_date)) + 

    (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10 + 25 *   duration) / 216e4

FROM        msdb.dbo.sysjobhistory jh JOIN msdb.dbo.sysjobs j

ON          j.job_id = jh.job_id

WHERE      jh.step_name = '(Job outcome)'

 

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