1. Rack and cable server ensure that each power supply plugs into a different power circuit if possible, make sure that the network cable is plugged into a different network switch 2.SQL Server service and the SQL Server Agent service uses a domain account. During SQL Server 2012 installation, you will need to know the user names and passwords for these accounts to use
never expireThe password 3. Check the primary BIOS settings on the server
enable Hyper-threading and Turbo-boost(is an overclocking technology that improves performance by up to 10%) power management should be set to OS control Disable memory test 4. Install Windows Server R2 standard edition on the server Use an integrated RAID controller for two internal drives in RAID 1 if possible, consider using the ssd If you use SSDs, you don't need to defragment them Create a separate partition for the C: drive Change the Windows paging file size to 16GB and prevent C drives Change the Windows power Plan to high performance run Cpu-z on the server to confirm that the processor is running at full speed change the drive letter of the optical drive to z: 5. Changes the NetBIOS name on the server to the desired server permanent name 6. Install. NET 3.51 7 by using the Windows Server R2 feature. Install Microsoft update on the server this is a superset of Windows Update 8. Install all Microsoft and Windows updates on the server this may take several rounds to get all the required update 9. Defragment the C drive    &NBsp; uses scheduled tasks to automatically defragment the C disk weekly does not allow the new drive to be automatically added to the plan 10. Create a static IP address that has the correct DNS and default gateway information 11. Join the server to the appropriate Windows domain 12. windows 13 is activated on the server. Install the latest version of Dell OMSA on the server (this thing I didn't use) 14. Download the latest version of Dell Server Update Utility (SUU) load. iso into Suu and run suu this will ensure that you have the latest firmware and driver 15 for the server. Using Dell OMSA Create a RAID array for a LUN Create a LUN and go to Logical Disk Manager Create/format drive ii. Create arrays and lun General perc settings in Dell Omsa, in the order shown below Use smart mirror ii for RAID 10 arrays. No read-ahead cache iii. Enable write-back cache iv. The cache policy v should be enabled. Using 64K allocation Unit &nbsP;16. Creating a logical disk using Windows Logical Disk Manager after creating an array using OMSA, open Disk Manager you will see the Initialize Disk dialog box Make sure to use the GPT partitioning style 17. Check to ensure that the new logical drive is visible in Windows Explorer 18. Before installing SQL Server 2012, create a 19 on all required logical drives. Use CrystalDiskMark to test the performance 20 of each logical drive. Use Sqlio to test the performance 21 of each logical drive.
on each drive, create the following folderData drive: SqlData log drive: sqllogs tempdb drive: tempdb backup drive: sqlbackups 22. Use the Group Policy Editor (Gpedit.msc) to grant these Windows permissions to SQL Se RVer Service Account
Perform volume maintenance tasks
Lock Memory Page 23. Install SQL Server 2012 Enterprise Edition Ensure that there are no pending reboots, or SQL Server 2012 will not install install only the SQL Server 2012 component c required for this instance. Use mixed-mode authentication set the sa password to strong password ii. Add yourself as a SQL administrator iii. Add any other dba that need to be an administrator use a domain account for the SQL Server service account Use the corresponding domain account as the SQL Server Agent account f. Set the SQL Server Agent service to start g automatically. Set the default directory to the appropriate drive letter and path I. User database directory: p:\ sqldata ii. User database log directory: L:\ SQLLOGS          III. Temp db directory: T:\ tempdb iv. Temp DB log directory: T:\ tempdb v. Backup directory: n:\ sqlbackups 24. Install the latest Service pack 25 for SQL Server 2012. Install SQL Server 2012 latest Cumulative Update 6 cumulative updates can be obtained from this location: http://support.microsoft.com/kb/2874879/en-us manually defragmenting the C: drive after installation If you are using an SSD, you do not need to do this 26. Change the SQL Server 2012 instance-level properties
A. Enabling optimize for ad hoc workloadsThis will allow SQL Server to use less memory to store the Ad hoc query Plan B at the first execution time.
setting the maximum degree of parallelismSet to the number of physical cores in the NUMA node on the server C.
Enable default backup compressionThis will default to all database backups using SQL Server backup compression d. In SQL Server Configuration Manager, add
trace flag 3226As a startup option, this prevents a successful database backup message from being logged in the SQL Server error log E. In SQL Server Configuration Manager, add
trace flag 1118As a startup option this will help alleviate configuration contention in tempdb F. Enable Database Mail on the instance for SQL Server Agent alerts and SQL Server Agent job failure when mail notification G.
set Max Server memory to the appropriate non-default valueThe value depends on the amount of physical memory available in the server and it also depends on the SQL Server components that are installed. Here are some sample values: 1.96GB Total RAM: Set the maximum server memory to 87000 2. 64GB Total RAM: Sets the maximum server memory to 56000 3. 32GB Total RAM: Sets the maximum server memory to 27000 H. Additional three tempdb data files are created in the t:\ tempdb directory.
a total of 4 tempdb files (no need to start with the number of CPUs aligned)
the size of all tempdb data files should be 4096MB
Set auto-grow to 1024MBIi.
the tempdb log file should be 1024MB 27. Verify that you can ping the SQL Server computer 28 from another computer on the domain. Use SQL Server Configuration Manager to confirm that the instance has TCP/IP enabled 29. Verify that you can connect to the SQL Server instance 30 remotely using SSMs on another computer. Create a SQL Server operator on the instance use Dbadmin with e-mail address [email protected] 31. Confirm Database Mail is working properly Right-click Database Mail and send test message 32. Configure SQL Server Agent messages to use Database Mail 33. Create a SQL Server Agent alert for the following error: a. YourServerName Alert-sev 19 Error: fatal error in resource b. YourServerName Alert-sev 20 Error: Fatal error c in the current process. YourServerName Alert-sev 21 Error: fatal error d in the database process. YourServerName Alert-sev 22 fatal error: Table integrity suspicious e. yourservername Alert-sev 23 Error: Fatal error database integrity suspect f. YourServerName Alert-sev 24 Error: Fatal hardware error       G. YourServerName Alert-sev 25 Error: fatal error h. YourServerName alert-error 825:read-retry required i. YourServerName Alert-Error 832: Constant page changed j.yourservername alert-error 855: uncorrectable Hardware Memory corruption detected k. YourServerName Alert-Error 856:SQL server has detected hardware memory corruption, but the page 34 has been recovered. This provides a generic script to create these SQL Server Agent alerts: ensure that each agent alert responds to notify the dbadmin operator 35. Create a SQL Server Agent job named nightly free System cache. Run this command: DBCC freesystemcache (' SQL plans '); Run 36 at 12:00 every night. Download the latest version of the OLA Hallengren SQL Server Maintenance solution Script: http:/ /ola.hallengren.com/ Open maintenancesolution.sql Script when connecting to an instance Modify the @backupdirectory variable to n:\ sqlbackups ii. Run a script to create 11 new SQL Server Agent jobs &nbsP;         III. For each job, if the job fails, go to the Notifications Properties window and send the job by e-mail to the Dbadmin group Iv.. For each job, create a schedule for the run time. v. This is a suggested work schedule: Commandlogcleanup Sunday a.m. 12:00 2. Databasebackup-system_databases-Full daily 11:55 pm 3. Databasebackup-user_databases-diff Daily at pm & Nbsp; 4. Databasebackup-user_databases-Full Day 12:00 5. Databasebackup-user_databases-Record once per hour dAtabaseintegritycheck-system_databases Saturday a.m. 7:55 7. Databaseintegritycheck-user_databases Saturday a.m. 8:00 8. Indexoptimize-user_databases Sunday pm 8:00 9. File cleanup Sunday Morning 12:00 10.sp_delete _backuphistory Sunday a.m. 12:00 11.sp_purge_jobhistory Sunday 12:00. Reprint: http://www.cnblogs.com/OwenZeng/p/7605157.html
Database server build and deployment notes
