wiki:ChecklistDB

Version 19 (modified by wqw, 8 years ago) (diff)

--

BIOS

  1. CPU sleep throttle -> off
    • Intel C-STATE Tech -> disable
    • C1E Support -> disable

Windows

  1. LAN card speed -> 1Gb full duplex + Jumbo Packets
  2. Power Options -> High performance

Инсталация

  1. Инсталация на instance-и (виж install.bat по-долу)
    • note: setup-а иска vcredist_x86.exe!!!
  2. Инсталация на SP2 на MSSQL 2008 R2
    • note: иска logoff след инсталацията от предната точка иначе забива
  3. Инсталация на JET OLEDB Provider: AccessDatabaseEngine_x64.exe
  4. Инсталация на 7-zip: 7z920-x64.msi

Настройки MSSQL

  1. SQL Configuration
    • Настройка на static port: 60xx
    • Настройка на trace flags
      • [*] -T845 Using locked pages for buffer pool
      • [*] -T4199 Trace flag 4199 is added to control multiple query optimizer changes previously made under multiple trace flags
      • [ ] -T4136 Disables parameter sniffing
      • [ ] -T1224 disables lock escalation based on number of locks
      • [*] -T3499 disable mirror db recovery after every transaction
      • [*] -T3226 no successful backup entry in the SQL Server error log and in the system event log
  2. Настройка на instance (max memory, etc.)
    EXEC sys.sp_configure N'show advanced options', N'1'
    RECONFIGURE WITH OVERRIDE
    EXEC sys.sp_configure N'min server memory (MB)', N'32767'
    EXEC sys.sp_configure N'max server memory (MB)', N'32768'
    EXEC sys.sp_configure N'awe enabled', N'1'
    EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
    EXEC sys.sp_configure N'remote admin connections', N'1'
    EXEC sys.sp_configure N'cost threshold for parallelism', N'25'
    EXEC sys.sp_configure N'max degree of parallelism', N'6'
    RECONFIGURE WITH OVERRIDE
    
  3. Настройка на брой файлове на tempdb
    ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, SIZE = 1024MB, FILEGROWTH = 10%);
    ALTER DATABASE tempdb MODIFY FILE (NAME = templog, SIZE = 1024MB, FILEGROWTH = 10%);
    ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\MSSQL10_50.INS09\MSSQL\DATA\tempdev2.ndf', SIZE = 1024MB, FILEGROWTH = 10%);
    ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = 'D:\MSSQL10_50.INS09\MSSQL\DATA\tempdev3.ndf', SIZE = 1024MB, FILEGROWTH = 10%);
    ALTER DATABASE tempdb ADD FILE (NAME = tempdev4, FILENAME = 'D:\MSSQL10_50.INS09\MSSQL\DATA\tempdev4.ndf', SIZE = 1024MB, FILEGROWTH = 10%);
    
  4. Настройка на linked server към mirroring partner
    EXEC master.dbo.sp_addlinkedserver @server = N'INS01', @srvproduct=N'sql', @provider=N'SQLNCLI10', @datasrc=N'DBS02\INS01'
    /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'INS01',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
    GO
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'collation compatible', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'data access', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'dist', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'pub', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'rpc', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'rpc out', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'sub', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'connect timeout', @optvalue=N'0'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'collation name', @optvalue=null
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'lazy schema validation', @optvalue=N'false'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'query timeout', @optvalue=N'0'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'use remote collation', @optvalue=N'true'
    EXEC master.dbo.sp_serveroption @server=N'INS01', @optname=N'remote proc transaction promotion', @optvalue=N'true'
    GO
    
  5. Настройка на msdb
    • създаване на usp_adm_InstanceMirror
    • създаване на usp_adm_InstanceTasks
    • създаване на usp_adm_InstanceTableUsage
    • създаване на usp_adm_Ftp
    • създаване на usp_adm_DbMaintenance
    • създаване на master.dbo.dba_CopyLogins
    • копиране на adm_InstanceDbs от mirror partner
      DECLARE		@LinkSrv		SYSNAME
      
      SELECT		TOP 1 @LinkSrv = name
      FROM		master.sys.servers
      WHERE		server_id <> 0
      
      EXEC		('
      USE			msdb
      
      IF			OBJECT_ID(''dbo.adm_InstanceDbs'') IS NOT NULL
      			DROP TABLE dbo.adm_InstanceDbs
      
      SELECT		*
      INTO		dbo.adm_InstanceDbs
      FROM		' + @LinkSrv + '.msdb.dbo.adm_InstanceDbs
      ORDER BY	DbName')
      
  6. Копиране на Operators и Jobs
    USE [msdb]
    GO
    EXEC msdb.dbo.sp_add_operator @name=N'Владимир Висулчев', 
    		@enabled=1, 
    		@weekday_pager_start_time=80000, 
    		@weekday_pager_end_time=180000, 
    		@saturday_pager_start_time=80000, 
    		@saturday_pager_end_time=180000, 
    		@sunday_pager_start_time=80000, 
    		@sunday_pager_end_time=180000, 
    		@pager_days=127, 
    		@email_address=N'wqw@unicontsoft.com', 
    		@category_name=N'[Uncategorized]'
    GO
    EXEC msdb.dbo.sp_add_operator @name=N'Иван Братов', 
    		@enabled=1, 
    		@weekday_pager_start_time=80000, 
    		@weekday_pager_end_time=180000, 
    		@saturday_pager_start_time=80000, 
    		@saturday_pager_end_time=180000, 
    		@sunday_pager_start_time=80000, 
    		@sunday_pager_end_time=180000, 
    		@pager_days=127, 
    		@email_address=N'Igbr@unicontsoft.com', 
    		@category_name=N'[Uncategorized]'
    GO
    
  7. Настройка на Database Mail
    DECLARE @password NVARCHAR(128) = '####'
    
    EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryAttempts'
    	, @parameter_value=N'1', @description=N'Number of retry attempts for a mail server'
    EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryDelay'
    	, @parameter_value=N'60', @description=N'Delay between each retry attempt to mail server'
    EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DatabaseMailExeMinimumLifeTime'
    	, @parameter_value=N'600', @description=N'Minimum process lifetime in seconds'
    EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'DefaultAttachmentEncoding'
    	, @parameter_value=N'MIME', @description=N'Default attachment encoding'
    EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'LoggingLevel'
    	, @parameter_value=N'2', @description=N'Database Mail logging level: normal - 1, extended - 2 (default), verbose - 3'
    EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'MaxFileSize'
    	, @parameter_value=N'1000000', @description=N'Default maximum file size'
    EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'ProhibitedExtensions'
    	, @parameter_value=N'exe,dll,vbs,js', @description=N'Extensions not allowed in outgoing mails'
    EXEC msdb.dbo.sysmail_add_account_sp @account_name=N'sql@saas.bg'
    	, @email_address=N'sql@saas.bg'
    	, @mailserver_name=N'mail.saas.bg'
    	, @username='sql@saas.bg'
    	, @password=@password
    EXEC msdb.dbo.sysmail_add_profile_sp @profile_name=N'Default Db Mail'
    EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'Default Db Mail'
    	, @account_name=N'sql@saas.bg', @sequence_number=1
    EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest'
    	, @profile_name=N'Default Db Mail', @is_default=1
    GO
    
    exec dbo.sp_configure 'show advanced options',1
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    exec dbo.sp_configure 'Database Mail XPs',1
    GO
    RECONFIGURE WITH OVERRIDE
    GO
    
    DECLARE @Body VARCHAR(2000) = 'from ' + @@SERVERNAME
    EXECUTE msdb.dbo.sp_send_dbmail
       @Recipients = 'wqw@unicontsoft.com',
       @Subject = 'test',
       @Body = @Body
    GO
    EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
    GO
    EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'Default Db Mail'
    GO
    
  8. Създаване на Alerts
    USE [msdb]
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 016',@message_id=0,@severity=16,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 017',@message_id=0,@severity=17,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 018',@message_id=0,@severity=18,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 018', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 019',@message_id=0,@severity=19,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 019', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 020',@message_id=0,@severity=20,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 020', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 021',@message_id=0,@severity=21,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 021', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 022',@message_id=0,@severity=22,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 022', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 023',@message_id=0,@severity=23,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 024',@message_id=0,@severity=24,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Severity 025',@message_id=0,@severity=25,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000';
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Error Number 823',@message_id=823,@severity=0,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000'
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 823', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Error Number 824',@message_id=824,@severity=0,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000'
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 824', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    EXEC msdb.dbo.sp_add_alert @name=N'Error Number 825',@message_id=825,@severity=0,@enabled=1,@delay_between_responses=60,@include_event_description_in=1,@job_id=N'00000000-0000-0000-0000-000000000000'
    EXEC msdb.dbo.sp_add_notification @alert_name=N'Error Number 825', @operator_name=N'Владимир Висулчев', @notification_method = 1;
    GO
    EXEC master.dbo.sp_MSsetalertinfo @failsafeoperator=N'Владимир Висулчев', @notificationmethod=1
    GO
    
  9. Настройки на mirroring
    • exec msdb.dbo.usp_adm_InstanceMirror 'SrvCreateEndpoint', '5022'
    • exec msdb.dbo.usp_adm_InstanceMirror 'SrvCopyLogins'
    • exec msdb.dbo.usp_adm_InstanceMirror 'SrvCreateTempdbUsers'
    • exec msdb.dbo.usp_adm_InstanceMirror 'Setup'

install.bat

@echo off
setlocal
set accountname=UCSSAAS\sqlservice
set accountpass=#########
set sapwd=########
set sql_setup=e:\setup.exe

net localgroup Administrators %accountname% /add
%~dp0ntrights.exe +r SeManageVolumePrivilege -u %accountname%
%~dp0ntrights.exe +r SeLockMemoryPrivilege  -u %accountname%

%sql_setup% /qs /INSTANCENAME=INS09 /FEATURES=SQLEngine,ADV_SSMS /ACTION=install /INSTANCEDIR=D:\ /SECURITYMODE=SQL /SAPWD="%sapwd%" /SQLCOLLATION=Cyrillic_General_CI_AS /SQLSVCACCOUNT="%accountname%" /SQLSVCPASSWORD="%accountpass%" /AGTSVCACCOUNT="%accountname%" /AGTSVCPASSWORD="%accountpass%" /AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /IACCEPTSQLSERVERLICENSETERMS /TCPENABLED=1 
rem %sql_setup% /INSTANCENAME=INS09 /FEATURES=SQLEngine /ACTION=install /INSTANCEDIR=D:\ /SECURITYMODE=SQL /SAPWD="%sapwd%" /SQLCOLLATION=Cyrillic_General_CI_AS /SQLSVCACCOUNT="%accountname%" /SQLSVCPASSWORD="%accountpass%" /AGTSVCACCOUNT="%accountname%" /AGTSVCPASSWORD="%accountpass%" /AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /IACCEPTSQLSERVERLICENSETERMS /TCPENABLED=1 
%sql_setup% /qs /INSTANCENAME=INS10 /FEATURES=SQLEngine /ACTION=install /INSTANCEDIR=D:\ /SECURITYMODE=SQL /SAPWD="%sapwd%" /SQLCOLLATION=Cyrillic_General_CI_AS /SQLSVCACCOUNT="%accountname%" /SQLSVCPASSWORD="%accountpass%" /AGTSVCACCOUNT="%accountname%" /AGTSVCPASSWORD="%accountpass%" /AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /IACCEPTSQLSERVERLICENSETERMS /TCPENABLED=1 
%sql_setup% /qs /INSTANCENAME=INS11 /FEATURES=SQLEngine /ACTION=install /INSTANCEDIR=D:\ /SECURITYMODE=SQL /SAPWD="%sapwd%" /SQLCOLLATION=Cyrillic_General_CI_AS /SQLSVCACCOUNT="%accountname%" /SQLSVCPASSWORD="%accountpass%" /AGTSVCACCOUNT="%accountname%" /AGTSVCPASSWORD="%accountpass%" /AGTSVCSTARTUPTYPE=Automatic /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /IACCEPTSQLSERVERLICENSETERMS /TCPENABLED=1 
netsh advfirewall firewall add rule name="SQL Server (INS09)" dir=in action=allow program="D:\MSSQL10_50.INS09\MSSQL\Binn\sqlservr.exe" enable=yes
netsh advfirewall firewall add rule name="SQL Server (INS10)" dir=in action=allow program="D:\MSSQL10_50.INS10\MSSQL\Binn\sqlservr.exe" enable=yes
netsh advfirewall firewall add rule name="SQL Server (INS11)" dir=in action=allow program="D:\MSSQL10_50.INS11\MSSQL\Binn\sqlservr.exe" enable=yes
netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow program="C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe" enable=yes

rem e:\setup.exe /qs /INSTANCENAME=INS09 /FEATURES=SQLEngine /ACTION=uninstall
rem e:\setup.exe /qs /INSTANCENAME=INS10 /FEATURES=SQLEngine /ACTION=uninstall
rem e:\setup.exe /qs /INSTANCENAME=INS11 /FEATURES=SQLEngine /ACTION=uninstall

compress.bat

@echo off
setlocal
set sevenzip="C:\Program Files\7-Zip\7z.exe"

if "%1"=="/?" echo usage: %~nx0 [yyyy_mm_dd] && goto :eof
if not "%1"=="" call :compress %1 && goto :eof

for %%i in (Dreem15_*.bak) do call :extract_date %%i
for %%i in (imPress37_*.bak) do call :extract_date %%i
if "%bak_file%"=="" echo Nothing to do
goto :eof

:extract_date
set bak_file=%1
call :compress %bak_file:~-14,10%
goto :eof

:compress
if exist Dreem15_*_%1.BAK %sevenzip% a -mx3 -mmt=on -m0=LZMA2 Dreem15_%1 Dreem15_*_%1.BAK && del Dreem15_*_%1.BAK
if exist imPress37_*_%1.BAK %sevenzip% a -mx3 -mmt=on -m0=LZMA2 imPress37_%1 imPress37_*_%1.BAK && del imPress37_*_%1.BAK
goto :eof