Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to build and deploy the checklist in the database server, which has a certain reference value. Interested friends can refer to it. I hope you will learn a lot after reading this article. Let's take a look at it.

Text

1. Rack and cable server

Make sure each power supply is plugged into a different power circuit

If possible, make sure that the network cable is plugged into a different network switch

The usage domain account for the 2.SQL Server service and the SQL Server proxy service.

You will need to know the usernames and passwords of these accounts during SQL Server 2012 installation

Let these accounts use passwords that never expire

3. Check the primary BIOS settings on the server

Enable hyper-threading and turbo-boost (an overclocking technology that improves performance by up to 10%)

Power management should be set to operating system control

Disable memory testing

4. Install Windows Server 2012 R2 Standard Edition on the server

Using an integrated RAID controller to use two internal drives in RAID 1

If possible, consider using SSD

If you use SSD, you do not need to defragment it

Create a separate partition for the C: drive

Change the size of the Windows page file to 16GB and prevent disk C.

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 CD drive to Z:

5. Change the NETBIOS name on the server to the desired server permanent name

6. Install .net 2012 using the native features of Windows Server 3.51 R2

7. Install Microsoft Update on the server

This is a superset of Windows Update.

8. Install all Microsoft and Windows updates on the server

It may take several rounds to get all the necessary updates

9. Defragment disk C

Use scheduled tasks to automatically defragment disk C every week

New drives are not allowed to be automatically added to the schedule

10. Create a static IP address with the correct DNS and default gateway information

11. Join the server to the appropriate Windows domain

twelve。 Activate Windows on the server

13. Install the latest version of Dell OMSA on the server (I haven't used it)

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 drivers for the server

15. Create a RAID array for LUN using Dell OMSA

Create a LUN and then go to logical disk Manager to create / format the drive

II . Create the array and LUN in the order shown below

General PERC Settings in Dell OMSA

Use smart mirroring for RAID 10 array

II . No read-ahead cache

III . Enable writeback caching

IV . Caching policy should be enabled

V . Use 64K allocation unit

16. Create a logical disk using Windows logical disk Manager

After creating the array using OMSA, open disk Manager

You will see the initialize disk dialog box

Be sure to use the GPT partition style

17. Check to make sure that the new logical drive can be seen in Windows Explorer

18. Before installing SQL Server 2012, create all required logical drives on

19. Use CrystalDiskMark to test the performance of each logical drive

20. Use SQLIO to test the performance of each logical drive

21. On each drive, create the following folder

Data driver: SQLData

Log drive: SQLLogs

TempDB drive: TempDB

Backup drive: SQLBackups

twenty-two。 Use the Group Policy Editor (GPEDIT.MSC) to grant these Windows permissions to the SQL Server service account

Perform volume maintenance tasks

Lock the memory page

23. Install SQL Server 2012 Enterprise Edition

Make sure there is no pending reboot, otherwise SQL Server 2012 will not be installed

Install only the SQL Server 2012 components required for this instance

C . Use mixed mode authentication

Set the sa password to a strong password

II . Add yourself as a SQL administrator

III . Add any other DBA that needs to be an administrator

Use domain accounts for SQL Server service accounts

Use the corresponding domain account as the SQL Server proxy account

F . Set the SQL Server proxy service to start automatically

G . 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. TempDB directory: t:\ TempDB

IV . TempDB log directory: t:\ TempDB

V . Backup directory: n:\ SQLBackups

24. Install SQL Server 2012 latest Service Pack

25. Install the latest cumulative update for SQL Server 2012 6

Cumulative updates are available from this location:

Http://support.microsoft.com/kb/2874879/en-us

Manually defragment the C: drive after installation

You do not need to do this if you are using SSD

twenty-six。 Change SQL Server 2012 instance-level properties

a. Enable optimize for ad hoc workloads

This will allow SQL Server to use less memory to store temporary query plans when executed for the first time

b. Set the maximum parallelism to the number of physical cores in the NUMA node on the server

c. Enable default backup compression

This will use SQL Server backup compression by default for all database backups

d. Add trace flag 3226 as a startup option in SQL Server configuration Manager

This will prevent successful database backup messages from being recorded in the SQL Server error log

E. Add trace flag 1118 as a startup option in SQL Server configuration Manager

This will help ease configuration contention in tempdb

f. Enable database mail on the instance

Used for mail notifications when SQL Server agent alerts and SQL Server agent jobs fail

G . Set Max Server Memory to the appropriate non-default value

The value depends on the amount of physical memory available in the server

It also depends on the SQL Server components installed

II . Here are some sample values:

Total RAM of 1.96GB: set maximum server memory to 87000

2. Total RAM of 64GB: set the maximum server memory to 56000

3. Total RAM of 32GB: set the maximum server memory to 27000

H . Create three additional TempDB data files in the T:\ TempDB directory. A total of 4 tempdb files (do not need to be aligned with the number of CPU in the first place)

The size of all TempDB data files should be 4096MB

Set automatic growth to 1024MB

II . TempDB log file should be 1024MB

twenty-seven。 Verify that you can ping the SQL Server computer from another computer on the domain

twenty-eight。 Using SQL Server 2012 Configuration Manager, verify that TCP / IP is enabled on the instance

twenty-nine。 Verify that you can remotely connect to the SQL Server instance using SSMS on another computer

thirty。 Create a SQL Server operator on the instance

Use DBAdmin with email address dbadmin@yourcompany.com

thirty-one。 Confirm that database mail is working properly

Right-click the database mail and send a test message

thirty-two。 Configure SQL Server Agent Mail to use Database Mail

thirty-three。 Create SQL Server agent alerts for the following errors:

A. YourServerName Alert-Sev 19 error: fatal error in resources

B. YourServerName Alert-Sev 20 error: a fatal error in the current process

C . YourServerName Alert-Sev 21 error: fatal error in database process

D . YourServerName Alert-Sev 22 error fatal error: table integrity suspect

E. YourServerName Alert-Sev 23 error: fatal error database integrity suspected

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 recovered the page

thirty-four。 A general script for creating these SQL Server agent alerts is provided here:

Ensure that each agent alert has a response to notify the DBAdmin operator

thirty-five。 Create a SQL Server agent job named Nightly Free System Cache and run this command:

DBCC FREESYSTEMCACHE ('SQL Plans')

Run at 12:00 in the morning every night

thirty-six。 Download the latest version of Ola Hallengren's SQL Server maintenance solution script:

Http://ola.hallengren.com/

Open the MaintenanceSolution.sql script when connecting to the instance

Change the @ BackupDirectory variable to N:\ SQLBackups

II . Run the script to create eleven new SQL Server agent jobs

III . For each job, if the job fails, go to the Notification property window and email the job to the DBAdmin group

IV . For each job, create a run-time schedule.

V . This is a proposed work schedule:

CommandLogCleanup 12:00 Sunday morning

2. DatabaseBackup-SYSTEM_DATABASES-full daily 11:55 PM

3. DatabaseBackup-USER_DATABASES-DIFF Daily at 12:00 PM

4. DatabaseBackup-USER_DATABASES-12: 00:00 all day

5. DatabaseBackup-USER_DATABASES-record once an hour

DatabaseIntegrityCheck-SYSTEM_DATABASES 7:55 on Saturday

7. DatabaseIntegrityCheck-USER_DATABASES 8:00 on Saturday

8. IndexOptimize-USER_DATABASES 8:00 on Sunday

9. File cleanup 12:00 on Sunday

10.sp_delete_backuphistory 12:00 Sunday morning

12:00 on Sunday morning, 11.sp_purge_jobhistory.

Summary

I use red to mark all the best practices that I think are important. However, the above enable hyperthreading and turbo-boost

I think according to the actual situation of the customer, if the customer's system can use these extra logic CPU, then hyperthreading should be turned on. According to experience, it is usually beneficial for OLTP systems to turn on hyperthreading. However, for some report queries, it may have an adverse effect to turn on hyperthreading.

For details, please refer to: https://blogs.msdn.microsoft.com/slavao/2005/11/12/be-aware-to-hyper-or-not-to-hyper/

Number of tempdb files

We know that adding tempdb data files can reduce PAGELATCH contention, which is based on previous best practice to align with the number of CPU kernels. But now that it has been optimized, there is no need to set up so many.

MBR and GPT

GPT means GUID partition table. (GUID means globally unique identifier). This is a new standard that is gradually replacing MBR. It and UEFI complement each other-UEFI is used to replace the old BIOS, while GPT replaces the old MBR. It is called GUID partition table because each partition on your drive has a globally unique identifier. On MBR disks, partition and boot information are saved together. If this part of the data is overwritten or destroyed, things will be in trouble. In contrast, GPT keeps multiple copies of this information on the entire disk, so it is more robust and can recover the corrupted information. GPT also keeps cyclic redundancy check codes (CRC) for this information to ensure its integrity and correctness-if the data is corrupted, GPT will detect the corruption and recover it from elsewhere on disk. There's nothing MBR can do about these problems-it's only after the problem that you find that the computer doesn't boot or the disk partition is missing.

Thank you for reading this article carefully. I hope the article "how to build and deploy a checklist in a database server" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.