Disaster Recovery and High Availability - Overview

Here is a presentation that was given to the Boulder SQL Server Users' Group on April 19, 2011.

Disaster Recovery and High Availability - Overview 
(Opens in new window)

CreateVwObjectFileGroup

This proc creates a server-wide view that shows the file group of a given object.  Note the ObjectID pertains to that particular database.  This can be useful for managing space and storage requirements.

Later this month I’ll post the code for a view (vwTable) which will use vwObjectFileGroup and other views previously posted to show a server-wide glimpse of tables with number of rows, bytes, file group, and number of partitions.

USE Admin

GO

IF OBJECT_ID('dbo.CreateVwObjectFileGroup') Is Not Null

      DROP PROC dbo.CreateVwObjectFileGroup

GO

CREATE PROC dbo.CreateVwObjectFileGroup

      @ShowSql bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      8/12/10           PPaiva            Intial creation.

      9/1/10            PPaiva            Added column FileGroupID.

      DESCRIPTION

            Creates a view in the Admin database called vwObjectFileGroup.

            Coded to use Convert(varchar, ServerProperty('ServerName'))

                  rather than @@ServerName since the latter can be erroneous.

      USAGE

            Exec Admin..CreateVwObjectFileGroup

            Exec Admin..CreateVwObjectFileGroup 1

      DEBUG

            SELECT *

            FROM vwObjectFileGroup

*/

SET NOCOUNT ON

DECLARE @s varchar(max),

            @DB varchar(100),

            @MaxDB varchar(100),

            @i int,

            @sNow varchar(16)

SET @sNow = Convert(varchar(16), GetDate(), 120)

SET @s = 'IF Object_ID(''dbo.vwObjectFileGroup'') Is Not Null

      DROP VIEW dbo.vwObjectFileGroup'

IF @ShowSql = 1

      Print @s

Exec(@s)

     

Print ''

SET @s = 'CREATE VIEW dbo.vwObjectFileGroup

AS

/*    DATE        AUTHOR            REMARKS

      ' + Convert(varchar(10), GetDate(), 01) + '     PPaiva            Intial creation.

      DESCRIPTION

            Provides a list of file group per object_id

                  on this server.

      SELECT *

      FROM Admin..vwObjectFileGroup

*/

'

SELECT  name

INTO #FileGroupDBs

FROM sys.databases

WHERE state_desc = 'ONLINE'

SELECT  @MaxDB = Max(Name),

            @DB = '',

            @i = 0

FROM #FileGroupDBs

WHILE @DB < @MaxDB

      BEGIN

            SET @i = @i + 1

            SELECT @DB = Min(Name)

            FROM #FileGroupDBs

            WHERE Name > @DB

            SET @s = @s + '  

      SELECT  Convert(varchar, ServerProperty(''ServerName'')) as Server,

            ''' + @DB + ''' as DB,

            i.id ObjectID,

            fg.groupname COLLATE SQL_Latin1_General_CP1_CI_AS FileGroup,

            fg.GroupID FileGroupID,

      ''' + @sNow + ''' ViewCreateDate

      FROM [' + @DB + '].sys.sysfilegroups fg

      JOIN [' + @DB + '].sys.sysindexes i

            ON  i.groupid = fg.groupid

            AND i.indid < 2

'

            IF @DB <> @MaxDB

                  SET @s = @s + 'UNION ALL '

      END

IF @ShowSql = 1

      BEGIN

            SELECT *

            FROM #FileGroupDBs

            ORDER BY Name

            Print @s

      END

           

Exec (@s)

ConfigNewServer

Here's a proc I like to keep handy in the Admin database for when I set up a new server.  I may not set all options, but it serves as a checklist to which ones I might want to set.

USE Admin

GO

IF object_id('dbo.ConfigureNewServer') Is Null

      DROP PROC dbo.ConfigNewServer

go   

CREATE PROC [dbo].[ConfigNewServer]

AS

/*    DATE        AUTHOR            REMARKS

      4/7/11            PPaiva            Initial creation.

      DESCRIPTION

            Code snippets for some server-wide settings that may need to be set

                  for a new server.

*/

RETURN      -- contains code snippets, not meant to be executed

Exec sp_configure

Exec sp_configure 'show advanced options', 1

RECONFIGURE

Exec sp_configure xp_cmdshell, 1

RECONFIGURE

EXEC sp_configure 'backup compression default', 1

RECONFIGURE

EXEC sp_configure 'max server memory (MB)', 2000

RECONFIGURE

Exec sp_configure  'clr enabled', 1

RECONFIGURE

-- This should always be off

Exec sp_configure 'allow updates', 0

-- Set Windows and SQL authentication (requires service restart)

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 2

-- Set Windows only authentication (requires service restart)

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'LoginMode', REG_DWORD, 1