SQL Server 2005 SP3 is out today!

To everyone's surprise, Microsoft DID release the SQL Server 2005 SP3 before end of the year 2008! Now we are waiting for the brave souls to try it out before we adopt it at work

Download.

SQL Server Build version will be 9.00.4035 after SP3 (SP2 was 9.00.3042)

Overview
Service Pack 3 for Microsoft SQL Server 2005 is now available. SQL Server 2005 service packs are cumulative, and this service pack upgrades all service levels of SQL Server 2005 to SP3. You can use these packages to upgrade any of the following SQL Server 2005 editions:

* Enterprise
* Enterprise Evaluation
* Developer
* Standard
* Workgroup

System Requirements
* Supported Operating Systems: Windows 2000 Service Pack 4; Windows Server 2003; Windows Server 2008; Windows Vista; Windows XP
* 32-bit systems (x86)
o PC with Intel or compatible Pentium III 600 MHz or higher processor (1 GHz or faster recommended)
* 64-bit systems (x64, ia64)
o 1GHz or faster processor
* Minimum of 512 MB of RAM (1 GB or more recommended)
* 675 MB of available hard disk space

Note:
SQL Server 2005 Service Pack 3 (SP3) is designed to run on Windows Vista and Windows Server 2008.

Passed 70-431 Exam = I am MCTS: SQL Server 2005

I am now officially Microsoft Certified Technology Specialist: SQL Server 2005, passed the 70-431 exam today with flying colours

Next are 2 more exams to get my MCITP: Database Administrator certification

PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005
http://www.microsoft.com/learning/en/us/exams/70-443.mspx

PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005
http://www.microsoft.com/learning/exams/70-444.mspx

70-431 Exam - Here I Come

You snooze, you loose, proven again

I was lazy and waiting patiently to book my 70-431 exam for end of December when everyone is relaxed, vacations around the holidays. Then I looked yesterday, NOTHING is available until late January 2009, and the only spot left is NEXT Monday. Oops

I took it, and what better reason to force myself to study harder and memorize everything before I lose it in couple weeks anyway.

Exam 70-431: TS: Microsoft SQL Server 2005 - Implementation and Maintenance
After passing, one becomes Microsoft Certified Technology Specialist: SQL Server 2005

Trigger

Earlier this week I had to disable many triggers just so that we can Restore Databases and Create Databases

The triggers were set to send out alerts on DDL events but 2 issues

1. The DBA db didn't exist on that server but the trigger is relying on that db, so I found the triggers using the below scripts, and disabled them (or a simple SELECT * FROM sys.triggers would work too)

SELECT
             sysobjects.name AS [Trigger Name],
             syscomments.TEXT AS [Trigger Definition],
             --SUBSTRING(syscomments.text, 0, 26) AS [Trigger Definition],
             OBJECT_NAME(sysobjects.parent_obj) AS [Table Name],
             syscomments.encrypted AS [IsEncrpted]
FROM
             sysobjects INNER JOIN syscomments ON sysobjects.id = syscomments.id
WHERE
             (sysobjects.xtype = 'TR')
ORDER BY [Table Name]


2. SharePoint creating database name so long that it broke the XML column used in the DDL trigger for Creating Database, so I disabled the DDL Trigger by

1. Find the trigger name (KEY!)
Server -> Server Objects -> Triggers -> trigger_name

2. DISABLE TRIGGER trigger_name

FREE SQL Server Tools

I am starting to compose a list of SQL Server Tools I personally use

Today starts with xSQL Software, especially their FREE products
http://www.xsqlsoftware.com/Product/Index.aspx

xSQL Object
FREE LITE EDITION
Compare and Synchronize SQL Server Database Schemas
generates SQL scripts too, closest to RedGate SQL Compare IMO

xSQL Script Executor
Free tool that provides for transactional execution of T-SQL scripts

xSQL Object Search
Free tool for SQL Server 2005 that locates database objects the name and/or definition of which meet a search criteria.

xSQL Script Executor
Free tool that provides for transactional execution of T-SQL scripts

RSS Reporter
FREE for 1 server
Generate standard RSS feeds for SQL Server job status, ad-hoc queries, database size and space usage.

xSQL Data Compare
FREE LITE EDITION
Compare and Synchronize SQL Server Database content

Sad SQL Joke

I read this joke today
A SQL query goes into a bar, walks up to two tables and says, "Can I join you".

Oh man...

xSQL Profiler Take 2

Today I decided to use xSQL Profiler to help me diagnose some memory pressure/paged out problems we are facing, and boy, that was probably an error (partially my fault to trace too much perhaps)

Problems:

• I was tracing a remote server A (trace files are located on A's E drive) starting 12:30 pm
• xSQL Profiler repository is located on my computer J, that go fetch trace files from server A
• I think my computer cannot catch up to the trace files creation, and in ten minutes I have 6GB of trace files on server A and running low on space.
• I killed the xSQL Profiler hoping it would stop (at this time about 80,000 rows collected)

And....

• It did not stop!! and drove my local SQL Server memory to 1GB and slowed my laptop to a crawl
• I could not start the "xSQL Profiler" as it says it cannot connect to the database despite being there
• I could not start the "xSQL Profiler Service" as it creates 10+ traces insantly on server A again
• I tried deleting entries in the local "xSQLProfiler" database tables (such as dbo.Traces, dbo.APPLICATION_SETTINGS, dbo.TRACE_SCHEDULES....) => as soon as I started xSQL Profiler, it created the 10+ traces AGAIN.
  

Fix:

• Created the SQL script below to KILL ALL traces except the default one (I ran about 10 times this afternoon)
• Renamed the trace file directory on server A's E drive [this made it stop, finally, near 3:30 pm]
• I still cannot open up the xSQL Profiler application (cannot connect to database)
• EXTREME CAUTION for NEXT TIME
  

This is the code I ended up with, and added to my toolbox
It kills ALL Profilers trace but the default SQL trace


DECLARE @traceid INT
DECLARE curDBs CURSOR FAST_FORWARD FOR -- fast forward = read only, forward only
   --Run the following Transact-SQL statement to retrieve the list of the running trace:
   SELECT DISTINCT traceid
   --,*
   FROM ::fn_trace_getinfo(NULL)   
   WHERE traceid <> 1 -- do not kill the default SQL trace

OPEN curDBs
FETCH NEXT FROM curDBs INTO @traceid
WHILE @@FETCH_STATUS = 0
BEGIN

   --Run the following Transact-SQL statement to stop the server-side trace
   EXEC sp_trace_setstatus @traceid = @traceid , @status = 0
  
   --Run the following Transact-SQL statement to close the trace and to delete the trace information
   EXEC sp_trace_setstatus @traceid = @traceid , @status = 2

   FETCH NEXT FROM curDBs INTO @traceid
END

CLOSE curDBs
DEALLOCATE curDBs

SELECT * FROM ::fn_trace_getinfo(NULL)

RETURN


Daylight Saving Time and SQL Server Job

We encountered an issue with Daylight Savings Time in our hourly transaction log after the Nov 2, 2008 Daylight Saving Time change.

Problem:
The transaction log is set to occur every hour, then copied over to standby servers for automatic restore

There was a transaction log for Nov 2, 2008 1:00 AM EDT (e.g. 200811020100.trn)

Next transaction log should be Nov 2, 2008 2:00 AM EDT (200811020200.trn), but since DST ended by one hour back at 2 am, 2 am EDT is now effectively 1 am EST
The OS time is now Nov 2, 2008 1:00 AM EST, and guess what, the transaction log file name is again 200811020100.trn, effectively overwrote the original 1 am log

How We Fixed It?
We ended up applying Differential backups since the last Full backup to bring the databases to the newest state, and then apply all the transaction logs after to catch up

How To Prevent It?
We change these servers to use UTC timestamp (namely GMT Reykjavik timezone in Windows), and increase current SQL Jobs by 5 hours ahead to run in the "correct" low traffic hours. Now we do not need to worry about the gap between 1 am & 3 am for Spring, and 1 am & 1 am for Fall.

Notes:
This problem ONLY occurs for regular-interval jobs, not for specific-time driven jobs
And supposedly the OS will never reach 2 am but 1:59:59 am EST, and then 1:00:00 am EDT so that the job will not run twice (and that SQL will adjust any job which ran between 1 and 2 am EDT for you)

Thoughts:
In short, it's shocking it's still happening, and I know why IT people love to use UTC now despite it's confusing for East Coast employees. Microsoft's How to prepare SQL Server 2005 and SQL Server 2000 for changes to daylight saving time in 2007 article.

New SQL Skills I have learned from jobs - Part 3

This is a continuation of New SQL Skills I have learned from jobs - Part 1, Part 2

 

SQL Server 2008 Spatial Index

• For the first time, native Spatial functions inside SQL Server (not 3rd party extensions) and it's a good start
• Speed is quite fast with proper usage of Spatial Index (dividing earth into quadrants and quad-trees)
• You can check for intersection, container and many other relationship between locations (either a Point, or a Polygon)

SQL Server 2008 Full-Text Search

• An improved version of the Full-Text search engine since 2000, merged into the Database Engine (instead of separate process). New features include Stoplists and Word Breakers
• Still one big black box that's hard to fully understand or customize for one's specific needs. Ability to custom-rank will be awesome if it exists. For now, it is just Google-alike text search, literally. If you want If X rank highest; If Y rank higher, etc... good luck!

SQL Server 2008 Integration Service

• I enjoyed the built-in Fuzzy Search and Fuzzy Grouping, makes finding duplicate data much easier (say looking for duplicate address based on Street Name, City, State, Zip, Phone #, etc...)
• Powerful if run on a powerful machine
• I am used to the Drag-and-Drop GUI interface for everything now - SSIS, Maintenance Plan, rarely would I need to write custom code or T-SQL scripts to extend the current abilities 

REVIEW: xSQL Profiler

I came across the xSQL software website and realized they have a few Free SQL Server products, so being the curious monkey I am, I downloaded them and installed. Here's my first usage of xSQL Profiler and a short review

What I liked:

• Easy to use and setup a quick trace
• Filter (in both setting up or querying) and interfaces are better than SQL Profiler trace
• Easy to define Category/Event Definition
• Lets you pick a repository database
• Custom Query Template is useful to find Top N queries by CPU time/Duration/Execution etc...
• Able to schedule the trace (one-time/daily/weekly/month)
• e.g. I can setup a Daily trace from 10am to 11am to get a snapshot of busy times

What could improve:

• Let me think on it... maybe more licenses for the Free version (or for me?)
• Uses up a lot of memory when Viewing Trace Data (e.g. to view 81000 rows, my local SQL Server memory incrased by ~200MB, and so did the xSQL.Profiler.UI.exe and took about 2~3 minutes to display

So far so good, it shall remain my first choice for Profiling for now, unless I am in Servers where it's not installed. Keep up the good work, xSQL! This is something Red Gate didn't have =P

New SQL Skills I have learned from jobs - Part 2

This is a continuation of New SQL Skills I have learned from jobs - Part 1

High Availability Features - Mirroring and Log Shipping

• Mirroring is a easy-to-setup high availability feature, the SSMS GUI makes it easy to setup as well
• The Restore and manual Failover is the difficult part, especially for the front end application
• Log Shipping is even better than Database Mirroring as it provides means to have a warm standby SQL Server with couple clicks and a network share

XML/XQuery

• I was right to fear the powerfulness of the XML language
• In its defense, XML provides means to store free-form data inside the database (i.e. you can consider XML a database inside a database column)
• However, performances greatly suffer compared to RDBMS structures and manipulation is not as straightforward (finding syntax alone was a challenge - .modify, .value, .query, .exist, .node etc...)

EXECUTE AS and REVERT

I learned a new thing today, switching between execution/user contexts using EXECUTE AS and REVERT.
I have been lucky in that I have not encountered too many permission problems due to

1. I am sysadmin
2. Most applications run under 'dbo' (probably bad practice)

Books Online has the perfect examples on EXECUTE AS and REVERT

This was my testing code, on testing a Database DDL Trigger


ENABLE TRIGGER Create_SP_Trigger ON DATABASE
--DISABLE TRIGGER Create_SP_Trigger ON DATABASE
GO

CREATE PROCEDURE Grant_Test_Proc
AS
PRINT 'should have triggered the trigger to auto-grant execute permission to user'
GO

--SELECT SUSER_NAME(), USER_NAME();

EXECUTE AS USER = 'testuser'   -- switches to a limited permission login
EXEC Grant_Test_Proc
GO

--SELECT SUSER_NAME(), USER_NAME();

REVERT;    -- switches to DBO and full permissions
--SELECT SUSER_NAME(), USER_NAME();
DROP PROCEDURE Grant_Test_Proc
GO



HOW TO: Import/Export SQL Server Maintenance Plan

As sqlmaint Utility will be phased out in future versions of SQL Server, Maintenance Plan is Microsoft's recommended approach for Database Maintenance tasks by the DBA

• Check Database Integrity
• Shrink Database
• Reorganize Index
• Rebuild Index
• Update Statistics
• Clean Up History
• Execute SQL Server Agent Job
• Backup Database (Full, Differential, Transaction Log)
• Maintenance Cleanup Task

Naturally, after spending the time and efforts to setup proper Maintenance Plans, you should save it and re-use elsewhere anytime you can. And here are 2 ways to export the Maintenance Plan in SQL Server 2005 and 2008

The easier GUI way
1. Open SQL Server Management Studio

2. In Object Explorer (left pane), click "Connect", choose "Integration Services..."

3. Login into the SQL Server with credentials

4. Expand SERVERNAME (Integration Services), and you'll see the below structure

• Running Packages
• Stored Packages
• File Systems
• MSDB
• Maintenance Plans <- this is what you want
  

5. Right Click on the Maintenance Plan you want to export, and select Export
 
The harder script way


USE MSDB

SELECT name
,PlanXML=CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
FROM sysdtspackages90

New SQL Skills I have learned from jobs - Part 1

I initlaly thought SQL Server was all about Transact SQL (T-SQL) programming and development, as that was how I started working with SQL Server 2000, extensively.

Then, last couple years I have came across opportunities at work to expand my knowledge of SQL Server to just about everything (shows you how little I knew)

Let's take a walk down memory lane by SELECT Name, Skills FROM Memory (NOLOCK) ORDER BY [Date] DESC

SQL Server Reporting Service (SSRS)

• Developed over 100 SSRS 2005 reports from scratch
• Learned by reading the books (very helpful) and reading online contents
• Deployed and integrated with SharePoint intranet
• Email subscriptions to management
• SSRS was quite powerful for a free product, almost can replace Crystal Reports easily

SQL Service Analysis Service (SSAS)

• Followed the book and built a demo Datawarehouse with Cubes and Dimensions, Slice & Dice!
• Connected SSRS report to report off the Cube and Dashboard/KPI
• Unfortunately didn't have the opportunity to fully try the Analysis models or Decision Trees to "predict future" from the history. That would've been very cool and helpful for business sales

 
More to comment later:

• High Availability Features - Mirroring and Log Shipping
• XML/XQuery
• SQL Server 2008 Spatial Index
• SQL Server 2008 Full-Text Search
• SQL Server Integration Service

HOW TO: Convert datetime stored in seconds to readable format

I needed to convert datetime stored in seconds to valid readable datetime, and came across this article
Apparently Unix timestamp range only from 1970 Jan 1 to 2038-01-19 at 3:14:08 AM, good to know


SELECT
scheduled_start_date, scheduled_end_date
,StartDate = DATEADD(s, scheduled_start_date, '19700101')
,EndDate = DATEADD(s, scheduled_end_date, '19700101')
FROM TABLE (nolock)

scheduled_start_date   scheduled_end_date  StartDate   EndDate
1219939200 1219942800  2008-08-28 16:00:00.000  2008-08-28 17:00:00.000
1220108400 1220133600  2008-08-30 15:00:00.000  2008-08-30 22:00:00.000
1221163200 1221163380  2008-09-11 20:00:00.000  2008-09-11 20:03:00.000
1221768000 1221786000  2008-09-18 20:00:00.000  2008-09-19 01:00:00.000
1221768000 1221786000  2008-09-18 20:00:00.000  2008-09-19 01:00:00.000
1223413200 1223416800  2008-10-07 21:00:00.000  2008-10-07 22:00:00.000
1225213200 1225215000  2008-10-28 17:00:00.000  2008-10-28 17:30:00.000
1225206000 1225209600  2008-10-28 15:00:00.000  2008-10-28 16:00:00.000
1225204200 1225206000  2008-10-28 14:30:00.000  2008-10-28 15:00:00.000
1225377000 1225378800  2008-10-30 14:30:00.000  2008-10-30 15:00:00.000
1225215000 1225216800  2008-10-28 17:30:00.000  2008-10-28 18:00:00.000
1225627200 1225628100  2008-11-02 12:00:00.000  2008-11-02 12:15:00.000

How to Ask The Right Questions the Right Way

As I started participating more and more in SQL Forums, it becomes clear that there are some small steps that could make everyone's task easier to help out.

This is a good article to start - Forum Etiquette: How to post data/code on a forum to get the best help

Personally these are my steps

1. Always try to include the SAMPLE data or tables (in SQL code, not just copy-and-paste data) unless it's syntax or concept question,
We DBAs are just like Web Designers, we like to work with what we can see

2. Beautify the SQL code, before any posting
Either using 3rd party tools such as Red Gate SQL Prompt/Refactor, or use the online The Simple-Talk Code Prettifier for SQL Server, Python, VB and C#

3. Always state what versions of software being used
For example, T-SQL changed drastically from 2000 to 2005 to 2008, one code may not work in every version

4. Willing to learn and try it out by actually doing it, and not hoping someone will take your code, and give you back a working code so that your problem is resolved by someone else.
Specifically, try to UNDERSTAND the actual solution and not just copy-and-paste. It hurts me to see users not trying to fix a clear syntax error when a clear error message presented; instead, they come back and say "it doesn't work"

I must say, the tasks of facing a challenge, conquer it and help someone out is very satisfying.

"A problem well stated is a problem half solved." -- Charles F. Kettering

SSMS Tools Pack (for SQL Management Studio 2005/2008)

I want to share a tool (FREE) that I have used extensively to help in where SSMS lack in features (such as Generate Insert Statements, run Custom Scripts, etc...) - SSMS Tools Pack

The author released the SQL 2008 version just recently, and it has worked beautifully just like the 2005 version.

The SSMS Tools Pack does NOT work for SSMS 2005 versions before SP2 anymore.
---------------------------------------------------------------
SSMS Tools PACK is an Add-In (Add-On) for Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express.

It contains a few upgrades to the IDE that I thought were missing from the Management Studio:

Query Execution History (Soft Source Control) and Current Window History.

Search Database Data.

Uppercase/Lowercase keywords.

Run one script on multiple databases.

Copy execution plan bitmaps to clipboard.

Search Results in Grid Mode and Execution Plans.

Generate Insert statements for a single table, the whole database or current resultsets in grids.

Text document Regions and Debug sections.

Running custom scripts from Object explorer's Context menu.

CRUD (Create, Read, Update, Delete) stored procedure generation.

New query template.

SQL Server 2008 Books Online (August 2008) Released

Download Microsoft SQL Server 2008 Books Online (August 2008)
The file is 141MB (SQLServer2008_BOL_August2008_ENU.msi)

I still prefers GOOGLE to find syntax help or the Books Online ONLINE at Microsoft ;-p

Red Gate SQL Compare

http://www.red-gate.com/products/SQL_Compare/index.htm

• Automate database comparisons (we don't use command mode)
• Synchronize your databases (we use it extensively)
• Work with SQL scripts, snapshots, backups or live databases
• Save hours of tedious work and eliminate manual scripting errors
• Full support for SQL Server 2008 (yeah)

---

Let's start as I promised, and with the most important Red Gate product we use (and did a case study for, see text below for excerpts)

My job is to monitor SQL Servers and maintain backup/restore, configuration and security. I also try to tune and optimize our code base for performance, and work with developers on stored procedures and database design.

As our software changed and solidified, the need to generate a diff/synch script became apparent. Every time we release new front-end code, a DB sync script needs to go with it if there are any changes. This means with each release, I compare the development database with production backup, check the sync script into TFS, and generate a build (application plus DB script).

[Company] has a license for Red Gate’s SQL Toolbelt, so I’ve used SQL Compare V7 for database migration. SQL Compare V7 is easy to use, and its support of SQL Server 2008 was crucial as we updated our platforms.
Recently, I’ve found that I can use the compare-to-backup feature in SQL Compare V7 to achieve additional benefits, including:

• Greater accuracy – comparing the development database with the verified and only copy of production backup ensures that you get only the expected changes, not unwanted or unexpected ones.

• Time savings– saves you from restoring to both SQL Server and SQL Compare.

• Ease of mind – No need to worry about finding a server to restore – you just need to know the location of the backup.

SQL Compare does what it does very well, and the team behind it at Red Gate are friendly and very helpful. That’s why we use SQL Compare and love it.

Red Gate Tools

Before this year, I have only heard of RedGate but never used it.

Now I have, as a SQL Server DBA, they have one of the best tools available for SQL Server.

I made this comment only after I have played with TOAD, Idera tools, Quest LiteSpeed, a few other tools, and even Microsoft's own IntelliSense. And of course, having done a few usability sessions and surveys with Red Gate, they have one of the best people to work with as well.

You gotta love the British people, coffee or tea? ;-)

In the next few days I will write up on my experiences of the few products I use

• SQL Compare
• SQL Backup Pro
• SQL Prompt
• SQL Refactor
• SQL Response

Not so often used, but included in Toolbelt anyway

• SQL Data Compare
• SQL Data Generator
• SQL Refactor
• SQL Dependency Tracker
• SQL Docs
• SQL Multiscript
• SQL Packager

Smooth move from SQL Server 2008 RC0 to RTM

Recently we performed a Backup-and-Restore from SQL Server 2008 Standard RC0 to another server installed with SQL Server 2008 Standard RTM.
Overall it only took 30~60 minutes for the entire process, and I am glad for Red Gate's SQL Backup (for compression)

• Backup & Compress databases on RC0
• Copy over network for shorter time
• Uncompress (using SQL Backup to MTF Converter)
• Restore on RTM
• Cleanup (update stats, index reorganize, associate logins, SQL jobs, etc...)

Free eBook - Virtualization for Dummies

Virtualization seems to be the trend for everything now. We utilize VMWare at work, and I have worked with Microsoft Virtual Server 2005 as well.

Gladly I didn't invest in VMWare who was all the hype before it fell ;-)


http://www.sun.com/systems/solutions/virtualizationfordummies/index.jsp

Virtualization for Dummies – Sun and AMD Special Edition is now available! Published by the same folks who create all the "Dummies" books – this special edition version showcases Sun and AMD virtualization offerings, how they work together, and how they can benefit businesses. Learn about the latest virtualization technologies with this brief and easy-to-read booklet.

Free eBook - Introducing Microsoft SQL Server 2008

I found it via SQLBlog.com, downloaded it and finished reading it.
To me (from the SQL Server 2005 DBA viewpoint), it was a well written overview of the SQL Server 2008 new features (and new features only). Because of our usage of SQL 2008 at work, I have greater appreciation and understanding of the new features as well.

Tips to download the PDF:

You can press F8 to display the toolbar, and "Save As"
or press Ctrl-Shift-S to save the PDF locally


Introducing Microsoft SQL Server 2008

by Peter DeBetta, Greg Low, and Mark Whitehorn
ISBN: 9780735625587


Learn about major new features in SQL Server 2008 including security, administration, and performance.


Introducing Microsoft SQL Server 2008:

Chapter 1: Security and Administration

Chapter 2: Performance

Chapter 3: Type System

Chapter 4: Programmability

Chapter 5: Storage

Chapter 6: Enhancements for High Availability

Chapter 7: Business Intelligence Enhancements

Love for SQL Server

Since I work with Microsoft SQL Server(s) daily, hourly, and yearly, I figure it may be time to start writing about them (SQL Server 2005/2008) and maybe educate myself in the writing and learning.


The recent buzz among SQL Server world was clearly the release of Microsoft SQL Server 2008 in August (RTM version, after a stable RC0 release)

Fortunately I have had chances to work with SQL Server 2008 extensively, and therefore will start to share some of my bright moments, or dark hours of the lovely SQL Server experience