SQL Server Tacklebox- P5 ppt
Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (125.31 KB, 5 trang )
1 – Eating SQL Server installations for breakfast
20
you do not enable it for 32-bit installations of SQL Server, on Windows 2003
Enterprise, you will not use the memory that you may think you should be using;
SQL Server will live within the 2G memory range to which 32-bit applications are
generally relegated.
However, there are also options that you will not want to change. Two of these
options are "priority boost" and "lightweight pooling". Changes to these options
are typically done only with affirmation from Microsoft Support that it will help
and not hinder your environment. In general, please do not change a configuration
unless you have thoroughly tested it.
The automated SQL Server configuration script
So, what configuration changes can we automate? There are many, and the base
script provided in the next section can be extended to support your particular
environment. The options fall into three categories:
• Server-level changes you can make with
sp_configure
• Database-level changes you can make with
sp_dboption
• Custom configurations that you will apply, specifically custom
administrative code, job schedules and DDL triggers.
What follows is the pseudo code for what the configuration script will automate
for you. All told, I would estimate that the configuration script will save about 30
minutes of manual configuration effort. More important than the time saving,
however, is the fact that this script offers a repeatable and accurate configuration
for each server.
• SQL Server Memory
• If 64-bit, do not enable AWE
• If 32-bit SQL 2008 Standard on Windows 2003 Enterprise and
RAM is more than 4G
• Set max server memory = 2G less than Total Server Memory
• If 32-bit SQL 2008 Standard on Windows 2003 Standard and
RAM is less than 4G
• Set max server memory = 2G
• If > 2005 automate setup with DBMail SPs
• Send Test Mail
• If < 2005 Document necessity to create MAPI profile
• Print steps to configure e-mail
1 – Eating SQL Server installations for breakfast
21
• DDL Triggers
• Add Server Trigger to notify upon database create or drop
• Security
• Set to Log Successful and Failed logins
• DB Maintenance Database
• Create the _DBAMain database
• Create the stored procedures in the _DBAMain database
• Create and Schedule Maintenance Jobs via stored procedures
• Other Modifications
• Change Model Database Options.
Listing 1.2 displays the actual T-SQL automation script to implement the above
steps, which you can execute against your newly installed SQL Server instance. It
is documented at stages to distinguish between server, database and custom
additions.
/* SQL Server Automated Configuration Script
2009 - Rodney Landrum
*/
Create Temp table #SerProp. This table will be used
to hold the output of xp_msver to control server property
configurations
SET NOCOUNT ON
GO
IF EXISTS ( SELECT name
FROM tempdb sysobjects
Where name like '#SerProp%' )
If So Drop it
DROP TABLE #SerProp
create table #SerProp
(
ID int,
Name sysname,
Internal_Value int,
Value nvarchar(512)
)
GO
Set Show Advanced Option
sp_configure 'Show Advanced Options', 1
Reconfigure
GO
1 – Eating SQL Server installations for breakfast
22
DECLARE @PhysMem int
DECLARE @ProcType int
DECLARE @MaxMem int
INSERT INTO #SerProp
Exec xp_msver
Select @PhysMem = Internal_Value
from #SerProp
where Name = 'PhysicalMemory'
Select @ProcType = Internal_Value
from #SerProp
where Name = 'ProcessorType'
Set Memory Configuration from server properties
(memory level and processortype)
If @PhysMem > 4096 AND @ProcType = 8664
BEGIN
SET @MaxMem = @PhysMem - 3072
EXEC sp_configure 'max server memory', @MaxMem
Reconfigure
END
ELSE
IF @PhysMem > 4096 AND @ProcType <> 8664
BEGIN
SET @MaxMem = @PhysMem - 3072
EXEC sp_configure 'awe enabled', 1
Reconfigure
EXEC sp_configure 'max server memory', @MaxMem
Reconfigure
END
Setup Database Mail (SQL Server > 2005 )
Turn on Mail XPs via sp_configure
sp_configure (To turn on Mail XPs)
Add Profile
If @@microsoftversion / power(2, 24) > 8
BEGIN
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Admin Profile',
@description = 'Mail Profile For Alerts' ;
Add Mail Account
EXECUTE msdb.dbo.sysmail_add_account_sp
1 – Eating SQL Server installations for breakfast
23
@account_name = 'Admin Account',
@description = 'General SQL Admin Account for DBA
Notification',
@email_address = '<Your DBA e-mail account>,
@display_name = 'SQL Admin Account',
@mailserver_name = '<Yourmailservername>;
Add Mail Account to Profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Admin Profile',
@account_name = 'Admin Account',
@sequence_number = 1 ;
Send Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Admin Profile',
@recipients = '<Your DBA e-mail Account>,
@body = 'Sever Mail Configuration Completed,
@subject = 'Successful Mail Test;
END
ELSE
Print Instructions for SQl Server 2000
BEGIN
PRINT 'For SQL Server 2000, you will need to
configure a MAPI client'
PRINT 'such as Outlook and create a profile to use
for SQL Mail and SQL Agent'
PRINT 'mail. Instructions can be found
at:______________________________'
END
Setup Security Logging
Enable Successful and Unsuccessful Login Attempts
SQL Server Services must be restarted to take affect
exec master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\MSSQLServer',
N'AuditLevel', REG_DWORD,3
Create Maintenance Database "_DBAMain"
USE [master]
GO
/****** Object: Database [_DBAMain]
Script Date: 02/05/2009 20:41:24 ******/
1 – Eating SQL Server installations for breakfast
24
IF EXISTS (SELECT name FROM sys.databases
WHERE name = N'_DBAMain')
DROP DATABASE [_DBAMain]
GO
/****** Object: Database [_DBAMain]
Script Date: 02/05/2009 20:41:24 ******/
CREATE DATABASE [_DBAMain] ON PRIMARY
( NAME = N'_DBAMain_Data',
FILENAME = N'C:\Data\_DBAMain_Data.MDF',
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%)
LOG ON
( NAME = N'_DBAMain_Log',
FILENAME = N'C:\Logs\_DBAMain_Log.LDF' ,
SIZE = 3072KB ,
MAXSIZE = 2048GB ,
FILEGROWTH = 10%)
GO
/*
Run Script To Create Stored Procedures
In _DBAMain
*/
sp_configure 'xp_cmdshell', 1
Reconfigure
exec xp_cmdshell 'sqlcmd -i C:\Writing\Create_DBAMain_2.sql'
Schedule Indexing Stored Procedure
/*
Usage:
spxCreateIDXMaintenanceJob
'Owner Name'
, 'Operator'
, 'Sunday'
, 0
*/
Create Procedure
[dbo].[spxCreateIDXMaintenanceJob]
(
@JobOwner nvarchar(75)
, @ValidOperator nvarchar(50)
, @DayToReindex nvarchar(8)
, @NightlyStartTime int 230000 (11pm), 0 (12am), 120000
(12pm)
)
As
BEGIN TRANSACTION
