SQL Server Newbie

Restoring SYSTEM databases

2012-01-23T10:14:00.000-05:00

I had the opportunity to restore a few system databases due to disk inconsistency/corruption. Actually my first time in a long time and was it fun. I have a few tips to share

All below were done on a SQL Server 2005 instance

Restoring MASTER Database!!
- start SQL Server in Single User mode (add -m to the startup parameter)
- You can Google this with lots of results. Microsoft How to on SQL 2008 R2

Restoring MSDB Database
- This is easy, just restore like a user database

Restoring MODEL Database!
- Restore model in RECOVERY mode, never in NORECOVERY mode as SQL Server will stop (due to tempdb unable to create, without a model database)
- If you did NORECOVERY, the only way to get in is manual work.
For me, I started it and detach-and-attach the model database that I copy from another location, as one cannot issue RESTORE command in Single-User mode

net start MSSQLSERVER /c /m /T3608 (you can go into SQL BINN and run sqlserver -c -m -T3608 as well)

sqlcmd -E (to connect to the SQL Server)

Detach and Re-attach model databasesp_detach_db 'model'
go
sp_attach_db 'model', 'DATA.mdf', 'LOG.ldf'
go
use model
go

Command line parameter	Use
-c	Forces an instance of SQL Server not to start as a service.
-m	Allows updates to the system catalog.
-T3608	Recovers the master database only.

There is a ($50 USD) benefit to Read and Comment Simple Talk articles!

2011-07-13T10:15:00.000-04:00

Today is my lucky day as I unexpectedly found a $50 USD Amazon.com gift card in my email inbox, courtesy of simple-talk, all because I read and commented on Tony's "Cloud Backup: Getting the Users' Backs Up" article

You can subscribe to Simple-Talk's SQL and RSS, and usually each newsletter will end with this comment
Reply to my blog post to be in with the chance of winning a $50 Amazon voucher.

I just love Red Gate more and more (as Simple-Talk is owned by Red Gate)

Change the [Change Connection] shortcut in SSMS

2011-07-13T10:07:00.000-04:00

I recently found this shortcut and it has been a lifesaver to manage multiple SQL servers in the [New Query] window

I can now paste/type the SQL statement first, ALT+G, enter new SQL name, F5 to execute

http://narfle.com/blog/2006/07/ssms-keyboard-shortcut-for-change.asp

1.Open a Query Editor window connected to your favorite server.

2.Right click on the Change Connection toolbar button on the Query Toolbar button and select the Customize command at the bottom of the menu.

3.Right click on Change Connection button again and then change the command name "C&hange Connection..." to "Chan&ge Connection...". This will change the hot key from Alt-H (which would conflict with the Help menu hot key) to Alt-G.

4.Finally, in the Right Click menu for the Change Connection button, you need to select the command option to display both "Image and Text". This exposes the Alt-G command to the Query Editor.

5.You can now close the Customize dialog and use Alt-G as your hot key!

Is DBA Enough?

2009-07-24T15:41:00.000-04:00

IT is a very broad field filled with every position and every dream

I thought my transition from a Database Developer, then a Developer/DBA, and finally becoming a true DBA with MCITP certification will be the finish line. Now I am asking myself - is this it?

Let's ignore the shift from Junior DBA to Senior DBA with salary increase for the moment, I sometimes wonder if SQL is all I can talk about for my whole life. This thought freightened me, just as I do not like settling my life and always wanting to do more, at my job, at my personal life, at my goals

I have found the answer to my question, which means more efforts for the next few years, but I know it will be worthwhile, to challenge myself to be all I can be.

RowGUID

2009-06-04T11:28:00.000-04:00

I learned something new today, regarding the RowGUID property in the SSMS Table Design view

In short, to use GUID as Primary Key (not recommended in general), set the column data type to uniqueidentifier, and default value of newid(), or better newsequentialid()


CREATE TABLE Product( 
ProductID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT (newsequentialid()), 
ProductName VARCHAR(50), 
ListPrice money 
)

Only set the RowGuid property to Yes if you are using Replication

Source
The setting of RowGuid=No is correct for a GUID pk. The RowGuid is used by SQL Server replication to uniquely identify rows across replicated databases (i.e. you have have two records with the same PK on different servers and when you replicate one database into the other, SQL Server will handle it). So, the RowGuid property of a column is independent from the primary key. Generally, when you use replication and a GUID pk, you can make them the same column.

Another good read - Can Sequential_GUID be the PK?

FREE e-book from Microsoft: Introducint Microsoft SQL Server 2008

2009-06-03T11:31:00.000-04:00

You will need to sign in with your Passport/Live/Hotmail account
https://profile.microsoft.com/RegSysProfileCenter/wizard.aspx?wizid=2e29311d-516a-4817-bd65-b43e105cb666&lcid=1033

To save the book, press F8 for Toolbar and click the Save button.
Or go to the browser [File] menu and choose [Save As...]

Restore backups from a given directory

2009-05-28T14:28:00.000-04:00

I modified this script originally created by Tibor Karaszi
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp

Added it with some smarter features for work needs, and now I am here sharing it

[code]
/*
Original
http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp

Modified
-----------------------------------------------------------------
2009/05/31 Jerry Hung
Summary:
- support both 2000/2005/2008
- fills in missing \ if any
- better line break for dynamic SQL output of RESTORE
- filters on only BAK/DIFF extensions
- generate script to restore the "latest" BAK AND the latest DIFF file (if both DB.bak and DB1.diff, DB2.diff exist for example)
- restore multiple logical MDF/LDF files (if exists), and full-text catalogs
- alters database into SINGLE_USER before restore to avoid "db in use" error
-----------------------------------------------------------------

-- how to call
EXEC usp_RestoreFromAllFilesInDirectory
@SourceDirBackupFiles = '\\10.25.5.141\G$\DBA\DBVICASI1'
,@DestDirDbFiles = 'R:\MSSQL\DATA\'
,@DestDirLogFiles = 'L:\MSSQL\LOG\'
,@RecoveryMode = 'RECOVERY'
*/
/**/
CREATE PROCEDURE usp_RestoreFromAllFilesInDirectory
@SourceDirBackupFiles NVARCHAR(200) = NULL
,@DestDirDbFiles NVARCHAR(200) = 'R:\MSSQL\DATA\'
,@DestDirLogFiles NVARCHAR(200) = 'L:\MSSQL\LOG\'
,@RecoveryMode VARCHAR(100) = 'RECOVERY'
AS

/*
-- TEST
DECLARE @SourceDirBackupFiles NVARCHAR(1000)
,@DestDirDbFiles NVARCHAR(1000)
,@DestDirLogFiles NVARCHAR(1000)
,@RecoveryMode VARCHAR(100)

SET @SourceDirBackupFiles = '\\DBCA1\Z$\Recovery'
SET @DestDirDbFiles = 'R:\MSSQL\DATA\'
SET @DestDirLogFiles = 'L:\MSSQL\LOG\'

-- Jerry: set database recovery mode
SET @RecoveryMode = 'RECOVERY' -- RECOVERY/NORECOVERY
*/

SET XACT_ABORT, NOCOUNT ON

BEGIN TRAN

-- Jerry: Ensure a trailing \
IF RIGHT(@SourceDirBackupFiles, 1) <> '\'
SET @SourceDirBackupFiles = @SourceDirBackupFiles + '\'

--Table to hold each backup file name in
CREATE TABLE #files
(
fname VARCHAR(1000)
,depth INT
,file_ INT
)
INSERT #files
EXECUTE master.dbo.xp_dirtree @SourceDirBackupFiles, 1, 1

---------------------------------------------------------------------------------------------------------
-- Jerry: filter on file extensions, and only restore the LATEST FULL backup and LATEST DIFF backup (no TRN restore for now)
---------------------------------------------------------------------------------------------------------
--SELECT * FROM #files

-- delete non-backup files
DELETE #files
WHERE 1 = 1
AND fname NOT LIKE '%DIFF%'
AND fname NOT LIKE '%BAK%'

-- keep only latest BAK file
DELETE F1
FROM #files F1
WHERE 1 = 1
AND fname LIKE '%BAK%'
AND fname < (
SELECT MAX(fname)
FROM #files F2 (NOLOCK)
WHERE F2.fname LIKE '%BAK%'
AND LEFT(F1.fname, 20) = LEFT(F2.fname, 20)
)

-- keep only latest DIFF file
DELETE F1
FROM #files F1
WHERE 1 = 1
AND fname LIKE '%DIFF%'
AND fname < (
SELECT MAX(fname)
FROM #files F2 (NOLOCK)
WHERE F2.fname LIKE '%DIFF%'
AND LEFT(F1.fname, 20) = LEFT(F2.fname, 20)
)

--SELECT * FROM #files

---------------------------------------------------------------------------------------------------------
-- Jerry: section to handle SQL 2000 differently from 2005/2008
---------------------------------------------------------------------------------------------------------
DECLARE @IsSQLServer2000 BIT
IF @@VERSION LIKE '%2000%'
SET @IsSQLServer2000 = 1

-- PRINT @@VERSION

--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from
IF @IsSQLServer2000 = 1
BEGIN

CREATE TABLE #bdev2000
(
BackupName NVARCHAR(128)
,BackupDescription NVARCHAR(255)
,BackupType SMALLINT
,ExpirationDate DATETIME
,Compressed TINYINT
,Position SMALLINT
,DeviceType TINYINT
,UserName NVARCHAR(128)
,ServerName NVARCHAR(128)
,DatabaseName NVARCHAR(128)
,DatabaseVersion INT
,DatabaseCreationDate DATETIME
,BackupSize NUMERIC(20, 0)
,FirstLSN NUMERIC(25, 0)
,LastLSN NUMERIC(25, 0)
,CheckpointLSN NUMERIC(25, 0)
,DifferentialBaseLSN NUMERIC(25, 0)
,BackupStartDate DATETIME
,BackupFinishDate DATETIME
,SortOrder SMALLINT
,CodePage SMALLINT
,UnicodeLocaleId INT
,UnicodeComparisonStyle INT
,CompatibilityLevel TINYINT
,SoftwareVendorId INT
,SoftwareVersionMajor INT
,SoftwareVersionMinor INT
,SoftwareVersionBuild INT
,MachineName NVARCHAR(128)
,Flags INT
,BindingID UNIQUEIDENTIFIER
,RecoveryForkID UNIQUEIDENTIFIER
,Collation NVARCHAR(128)
)

--Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles2000
(
LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size NUMERIC(20, 0)
,MaxSize NUMERIC(20, 0)
)
END
ELSE
BEGIN
--Table to hold the result from RESTORE HEADERONLY. Needed to get the database name out from
CREATE TABLE #bdev2005
(
BackupName NVARCHAR(128)
,BackupDescription NVARCHAR(255)
,BackupType SMALLINT
,ExpirationDate DATETIME
,Compressed TINYINT
,Position SMALLINT
,DeviceType TINYINT
,UserName NVARCHAR(128)
,ServerName NVARCHAR(128)
,DatabaseName NVARCHAR(128)
,DatabaseVersion INT
,DatabaseCreationDate DATETIME
,BackupSize NUMERIC(20, 0)
,FirstLSN NUMERIC(25, 0)
,LastLSN NUMERIC(25, 0)
,CheckpointLSN NUMERIC(25, 0)
,DatabaseBackupLSN NUMERIC(25, 0)
,BackupStartDate DATETIME
,BackupFinishDate DATETIME
,SortOrder SMALLINT
,CodePage SMALLINT
,UnicodeLocaleId INT
,UnicodeComparisonStyle INT
,CompatibilityLevel TINYINT
,SoftwareVendorId INT
,SoftwareVersionMajor INT
,SoftwareVersionMinor INT
,SoftwareVersionBuild INT
,MachineName NVARCHAR(128)
,Flags INT
,BindingID UNIQUEIDENTIFIER
,RecoveryForkID UNIQUEIDENTIFIER
,Collation NVARCHAR(128)
-- new in 2005
,FamilyGUID UNIQUEIDENTIFIER
,HasBulkLoggedData INT
,IsSnapshot INT
,IsReadOnly INT
,IsSingleUser INT
,HasBackupChecksums INT
,IsDamaged INT
,BegibsLogChain INT
,HasIncompleteMetaData INT
,IsForceOffline INT
,IsCopyOnly INT
,FirstRecoveryForkID UNIQUEIDENTIFIER
,ForkPointLSN NUMERIC(25, 0)
,RecoveryModel NVARCHAR(128)
,DifferentialBaseLSN NUMERIC(25, 0)
,DifferentialBaseGUID UNIQUEIDENTIFIER
,BackupTypeDescription NVARCHAR(128)
,BackupSetGUID UNIQUEIDENTIFIER
)

--Table to hold result from RESTORE FILELISTONLY. Need to generate the MOVE options to the RESTORE command
CREATE TABLE #dbfiles2005
(
LogicalName NVARCHAR(128)
,PhysicalName NVARCHAR(260)
,Type CHAR(1)
,FileGroupName NVARCHAR(128)
,Size NUMERIC(20, 0)
,MaxSize BIGINT
-- new in 2005
,FileId INT
,CreateLSN NUMERIC(25, 0)
,DropLSN NUMERIC(25, 0)
,UniqueId UNIQUEIDENTIFIER
,ReadOnlyLSN NUMERIC(25, 0)
,ReadWriteLSN NUMERIC(25, 0)
,BackupSizeInBytes BIGINT
,SourceBlockSize INT
,FilegroupId INT
,LogGroupGUID UNIQUEIDENTIFIER
,DifferentialBaseLSN NUMERIC(25)
,DifferentialBaseGUID UNIQUEIDENTIFIER
,IsReadOnly INT
,IsPresent INT
)

END

DECLARE @fname VARCHAR(1000)
DECLARE @dirfile VARCHAR(1000)
DECLARE @LogicalName NVARCHAR(1000)
DECLARE @PhysicalName NVARCHAR(1000)
DECLARE @type CHAR(1)
DECLARE @DbName SYSNAME
DECLARE @sql NVARCHAR(2000)
DECLARE @LogicalCounter TINYINT
DECLARE @recoverySQL VARCHAR(4000)
SET @recoverySQL = ''

DECLARE files CURSOR FAST_FORWARD
FOR SELECT fname
FROM #files
WHERE [file_] = 1

IF @IsSQLServer2000 = 1
DECLARE dbfiles CURSOR FAST_FORWARD
FOR SELECT LogicalName
,PhysicalName
,Type
FROM #dbfiles2000

ELSE
DECLARE dbfiles CURSOR FAST_FORWARD
FOR SELECT LogicalName
,PhysicalName
,Type
FROM #dbfiles2005

OPEN files
FETCH NEXT FROM files INTO @fname

WHILE @@FETCH_STATUS = 0
BEGIN
SET @dirfile = @SourceDirBackupFiles + @fname

--Get database name from RESTORE HEADERONLY, assumes there's only one backup on each backup file.
IF @IsSQLServer2000 = 1
BEGIN
TRUNCATE TABLE #bdev2000
INSERT #bdev2000
EXEC
('RESTORE HEADERONLY FROM DISK = ''' + @dirfile
+ ''''
)
--SELECT * FROM #bdev
SET @DbName = (
SELECT TOP 1
DatabaseName
FROM #bdev2000
)
END
ELSE
BEGIN
TRUNCATE TABLE #bdev2005

INSERT #bdev2005
EXEC
('RESTORE HEADERONLY FROM DISK = ''' + @dirfile
+ ''''
)
--SELECT * FROM #bdev2005

SET @DbName = (
SELECT TOP 1
DatabaseName
FROM #bdev2005
)
END

--Construct the beginning for the RESTORE DATABASE command
SET @sql = 'RESTORE DATABASE [' + @DbName + '] FROM DISK = N'''
+ @dirfile + ''''
--+ char(13)+char(10)
--PRINT('RESTORE HEADERONLY FROM DISK = ''' + @dirfile + '''')

---------------------------------------------------------------------------------------------------------
-- Jerry: Only add logical name parts if FULL backup restore; skip the logical name part for DIFF file
---------------------------------------------------------------------------------------------------------
IF @dirfile LIKE '%.BAK'
BEGIN
--PRINT('RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + '''')

--Get information about database files from backup device into temp table
IF @IsSQLServer2000 = 1
BEGIN
TRUNCATE TABLE #dbfiles2000
INSERT #dbfiles2000
EXEC
('RESTORE FILELISTONLY FROM DISK = '''
+ @dirfile + ''''
)
END
ELSE
BEGIN

TRUNCATE TABLE #dbfiles2005

INSERT #dbfiles2005
EXEC
('RESTORE FILELISTONLY FROM DISK = '''
+ @dirfile + ''''
)

--SELECT * FROM #dbfiles2005
END

--SELECT LogicalName, PhysicalName, Type FROM #dbfiles
SET @sql = @sql + CHAR(13) + CHAR(10) + 'WITH'

OPEN dbfiles
FETCH NEXT FROM dbfiles INTO @LogicalName, @PhysicalName,
@type
--For each database file that the database uses
---------------------------------------------------------------------------------------------------------
-- Jerry: capable of handling multiple LDF file (DB1.ldf, DB2.ldf, etc...)
---------------------------------------------------------------------------------------------------------
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = @sql + CHAR(13) + CHAR(10) + ' MOVE '

IF @type = 'D' -- Data
BEGIN
SET @sql = @sql + '''' + @LogicalName
+ ''' TO ''' + @DestDirDbFiles + @DbName
+ '.mdf'','
SET @LogicalCounter = 0
END
ELSE
BEGIN
IF @type IN ('L') -- Log
BEGIN
SET @sql = @sql + '''' + @LogicalName
+ ''' TO ''' + @DestDirLogFiles
+ @DbName + CASE @LogicalCounter
WHEN 0 THEN ''
ELSE CAST(@LogicalCounter AS VARCHAR)
END + '.ldf'','
SET @LogicalCounter = @LogicalCounter
+ 1
END
ELSE
---------------------------------------------------------------------------------------------------------
-- Jerry: restore full-text as best as we can
---------------------------------------------------------------------------------------------------------
IF @type IN ('F') -- Full-text
BEGIN
SET @sql = @sql + ''''
+ @LogicalName + ''' TO '''
+ @DestDirDbFiles + @DbName
+ CASE @LogicalCounter
WHEN 0 THEN ''
ELSE CAST(@LogicalCounter AS VARCHAR)
END + '.' + @LogicalName + ''','
SET @LogicalCounter = @LogicalCounter
+ 1
END
END
FETCH NEXT FROM dbfiles INTO @LogicalName,
@PhysicalName, @type
END

CLOSE dbfiles

SET @sql = @sql + CHAR(13) + CHAR(10) + 'REPLACE, STATS, '
+ @RecoveryMode
END
ELSE
SET @sql = @sql + CHAR(13) + CHAR(10) + 'WITH REPLACE, STATS, '
+ @RecoveryMode

--Here's the actual RESTORE command
PRINT 'PRINT ''--RESTORE FILELISTONLY FROM DISK = ''''' + @dirfile
+ '''' + '''' + ''''
--PRINT '--RESTORE FILELISTONLY FROM DISK = ''' + @dirfile + ''''
---------------------------------------------------------------------------------------------------------
-- Jerry: Set ONLINE DB to single user before restore
---------------------------------------------------------------------------------------------------------
IF EXISTS ( SELECT [name]
FROM master.dbo.sysdatabases (NOLOCK)
WHERE 1 = 1
AND NAME = @dbname
AND DATABASEPROPERTYEX([name], 'IsInStandBy') = 0 -- not in Standby mode
AND DATABASEPROPERTYEX([name], 'Status') = 'ONLINE' -- only worry about ONLINE DB's
)
PRINT 'ALTER DATABASE [' + @DbName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'

PRINT @sql
PRINT 'GO'
PRINT ''

IF @RecoveryMode = 'NORECOVERY'
BEGIN
SET @recoverySQL = @recoverySQL + 'RESTORE DATABASE [' + @DbName + '] WITH RECOVERY;' + CHAR(10)
END

--Remove the comment below if you want the procedure to actually execute the restore command.
--EXEC(@sql)

FETCH NEXT FROM files INTO @fname

END

-- provide a quick way to change to RECOVERY mode from NORECOVERY
PRINT '/*'+ @recoverySQL + '*/'

-- cleanup section
----------------------------------------------------------------------
CLOSE files
DEALLOCATE dbfiles
DEALLOCATE files

DROP TABLE #files

IF @IsSQLServer2000 = 1
BEGIN
DROP TABLE #bdev2000
DROP TABLE #dbfiles2000
END
ELSE
BEGIN
DROP TABLE #bdev2005
DROP TABLE #dbfiles2005
END
----------------------------------------------------------------------

COMMIT

GO

/*
Outline
Below stored procedure reads the contents of a number of backup files in a directory and based on that generates RESTORE DATABASE commands. The outline of the procedure is:

* Use xp_dirtree to save all file names in a directory in a temp table.
* For each file, EXEC RESTORE HEADERONLY into a temp table to get the database name from the backup file.
* Use EXEC and RESTORE FILELISTONLY into a temp table so we can go through that and generate MOVE for each database file.
* Print out the RESTORE commands.

Usage

@SourceDirBackupFiles nvarchar(200)
This is the name of the directory where the backup files are stored.

@DestDirDbFiles nvarchar(200)
This is the name of the directory where the databases' data files are to be created.

@DestDirLogFiles nvarchar(200)
This is the name of the directory where the databases' log files are to be created.

Note that the procedure doesn't execute the RESTORE commands; it only outputs them to the result window so you can go through them before pasting them to the query window and executing them.

Limitations
Only one backup on each backup file.
Only database backups in the files.
Only one mdf and one ldf file per database.

Sample execution
EXEC sp_RestoreFromAllFilesInDirectory 'C:\Temp\', 'C:\SqlDataFiles\', 'D:\SqlLogFiles\'

Copyright Tibor Karaszi, Nucleus Datakonsult, 2004. Use at own risk.
Restores from all files in a certain directory. Assumes that:
There's only one backup on each backup device.
Each database uses only two database files and the mdf file is returned first from the RESTORE FILELISTONLY command.
Modified to work with SQL Server 2005 [Andreas Moe, Ole Robin 2008]:
Added posibility to put log files in different location than database file, altered if statement
Updated Table creating #bedev and #dbfiles to suite SQL2005(also works with SQL2000), more columns added
Sample execution:
EXEC sp_RestoreFromAllFilesInDirectory 'C:\Mybakfiles\', 'D:\Mydatabasesdirectory\' ,’C:\MylogDirectory\’

*/
[/code]

Great simple tip on how to delete ordered data fast

2009-05-21T10:51:00.000-04:00

SQLCAT team posted a simple yet elegant solution to an often-encountered problem during archiving or deleting the oldest data
http://sqlcat.com/msdnmirror/archive/2009/05/20/fast-ordered-delete.aspx

Great use of the "Can only use TOP in VIEW with ORDER BY" limit


CREATE VIEW v1 AS (SELECT TOP (10000) * FROM TABLE ORDER BY DATETIME)

--and we can delete the “top” rows using simply

DELETE FROM v1

MCITP: Database Administrator

2009-04-13T14:22:00.000-04:00

I passed the 70-443 exam today, and last of the 3 part exams for MCITP: Database Administrator. Pat myself on the shoulder, attaboy!

Exam 70-431
TS: Microsoft SQL Server 2005 - Implementation and Maintenance

Exam 70-443
PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005

Exam 70-444
PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005

No, I am not the guy in the picture :P

Canada Unemployment Rate at 8%, 7-year High. Waterloo region 9.6%

2009-04-09T09:51:00.000-04:00

It is scary, 9.6% in Waterlo means every 10 people you know, 1 is unemployed

Another reason to be grateful for my job, and to do better in it every day.

Canada's unemployment rate rose to 8 percent in March, up from 7.7 percent in February.

Statistics Canada says just over 61-thousand people lost their jobs last month. That's more than private sector economists had been forecasting.
They'd predicted about 55-thousand jobs likely vanished in March.
Most of the losses were in full-time work in manufacturing and construction.

Waterloo Region's unemployment rate rose to 9.6 percent in March from 9.1 the previous month.
Roughly 4-thousand full and part-time workers lost their jobs in our region last month.

Ontario's jobless rate stayed the same February to March at 8.7 per cent.
In the last 5 months the agency says Canada has shed 357-thousand jobs.

Source: 570news

SQL Server 2008 SP1 Released

2009-04-08T14:37:00.000-04:00

SQL Server 2008 Service Pack 1 Download

We remain committed to our plans to keep service packs contained, focusing on essential updates only, primarily a Roll-up of Cumulative Update 1 to 3, Quick Fix Engineering (QFE) updates, as well as fixes to issues reported through the SQL Server community. While keeping product changes contained, we have made significant investments to ease deployment and management of Service Packs:

* Slipstream – You are now able to integrate the base installation with service packs (or Hotfixes) and install in a single step.

* Service Pack Uninstall – You are now able to uninstall only the Service Pack (without removing the whole instance)

* Report Builder 2.0 Click Once capability

For more information about SQL Server 2008 Service Pack 1, please review the Release Notes.

System Requirements
* Supported Operating Systems: Windows Server 2003; Windows Server 2008; Windows Vista
* 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

FREE e-book: Dissecting SQL Server Execution Plans

2009-04-01T12:25:00.000-04:00

This is a great book to read about SQL Server Execution Plans, especially free, courtesy of Red Gate

http://downloads.red-gate.com/ebooks/HighPerformanceSQL_ebook.zip

Some of Grant's articles on Execution Plan to show the quality of his work.
They are the best simple-to-understand Execution Plan articles I have read so far, all bookmark'ed for future reference

Backup is important, Protecting the Backup is equally important

2009-03-26T13:20:00.000-04:00

It is a sad day when hacker(s) deliberately attached WebHostingTalk and this is just another incident recently that relates to database backups (after Carbonite, Ma.gnolia, JournalSpace)

As a DBA, it is hard to not imagine the worst for the databases we manage, and I will consult with my colleagues to ensure that our tape backup is safe from attack (both on-site AND off-site)

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

Hello fellow WHTers!

It's been pretty hectic around here, but I wanted to make sure as many members as possible know what's going on. At approximately 8:30 pm EST on Saturday, March 21 The malicious attacker deleted all backups from the backup servers within the infrastructure before deleting tables from our db server. We were alerted of the db exploitation and quickly shut down the site to prevent further damage.

We've tried to answer any questions or concerns in the following thread posted at http://www.webhostingtalk.com/showthread.php?t=729727.
Be sure to subscribe if you want to stay informed.

Remember, you can follow us on Twitter @WebHostingTalk.

WHT Data - Q&A Information
========================

What do we know about the damage done?
This attack was very deliberate, sophisticated and calculated. The attacker was able to circumvent our security measures and access via an arcane backdoor protected by additional firewall. We are still investigating the situation, but we know the attacker infiltrated and deleted the backups first and then deleted three databases: user/post/thread. We have no record or evidence that private message data was accessed. Absolutely no credit card or PayPal data was exposed.

Do we know the motivation behind the attack?
We don’t know enough at this time, so any insight would be purely speculative in nature. WHT is a platform where positive and negative information is shared and exposed about business and individuals. Under TOS policy, we cannot edit or remove user-generated content at the request of an unsatisfied third party. Therefore, WHT tends to become the target for disgruntled individuals and businesses.

Have we been able to restore more recent back-ups?
The offsite backup, the onsite backup and the operational data were destroyed by the attacker, so we’ve resorted to a physical back-up of last resort. Unfortunately, we are experiencing difficulty restoring from our most recent physical backup. At this point, October is the most recent backup that we were able to restore. We continue to work to extract data from a more recent set of DVDs. What is WHT focused on doing now?

The first priority, which kicked in immediately upon discovering the hack while in process, was locking down the infrastructure to avoid further damage and restoring the site. We also had to block the potential for a repeat attack. Now we are working on investigating how much prior data is restorable, reinstating premium memberships, contacting business partners, and communicating with the community members. We are also doing everything possible to identify the attacker and bring them to justice. Disappointments happen – we are working hard to restore trust among community members and to bring things back to normal.

Is WHT doing anything different due to this attack?
WHT has been targeted before and our infrastructure has withstood previous attacks. However, following this well-planned and targeted attack, we will be altering aspects of our architecture to ensure that this type of attack does not happen again. Needless to say, we have learned from this situation and will address any discrepancies accordingly.

We had three, protected data back-up units with one offsite behind a firewall and a fourth physical data back-up layer. We evaluated our disaster recovery plan as recent as late-2008, and carefully reviewed how to recover from a disaster situation. The attacker appeared to have deliberately targeted our data back-up systems, a scenario that our disaster recovery plan did not fully anticipate. We have implemented changes to our data backup and disaster recovery plans to address this weakness. And we advise others to consider a scenario of deliberate, malicious data destruction in their backup and recovery plans.

What should members do now?
The password encryption technology we use is strong for securing non-financial data. However, we suggest that members change their passwords frequently and do not use the same user name and password for the forum as they may use for more sensitive services like online banking. If a member feels more comfortable changing their password, then we recommend that they do what makes them feel more secure.

A concern is that members may receive more spam because the attacker posted stolen email addresses on file sharing sites. I haven’t personally seen an increase in the amount of spam I usually receive to my email address, but it is a risk that we cannot easily alleviate. As we become aware of specific file sharing sites with these email addresses, we are requesting that the emails be removed promptly. So far, most have been quick to comply.

What if I can’t use my WHT account?
We are temporarily using a version of the database from October 2008. This means that if you joined WHT after October 2008, you’ll need to register again to post now. We may still be able to recover your account, but we don’t know yet. Please register with the same username you used before.

If you joined WHT before October 2008 and get a password error, the system is probably asking for the password you were using in October 2008. If you don’t remember your previous password and have access to the email address for your WHT account in October 2008, please use the password recovery tool.

Get updates on this topic here.

For help accessing your account, please open a helpdesk ticket.

If you’ve subscribed to a Premium or Corporate membership prior to October 2008, someone from iNET has contacted you by now. If you’ve subscribed (or re-subscribed) since October 2008 and haven’t heard from iNET, please contact us on the helpdesk.

Moving forward ...
We take the protection of user-contributed data very seriously, and we strongly regret what happened. iNET has a sophisticated infrastructure with advanced security. Yet even institutions that spend millions of dollars a year on Internet security are exploited. Anyone recall NASA being hacked some years back?

It’s not what you’ve done, it’s what you do. And from this day forward, we continue.

We’ve been overwhelmed by all the offers of help and support we’ve received from our members. What can I say about that beyond my heartfelt thanks? I love this community!

HOWTO: Enable and Configure Database Mail, Add SQL Agent Operator

2009-03-24T11:04:00.000-04:00

Why
If you are a DBA and want to receive ANY kind of alert/notification email from the SQL Server, you need to setup the Database Mail first, just like you have to configure Outlook to connect with Exchange before receiving any email.

Who
DBAs who want to get notified for any pre-configured alerts or job notifications

How
Prerequisites

External SMTP mail server, or Microsoft exchange server

Steps

Creates a Database Mail Profile
Creates a Database Mail Account
Adds SQL Agent Operator
Enables SQL Agent to use the Database Mail Profile (needs to manually restart SQL Agent)

When
Run this setup only for initial SQL Server configuration.
Normally there is no need to re-run afterwards

Where
SQL Server 2005/2008

Reference: this code was taken and modified from Jonathan's "Configuring SQL Server 2005/2008 Database Mail"

What

USE [master]
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE 
GO
----------------------------------------------------------------------------------------------------------------
-- Create a New Mail Profile for Notifications
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'DBA_Notifications',
    @description = 'Profile for sending Automated DBA Notifications'
GO
-- Set the New Profile as the Default
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'DBA_Notifications',
    @principal_name = 'public', @is_default = 1 ;
GO
-- Create an Account for the Notifications
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'SQLMonitor',
    @description = 'Account for Automated DBA Notifications',
    @email_address = 'sqlnotify@domain.com',  -- ************ Change This ************
    @display_name = 'SQL Monitor',
    @mailserver_name = 'exchange.domain.com'-- ************ Change This ************  
GO
-- Add the Account to the Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'DBA_Notifications',
    @account_name = 'SQLMonitor', @sequence_number = 1
GO

----------------------------------------------------------------------------------------------------------------
--Adds Operator for Job Notification
USE [msdb]
GO

EXEC msdb.dbo.sp_add_operator @name = N'SQLDBAs', @enabled = 1,
    @weekday_pager_start_time = 90000, @weekday_pager_end_time = 180000,
    @saturday_pager_start_time = 90000, @saturday_pager_end_time = 180000,
    @sunday_pager_start_time = 90000, @sunday_pager_end_time = 180000,
    @pager_days = 0, @email_address = N'sqlnotify@domain.com',-- ************ Change This ************
    @category_name = N'[Uncategorized]'
GO

----------------------------------------------------------------------------------------------------------------
-- Enable SQL Server Agent to use Database Mail profile (in Alert System tab)
-- restart SQL Agent after
USE [msdb]
GO
EXEC MASTER.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',
    N'REG_SZ', N'DBA_Notifications'
GO

HOW TO: Setup Cluster Failover Alert Email

2009-03-20T09:00:00.000-04:00

To be creative, I will try to model my posts into the below format, dragging my lazy bum to finally come back and post.

Why
It is strange a few years as DBA I have not yet seen a de-facto Cluster Alert solution. One can monitor event logs, SQL error logs, but there is no wizard in SQL/Windows Cluster to setup alerts for failover. With help from Google, I used this solution to send me email when a cluster fails over (i.e. SQL Server Agent will fail over too and cause the job to run).

To my surprise, not that it is something I want to happen, it worked and sent me an email Tuesday
I am certain there is a better way, someone please let me know.

Who
DBAs who want to get notified when cluster failed over

How
Prerequisites (I will post on this tomorrow)
- Database Mail profile & account configured
- SQL Server Agent operator created

The job sends an Email to the specified Operator when it runs

JOB RUN: 'Cluster Alert' was run on 3/17/2009 at 4:06:40 PM

DURATION: 0 hours, 0 minutes, 0 seconds
STATUS: Succeeded
MESSAGES: The job succeeded. The Job was invoked by Start Sequence 0. The last step to run was step 1 (Cluster).

When
The job is scheduled to run WHEN SQL Server Agent Starts, which happens when the cluster service fails over

Where
SQL Server 2005/2008

What

USE [msdb]

GO

/****** Object:  Job [Cluster Alert]    Script Date: 03/19/2009 16:27:37 ******/

BEGIN TRANSACTION

DECLARE  @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [Database Maintenance]    Script Date: 03/19/2009 16:27:37 ******/

IF NOT EXISTS (SELECT name

               FROM   msdb.dbo.syscategories

               WHERE  name = N'Database Maintenance'

                      AND category_class = 1)

  BEGIN

    EXEC @ReturnCode = msdb.dbo.Sp_add_category

      @class = N'JOB' ,

      @type = N'LOCAL' ,

      @name = N'Database Maintenance'

    IF (@@ERROR <> 0

         OR @ReturnCode <> 0)

      GOTO quitwithrollback

  END

DECLARE  @jobId BINARY(16)

EXEC @ReturnCode = msdb.dbo.Sp_add_job

  @job_name = N'Cluster Alert' ,

  @enabled = 1 ,

  @notify_level_eventlog = 0 ,

  @notify_level_email = 3 ,

  @notify_level_netsend = 0 ,

  @notify_level_page = 0 ,

  @delete_level = 0 ,

  @description = N'No description available.' ,

  @category_name = N'Database Maintenance' ,

  @owner_login_name = N'sa' ,

  @notify_email_operator_name = N'SqlDbas' ,

  @job_id = @jobId OUTPUT

IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback

/****** Object:  Step [Cluster]    Script Date: 03/19/2009 16:27:37 ******/

EXEC @ReturnCode = msdb.dbo.Sp_add_jobstep

  @job_id = @jobId ,

  @step_name = N'Cluster' ,

  @step_id = 1 ,

  @cmdexec_success_code = 0 ,

  @on_success_action = 1 ,

  @on_success_step_id = 0 ,

  @on_fail_action = 2 ,

  @on_fail_step_id = 0 ,

  @retry_attempts = 0 ,

  @retry_interval = 0 ,

  @os_run_priority = 0 ,

  @subsystem = N'TSQL' ,

  @command = N'print ''cluster''' ,

  @database_name = N'master' ,

  @flags = 0

IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback

EXEC @ReturnCode = msdb.dbo.Sp_update_job

  @job_id = @jobId ,

  @start_step_id = 1

IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback

EXEC @ReturnCode = msdb.dbo.Sp_add_jobschedule

  @job_id = @jobId ,

  @name = N'SQLAgentStart' ,

  @enabled = 1 ,

  @freq_type = 64 ,

  @freq_interval = 0 ,

  @freq_subday_type = 0 ,

  @freq_subday_interval = 0 ,

  @freq_relative_interval = 0 ,

  @freq_recurrence_factor = 0 ,

  @active_start_date = 20090309 ,

  @active_end_date = 99991231 ,

  @active_start_time = 0 ,

  @active_end_time = 235959

IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback

EXEC @ReturnCode = msdb.dbo.Sp_add_jobserver

  @job_id = @jobId ,

  @server_name = N'(local)'

IF (@@ERROR <> 0

     OR @ReturnCode <> 0)

  GOTO quitwithrollback

GOTO endsave

QUITWITHROLLBACK:

IF (@@TRANCOUNT > 0)

  ROLLBACK TRANSACTION

ENDSAVE:

GO

SQL Server Enterprise Policy Management

2009-03-19T16:22:00.000-04:00

I came across the Enterprise Policy Management that utilized the SQL 2008 policy and serves a DBA's desire to centralize monitor/govern multiple database servers

Downloaded and set it up yesterday mostly fine, and the last obstacle was addressed today thanks to Lara's help - it really does NOT support "nested/2nd level" Central Management Server group

So far, my 2 test servers are 100% green :-)

Project Description

The Enterprise Policy Management Framework is a reporting solution on the state of the enterprise against a desired state defined in a policy. Extend Policy-Based Management to all SQL Server instances in the enterprise. Centralize and report on the policy evaluation results.

The Enterprise Policy Management Framework (EPM) is a solution to extend SQL Server 2008 Policy-Based Management to all versions of SQL Server in an enterprise, including SQL Server 2000 and SQL Server 2005. The EPM Framework will report the state of specified SQL Server instances against policies that define the defined intent, desired configuration, and deployment standards.

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

2009-02-23T13:14:00.000-05:00

Can't believe I missed it, but new version of my favourte "free" SQL Management Studio add-ins is available, SSMS Tools Pack version 1.5

New/improved features include:

Window Connection Coloring (a colored strip indicator that can be docked to any side of the window) - I should compare this to the SQL 2008 Central Management Server, where I set my production servers to be RED status bar for warning purpose
Search Table or Database Data
Uppercase/Lowercase keywords and Proper Case Database Object Names
Sending feedback directly from SSMS.
Import and Export options.
Delete Query history log files older than user settable age.

FREE e-book - Mastering SQL Server Profiler

2009-02-18T13:06:00.000-05:00

I came across this free e-book via Red Gate/SQL Server Central Database Weekly email
It is 283 pages, but looks to be a good read/tool book

Volume 2: Mastering SQL Server Profiler (10MB PDF)

I hope it's okay to list some useful contents I find (below are copied from the contents, copyright by Red Gate):

Contents 1
Getting Started with Profiler 5
The inner workings of Profiler 6
Profiler terminology 7
Getting Started with Profiler 9
Summary 21
Working with Traces and Templates 22
Understanding the Events Selection Options 22
Creating a Custom Trace from Scratch 25
Saving Traces to a SQL Server Table 35
Capturing Analysis Server Traces 37
Creating and Using Trace Templates 40
Summary 50
Profiler GUI Tips and Tricks 52
Summary 72
How to Identify Slow Running Queries 74
Creating a Trace to Identify Slow Running Queries 74
Analyzing the Poorly Performing Queries Identified by Profiler 83
Summary 94
How to Identify and Troubleshoot SQL Server Problems 95
How to Identify Deadlocks 95
How to Identify Blocking Issues 103
How to Identify Excessive Auto Stats Activity 109
How to Identify Excessive Statement Compilations 112
How to Identify Excessive Database File Growth/Shrinkage 118
How to Identify Excessive Table/Index Scans 122
How to Identify Memory Problems 126
Using Profiler to Audit Database Activity 131
Capturing Audit Events 132
Selecting Data Columns 134
Selecting Column Filters 136
Organizing Columns 137
How to Conduct an Audit Trace 138
Summary 140
Using Profiler with the Database Engine Tuning Advisor 141
Features and Benefits of Using the DTA 141 3
How to Create a Trace for Use by the DTA 142
Summary 156
Correlating Profiler with Performance Monitor 157
How to Collect Profiler Data for Correlation Analysis 157
How to Collect Performance Monitor Data for Correlation Analysis 159
How to Capture Profiler Traces and Performance Monitor Logs 170
How to Correlate SQL Server 2005 Profiler Data with Performance Monitor Data 171
How to Analyze Correlated Data 176
Summary 186
How to Capture Profiler Traces Programmatically 187
Summary 200
Profiler Best Practices 201
General Profiler Best Practices 201
Creating Traces 204
Running Traces 205
Analyzing Traces 206
Performance Monitor 207
Database Engine Tuning Advisor 208
Summary 208
Profiler Events and Data Columns Explained 209
Event Categories 209
Profiler Data Columns 274
Summary 281

SQL Server Certification Statistics

2009-02-13T09:46:00.000-05:00

Saw this interesting statistics today (source: Greg Low) and wow!

I want to emphasize that I am also a believer in real world experiences over certification though, for myself, and for any future persons I may hire

SQL Server related stats from Microsoft:

MCDBA SQL 2000 152086

MCTS
SQL 2005 41665
SQL 2005 BI 2600

SQL 2008 Dev 336
SQL 2008 BI 134

MCITP
SQL 2005 DBA 6695
SQL 2005 Dev 2925
SQL 2005 BI 1088

SQL 2008 DBA 92
SQL 2008 BI 50

MCM
SQL 2005 18
SQL 2008 2

Joke - I think, therefore I am

2009-01-09T14:19:00.000-05:00

This is a funny joke on Rene Descartes' famous statement - I think, therefore I am;

Descartes' wife: Rene - did you remember to pick up the milk?

Descartes: I don't think..(Descartes disappears)

Passed 70-444 Exam

2009-01-07T15:09:00.000-05:00

In a better than expected snowstorm, I arrived safely and finished the 70-444 exam quickly with perfect score

With 70-444 gone, I'll only have 70-443 left before getting the MCITP: Database Administrator

And one resolution achieved within a week of the new year

Then there is maybe MBA after that.... the never-ending learning in life

Taking my 70-444 Exam tomorrow, in snowstorm

2009-01-06T15:07:00.000-05:00

Not too shocked, the day I arranged a month ago to take my 70-444 exam, is the day where there will be 10~12 cm of snow. Great... I don't feel as ready as 70-431 already, and the weather isn't helping

Fingers crossed!

HAPPY NEW YEAR, 2009

2009-01-01T03:10:00.000-05:00

I want to wish everybody a HAPPY HAPPY NEW YEAR and a better 2009

New Year Resolutions for my SQL career

Obtain my MCITP: SQL Server 2005 shortly
Able to revamp our company backup policy and structure to make it better (smaller, faster, easier)
Work with Cluster and become an expert
Improve my management, social, and business skills

SQL Server 2005 SP3 is out today!

2008-12-16T11:29:00.000-05:00

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

2008-12-15T15:37:00.000-05:00

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

2008-12-09T10:52:00.000-05:00

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

2008-12-05T10:36:00.000-05:00

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

2008-12-05T09:42:00.000-05:00

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

2008-11-27T12:50:00.000-05:00

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

2008-11-25T15:21:00.000-05:00

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

2008-11-18T13:19:00.000-05:00

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

2008-11-10T11:58:00.000-05:00

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

2008-11-07T11:52:00.000-05:00

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

2008-11-05T11:48:00.000-05:00

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

2008-10-31T14:40:00.000-04:00

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

I am sysadmin
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

2008-10-30T14:25:00.000-04:00

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

2008-10-30T10:00:00.000-04:00

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

2008-10-28T14:30:00.000-04:00

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

2008-10-24T15:34:00.000-04:00

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)

2008-10-24T10:32:00.000-04:00

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

2008-10-02T10:44:00.000-04:00

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

2008-09-30T10:20:00.000-04:00

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

2008-09-26T10:03:00.000-04:00

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

2008-09-25T11:22:00.000-04:00

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

2008-09-21T22:41:00.000-04:00

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

2008-09-21T12:43:00.000-04:00

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

2008-09-21T12:40:00.001-04:00

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