Is DBA Enough?

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

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

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

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

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

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%

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

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

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

• Understanding More Complex Query Plans
• Graphical Execution Plans for Simple SQL Queries
• Execution Plan Basics

Backup is important, Protecting the Backup is equally important

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

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

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

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)

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

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

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

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

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

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

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