Latest Presentation on SQL Server Mirroring Given at Transcontinental's Offices

With a handful of System Administrators, Network Specialists and DBAs, I gave my third presentation on SQL Server Mirroring this afternoon.
Please find the Presentation Slide Deck here on SkyDrive.



Contenu - Contents

Introduction to-à Microsoft SQL Server Mirroring – Haut disponibilité avec des bases de données en mirroir. An updated version of this presentation will be given (mais en bilingue) based on several Blog posts, including: http://dbhive.blogspot.ca/2010/10/vermont-sql-server-user-group.html


- Connection Strings: http://dbhive.blogspot.ca/2010/10/connection-strings-database-mirroring.html
- Deep Dive (for those who are not satisfied with an intro, and need to use certificates, and complete code version of a mirror setup - mise en place d'un mirror par script, et sans domaine) : http://dbhive.blogspot.ca/2010/12/notes-from-mirroring-deep-dive-session.html

Note that if you want to group Mirrored databases, to mimmick Availability Groups in SQL 2012, you simply have to create a SQL Agent Job with a step that has the ALTER Database DBName Set Partner Failover; for each database you want in the group.

Two Ways to Document Your SQL Server Infrastructure Quickly

To collect information for your SQL Server Infrastructure, there are two ways I can recommend.
 
The first, as mentioned on Technet, is to execute the following parameter details on any SQL Server installation (I tested back to 2000), by run the following command.

exec xp_msver "ProductName", "ProductVersion", "Language", "Platform", "WindowsVersion", "PhysicalMemory", "ProcessorCount"

-- result set is a table, with a row for each parameter

The second, and my preference as best pratice for gathering essential server information in a single row with more details, is the following, including the Collation, Clustering, Service Pack Level (product level):

select serverproperty('MachineName') MachineName

,serverproperty('ServerName') ServerInstanceName

,replace(cast(serverproperty('Edition')as varchar),'Edition','') EditionInstalled

,serverproperty('productVersion') ProductBuildLevel

,serverproperty('productLevel') SPLevel

,serverproperty('Collation') Collation_Type

,serverproperty('IsClustered') [IsClustered?]

,convert(varchar,getdate(),102) QueryDate,

case

when  exists (select * from msdb.dbo.backupset where name like 'data protector%') then 'HPDPused'

else 'Local Copy_Only & Commvault' -- where you would replace the

-- strings with your respective third party or native backup solution

end

To run either of these queries across multiple servers in SSMS 2008 (assuming that you have more than one), under Registered Servers, right click on Local Server Groups, and select New Query.

References:  See all the recent Technet SQL Server Tips

Optimise Your Disk Subsystem I/O with An Index File Group and Index Compression

As a DBA who is always seeking solutions to performance bottlenecks, amidst the daily rituals of validating backups and other regular tasks, profiting from File Groups on differing disk subsystems, in this case for Indexes, arose as flavor of the week. With this option, note that I am assuming one has setup the SQL Server instance with several disks available. If you are not on Microsoft SQL Server Enterprise Edition the usual Index option, ONLINE=ON, will unfortunately not be available, however if you perform this task during a maintenance window with Standard edition, this problem is moot.  The distinction must be made that this is not a regular maintenance task, and this is not to rebuild an index, but to recreate an index. We are replacing the index location on the disk subsystem with the useful option DROP_EXISTING=ON while referring to a different Filegroup location (ON [Indexes]) at the end of the script.

Here is the example:

/****** Object:  First Add an Index File Group Script Date: 06/06/2012 4:55:16 PM ******/
Create FILEGROUP [Indexes]

( NAME = N'DBname_Indexes', FILENAME = N'DiskName:\DataFileFolder\DatabaseName_Indexes.ndf' ,

SIZE = 10GB , MAXSIZE = 50GB , FILEGROWTH = 1GB )

/****** Object:  Index [SampleTableName _IDX_000]    Script Date: 06/06/2012 4:55:16 PM ******/

CREATE NONCLUSTERED INDEX [SampleTableName_IDX_000] ON [dbo].[ SampleTableName]

(

            [ClientId] ASC,

            [InvNo] ASC,

            [TxNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,

DROP_EXISTING = ON, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Indexes]

GO

Reading up on Index options, I noticed, way down at the very bottom of the Alter Index page, another option to save disk space and improve performance (SQL 2008+), was that you can compress Indexes also:

ALTER INDEX IX_INDEX1

ON T1

REBUILD

WITH ( DATA_COMPRESSION = PAGE )

GO

Happy recreation of Indexes and I/O balancing across your disks.

Bartholeme Island Boardwalk, Galapagos Islands, Ecuador