Tuesday, March 27, 2012

SQL Server 2012 Synopsis - Mission Critical Applications Combined With The Fantastic 12 New Features or "The Baker's Dozen" as described by Microsoft this Week


Notes by Hugo S., Print Screens from three Microsoft Presentations


7 December, 2011 with Additions combined from early February 2012 day-long presentation of Server 2012’s Top 12 new features. Thank you Frank Wiemer (Data Platform head, Eastern Canada, based in Montreal, for your presentation slides). With these notes, I have tried to cut out any marketing spiel and just give the fact summary that’ll hopefully enable those who wish to have a great and deep overview of what is just about to be released (or already has by the time you read this). Click the Word icon below for the SkyDrive version, on Office Web Apps, Or the complete Word Doc from my Server

At the entrance to the tunnel, atop Penon D'Ifache, Calpé, Spain



Tuesday, March 20, 2012

Free E-Book on SQL Server 2012, Great Job by Ross Mistry and Stacia Misner (yes, they have done it again :)


Free E-Book: Introduction to SQL Server 2012

Ross Mistry and Stacia Misner have again done a wonderful job on this free e-book


You can the full details of the introduction here.



The book contains 10 chapters and 268 pages:

PART I   DATABASE ADMINISTRATION (by Ross Mistry)
1.   SQL Server 2012 Editions and Engine Enhancements
2.   High-Availability and Disaster-Recovery Enhancements
3.   Performance and Scalability
4.   Security Enhancements
5.   Programmability and Beyond-Relational Enhancements
PART II   BUSINESS INTELLIGENCE DEVELOPMENT (by Stacia Misner)
6.   Integration Services
7.   Data Quality Services
8.   Master Data Services
9.   Analysis Services and PowerPivot
10.   Reporting Services


Embark on SQL 2008 R2 with the Free E-Book

Sunday, March 18, 2012

Activity Monitor in SQL Server 2012: an Improved Embedded Optimisation Gizmo for the Thrifty DBA

Waaaay back in December ‘08, after having spoken briefly at SQL teach here in Montreal thanks to MVP Paul Nielsen (SQLserverBible.com), on this subject, the following is a re-introduction to one of our favorite Management Studio features: Activity Monitor. 
To use Activity Monitor, right click on a SQL Server 2005/8/12 instance in the object explorer and take a look,  or click on the farthest right icon in the SQL Server Management Studio toolbar (image below). It will perhaps inspire you to take care of what’s bogging down your database engine and motivate you for some good ol’ Spring cleaning.

Activity Monitor on the SQL Management Studio tool bar
SQL Server Management Studio's Activity Monitor Icon, bottom right
The latest version of Activity Monitor helps you see the processes, users, and applications that are currently using the database in the sortable/filterable Processes pane, and, of course, whether they are blocking transactions. Even if you do not have SQL 2008/12 instances, it is worth it to install SQL Server Management Studio complete client tools (SSMS, now in 2012 version RTM, is the client tool acronym to manage your SQL Server infrastructure) just to have this critical problem resolution feature known as Activity Monitor (click image below to see a full view of all the panes). 



Activity Monitor has built-in backward compatibility; thus, you can take advantage of the dynamic management views already existing since SQL Server 2005 while connecting from a SQL Server 2008/12 Management Studio Activity Monitor. Previously, to view the equivalent information in the Activity Monitor, was, for example, loading information directly from the dynamic management views, or in SQL Server 2000 exec sp_who2, in Excel sheets to understand what was really going on across the specific servers’ activity. Thankfully, this is now all built into SQL Server Management Studio because Activity Monitor provides sortable columns, which enable exceptionally swift pinpointing of problematic operations by database, through information on execution time/frequency, reads/writes, and CPU usage. SQL 2012's version of Activity Monitor has improved column resizing, but is pretty much the same as the 2008 version.
Activity Monitor Gives you a great overview of the SQL Instance, just like Rome from Villa Medicci, Bourhgese Gardens
Looking South over Rome, from the Villa Borghese 







Since the initial version of SQL Server 2008 was released, you have been able to view real-time critical SQL Server process details and even sort by the worst-performing queries—whether it be by the number of times the offending code is run per minute, which login is running it, which database it is in, which application it is running from, etc. In Activity Monitor, practically everything you need to fix SQL Server issues is available, including the option to right click on a line in Recent Expensive Queries to optimize the problematic code right away.

There are four panes with graphs for each, plus collapsible details, so you can view/filter processes, resource waits, disk activity, and Recent Expensive Queries. By hovering over any of the columns within the respective information panes, one can also see which dynamic management view was used to provide the systems management information; for further investigation and perhaps even set up alerts for when thresholds are met. Even if you cannot update your instances to SQL Server 2008 for a while, then you can use the updated client tools in SQL Server Management Studio to benefit from this updated feature—in my opinion, a critical step in remediation.



Happy spring to  all my  readers, this summer is going to be a scorcher in North America, I can feel it :)

Avoid Database Restore Issues (Errors 3219 or 3514) by a Simple Script Edit - the DB Restore Work-around


Starting with the 1000 foot view: During the process of applying changes to a production database - let's call it Change Management (pick your ITIL or COBIT method accordingly), we should always go through a thorough testing process on an exact copy and environment too, if possible, of the production database itself. Developers especially are familiar with this process, since they are often handed off a database backup and told to simply 'deal with it', however they often find themselves in this frustrating situation where Error 3154 or 3219 comes up, and this post is to explain the simple work around, instead of reading a big thread on MSDN. Straight to the point, there is a quick method to avoid Error 3154: The backup set holds a backup of a database other than the existing database message and 3219 which are related errors to restoring. In other words, the non-matching backup set restore failure that I have been stumped on occasionly, until I remember to edit the script as below.
Isabella Island, Galapagos, Ecuador
Isabella Island, Galapagos Islands, Ecuador (from a viewpoint above Targus Cove, just south the equatoral line )

Here's a typical Restore script run in SQL Server 2012/2008/5 - which will fail because the media set is not matching:

RESTORE DATABASE [SomeTestDBfromProduction] FILE= N'ProductionDB_Data', FILE = N'ProductionDB_Log' F
ROM
DISK = N'DriveName:\BACKUP\Production - Full Backup.BAK' WITH FILE = 1, MOVE N'ProductionDB_Data' TO N'DriveName:\DATA\ProdDBdataFile.mdf', MOVE N'ProductionDB_Log' TO N'DriveName:\LOG\ProdDBLogFile_log.ldf', NOUNLOAD, REPLACE, STATS =10 
-- replace is because you are restoring over

First create an empty database with the desired Test nomenclature for your respective environment (Or just create a new one, if this is not to be re-occuring).  Notice, to fix the script, that if we take out both the File=  headers for the restore (struck out below), we will be able to successfully restore the backup file even if the database is from an entirely different server.
RESTORE DATABASE [SomeTestDBfromProduction] FILE= N'ProductionDB_Data', FILE = N'ProductionDB_Log' F
ROM
DISK = N'DriveName:\BACKUP\Production - Full Backup.BAK' WITH FILE = 1, MOVE N'ProductionDB_Data' TO N'DriveName:\DATA\ProdDBdataFile.mdf', MOVE N'ProductionDB_Log' TO N'DriveName:\LOG\ProdDBLogFile_log.ldf', NOUNLOAD, REPLACE, STATS =10
Now, in another scenario, if we are doing this on databases being used in production, and require a quick restore, there will possibly be a log file (tail of the log file error) problem that can be mitigated by first doing a log backup prior to full restore, switching the database to single user mode, then restoring over top of the existing database (no replace needed below since it's on the same server, same DB) with the specific restore point, finally followed by a switch back to Multi User mode.
use masterGO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'MyDatabase' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'MyDatabase')
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''MyDatabase'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'DriveName:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Backup\MyDatabase_backup_2009_DATE_LSNumbers.trn' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
ALTER DATABASE [MyDatabase] SET SINGLE_USER WITH ROLLBACK IMMEDIATERESTORE DATABASE [MyDatabase] FROM DISK = N'DriveName:\Program Files\Microsoft SQL Server\MSSQL10.InstanceName\MSSQL\Backup\MyDatabase_backup_2009_DATE_LSNumbers.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
ALTER DATABASE [MyDatabase] SET MULTI_USER WITH ROLLBACK IMMEDIATE

If you are simply starting up the restore on a new server for the very first time or overwriting the same database again (that is not from a totally different media set, nor moves log and data files around), then you will only need a typical restore.
use mastergo

RESTORE DATABASE [RestoreDirectlyDBfromBackup] FROM 
DISK = N'DriveName:\SQLBackups\BackupFile.BAK'
-- N'\\NetworkServerExample\SQLBackups\DBNAME_db_2009date.BAK'
WITH  FILE = 1,  MOVE N'DatabaseName_data' TO N'DriveName:\SQLData\DBName.mdf',  MOVE N'DatabaseName_log' -- NB. if you have multiple data files you will need to do a MOVE X To FileLocation for each one of themTO N'DriveName:\SQLLogs\DBName.ldf',  NOUNLOAD,  STATS = 10

Happy Restoring :)