First up, the database.

OK, so I've been a bit busy with getting settled in with my new client. It's a manufacturing facility, and is pretty HA/HS. (High availability, high security.) There's a lot there to do. Still getting used to the way they operate.

The good part of the deal is that I get to work my favorite hours, which happens to be 6:00 AM to 2:30 PM. Beautiful. Nobody on the roads at travel time, and it's a long enough commute that I can decompress on the way home at the end of the day. There's also a good cafeteria on site, and the people are the friendliest I've worked with thus far, since I joined the consulting firm. (Next to those at the firm, of course. :-) )

I also attended a lecture on Agile Database Development this past Wednesday, and am still kind of processing things. I'm looking forward to being able to implement some of the methodologies as I can get to them in the new place. With the rapid pace of change that I see there, Agile might be a good way to go.

Now, I said I was going to start posting some scripts, and I shall. I suppose that posting code in lieu of actual information is a bit of a cheat as far as blogging goes, but I did promise this. Before I can give you the scripts, you have to have a place to hold them. Enter: the DBA database. I used to call it the ADMIN database, but ADMIN is a keyword in SQL 2005, so I decided to change that to the DBA database. So... here it is.

/*******************************************************************************
Title:  Create DBA DB Objects
Author: D Maxwell
Date:   Jan 2008
--Version: 20080129.1

Descr:  Creates necessary objects for my group of maintenance scripts.

Usage:  None.  Just run the script.  

Notes:  - The INI_File table parameters will need to be adjusted for the
          particular environment that this is installed in.  Check those
          before running the script.
        - The location of the database may also need to be adjusted to conform
          with the local standards for database creation.  It will otherwise
          be created at the default location.  The default database size should
          be fine, to start with.  On large servers with lots of traffic, go big.
*******************************************************************************/
SET NOCOUNT ON

/* Create Database */
CREATE DATABASE [DBA]
GO

/* Create Tables */
USE [DBA]
GO

-- The M_BackupLog table holds the information for all backups taken on the
-- system.  It is required by the backup and restore procedures.
CREATE TABLE [M_BackupLog] (
  DBName nvarchar(100) NOT NULL,
  [DBID] int NOT NULL,
  BackupFile nvarchar(1000) NOT NULL,
  CreateDate datetime NOT NULL,
  ExpirationDate datetime NOT NULL,
  Deleted bit NOT NULL
  )
GO

-- The M_ErrorLogs table contains the parsed and processed error logs for
-- the SQL system.  It is required by the error log processing procedure.
CREATE TABLE [M_ErrorLogs] (
  ID int NOT NULL,
  LogDate datetime NOT NULL,
  ProcessInfo nvarchar(50) NOT NULL,
  ErrorText varchar(7000) NULL,
  CreateDate datetime NOT NULL,
  ServerName nvarchar(30) NOT NULL,
  ErrorInd bit NOT NULL
  )
GO

ALTER TABLE [M_ErrorLogs]
ADD CONSTRAINT [PK_ErrorLogs] PRIMARY KEY CLUSTERED (ID)
GO

-- The INI_File table contains constant values required by many of
-- the scripts in the maintenance system.  If you need to add constants
-- used in any other scripts, put them here.
CREATE TABLE [INI_File] (
  INI_Name nvarchar(50) NOT NULL,
  INI_Value nvarchar(1000) NULL
  )
GO

-- Current INI file values.
INSERT INTO [INI_File]
SELECT 'BackupLocation', 'D:\DBBackups\' UNION ALL
SELECT 'DBAEmail', 'yourname@yourmailhost.tld' UNION ALL
SELECT 'LastErrRptDate', getdate() UNION ALL
SELECT 'MaxErrRpt', '10'
GO

-- The M_Scripts table is used for holding dynamically created scripts
-- that can then be exported to file and executed on one or many systems.
CREATE TABLE [M_Scripts] (
  Lines nvarchar(4000) NULL
  )
GO

-- The M_ServerInfo table holds identifying information about the local
-- server.  It is gathered on an irregular basis by the auditing portion
-- of the server maintenance system.  This is beyond the scope of
-- this document, but required to be in this database.
CREATE TABLE [M_ServerInfo] (
  ServerName nvarchar(30) NOT NULL,
  Version nvarchar(15) NOT NULL,
  Edition nvarchar(30) NOT NULL,
  IPAddress nvarchar(15) NOT NULL,
  LastUpdated datetime NOT NULL
  )
GO

More later. Comments welcome.