This month we have just missed the end of June for the release of the June Newsletter and as a result, there is a little bit less content than usual.  This has occurred as a result of much of our focus over the last month being concentrated on finalising the course content for the ‘A DBA’s Guide to SQL-DMO’  course that is being held in Brisbane at the end of September. For those who have not yet enrolled, the early bird rate of $450 inc. GST is still available until the 29th July 2005.  For more information regarding this course or the running of this course in other locations e-mail courses@wardyit.com or refer to the course outline http://www.wardyit.com/courses/ADBAsGuidetoSQLDMOCourseOutline.pdf.  We have also had some interest for this course to be run in Melbourne in October, so if you are unable to attend the September course in Brisbane and would like to attend the course in Melbourne, then now is the opportunity to express any preferences you have regarding dates for this course.

 

For those who have been holding out for the official release date for SQL Server 2005 before investing any time into the product; at the recent Microsoft Tech-Ed 2005 Paul Flessner, the Microsoft Senior Vice President Server Applications, announced that SQL Server 2005 will be launched the week of November 7 2005.  A good article to read to start getting up to speed with SQL Server 2005 is http://www.microsoft.com/technet/prodtechnol/sql/2005/overview.mspx.

 

A hotfix has been released for the bug introduced in SQL Server 2000 Service Pack 4 that limits the amount of memory that can be used by SQL Server to 50 percent of the physical memory that is on the server.  The hotfix can be downloaded from http://www.microsoft.com/downloads/details.aspx?familyid=7c407047-3f1f-48b8-9e4c-dc32875e1961&displaylang=en.

 

WARDY IT Solutions in the process of developing a backup monitoring solution for SQL Server, so if you are interested in being involved in the testing of this solution over the coming months then please contact us.

 

 

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 June CTP

Free SQL Server 2005 Training

New SQL Server PodCast

SYDI for SQL Server

SQL Server Cross-Tab Queries

SQL Server TPC Benchmarks

Compare Two Databases for Data & Structural Differences

DMO of the Month

T-SQL of the Month

SQL Server Tip of the Month

 

SQL Server 2005 June CTP

The June Community Technology Preview (CTP) for SQL Server 2005 is available for download and you no longer need to be an MSDN or BetaPlace Subscriber to download the product.  Currently SQL Server 2005 Enterprise Edition is available for download from

http://www.microsoft.com/downloads/details.aspx?FamilyID=dc02cbb3-d688-4663-9103-37c83e044d59&DisplayLang=en.

 

 

Free SQL Server 2005 Training

Until the 1st November 2005 Microsoft have made their E-Learning for SQL Server 2005 available for free.  So if you are looking to update your skills before the November release then this is a great place to start https://www.microsoftelearning.com/sqlserver2005/.

 

 

New SQL Server PodCast

SQL Server Downunder (http://www.sqldownunder.com/) is a new website that has been launched recently to host PodCasts for SQL Server professionals.  Currently there are two interviews that Greg Low has performed with Kalen Delaney on ‘Where we have been and Where we are going with SQL server’ and Hilary Cotter on ‘SQL Server Replication’.

 

 

SYDI for SQL Server

SYDI (Simple Network Documentation) is a tool that Network Administrators have been using for some time to assist with network and server documentation.  SYDI is now available for SQL Server to capture the configurations and settings for a SQL Server instance.  This documentation can be produced in either an XML or Word document and has the ability to greatly assist in automating configuration management.  http://sydi.sourceforge.net/download.php

 

 

SQL Server Cross-Tab Queries

A common question that is asked is how a Microsoft Access Cross-Tab query can be performed using T-SQL.  The following Knowledge Base Article lists several ways in which this can be performed http://support.microsoft.com/default.aspx?scid=kb;en-us;175574.  However we recently found the following article on performing a cross-tab query using Boolean Algebra that provides greater performance

http://www.stephenforte.net/owdasblog/PermaLink.aspx?guid=2b0532fc-4318-4ac0-a405-15d6d813eeb8.

 

 

SQL Server TPC Benchmarks

Microsoft has recently announced the first TPC benchmark results for SQL Server 2005.  Although the tests were performed using early builds of the product the results are indicative of those that can be expected.  In three TPC-H benchmarks, SQL Server 2005 had up to 162 percent higher performance than SQL Server 2000 and 38 percent higher performance than Oracle’s best result on a 16-processor Intel Itanium 2 server. In addition, the highest TPC-C result ever for Windows was achieved beating the one million transaction-per-minute mark.  The result had over 7 percent better performance and cost 35 percent less per transaction than Oracle’s best result on a 64-processor Intel Itanium. http://www.tpc.org/.

 

 

Compare Two Databases for Data & Structural Differences

A task that often needs to be performed is to identify the differences between the data and structure of two SQL Server Databases.  There are some great tools to perform this task such as Apex SQLDiff and RedGate SQLCompare, but Viktor Gorodnichenko has produced a Stored Procedure which can be used to perform this task, and best of all no business case will need to be written for its purchase http://www.sql-server-performance.com/vg_database_comparison_sp.asp

 

 

 

DMO of the Month:

 

The osql utility has a switch (osql –L) to enumerate all available SQL Servers on the local network segment.  This enumeration can also be performed using the SQLPing (http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=26) utility or by executing the SQL-DMO code snippet below:

 

 

Dim i

Dim oNames

Dim oSQLApp

 

Set oSQLApp = CreateObject("SQLDMO.Application")

Set oNames = oSQLApp.ListAvailableSQLServers()

 

For i = 1 To oNames.Count

      Msgbox oNames.Item(i)

Next

 

Set oSQLApp = Nothing

 

 

T-SQL of the Month:

 

MEDIAN is one of the aggregate statistical functions typically handled by OLAP.  MEDIAN is the middle of a distribution ie. half the values are above the median and half are below the median.  In order to calculate the MEDIAN using T-SQL the following example can be used.

 

CREATE TABLE foo

    (

        bar INT

    )

GO

 

SET NOCOUNT ON

INSERT foo (bar) SELECT 1

INSERT foo (bar) SELECT 2

INSERT foo (bar) SELECT 3

INSERT foo (bar) SELECT 4 

INSERT foo (bar) SELECT 5

INSERT foo (bar) SELECT 6 

INSERT foo (bar) SELECT 7 

INSERT foo (bar) SELECT 8

INSERT foo (bar) SELECT 9

INSERT foo (bar) SELECT 10

 

SELECT AVG(bar)

FROM   (SELECT bar

            FROM (SELECT TOP 1 bar = bar * 1.0

                     FROM (SELECT TOP 50 PERCENT bar

                     FROM foo

                               ORDER BY bar) sub_a

               ORDER BY 1 DESC) sub_1

      UNION ALL

      SELECT bar

      FROM (SELECT TOP 1 bar = bar * 1.0

            FROM (SELECT TOP 50 PERCENT bar

                        FROM foo

                        ORDER BY bar DESC) sub_b

              ORDER BY 1) sub_2) median

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