About Me

My photo
Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present.

Wednesday, April 20, 2011

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)

Friday, April 8, 2011

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)


Thursday, April 7, 2011

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