Detailed description of Database Server construction and deployment check list, detailed description of Database

Detailed description of Database Server construction and deployment check list, detailed description of Database

Preface

We may often install and deploy database servers, but we may suddenly forget a certain setting, which poses a risk for future O & M. The following is a list of overseas inspection items.

In fact, it also contains many best practices for database configuration. For example, the number of TEMPDB files, such as database files, how to store log files, and maximum memory settings. If you have any additional comments

Body

1. racks and cable servers

Ensure different power supply circuits are inserted for each power supply.

If possible, make sure that different network switches are inserted into the network cable.

2. domain accounts used by SQL Server and SQL Server proxy services.

During SQL Server 2012 installation, you will need to know the user names and passwords of these accounts

Allow these accounts to use a password that never expires

3. Check the master BIOS settings on the server.

Enables hyper-threading and turbo-boost (a super-frequency 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

Use two internal drives in RAID 1 using an integrated RAID Controller

If possible, consider using SSD

If SSD is used, no fragment is required.

Create a separate partition for the C: Drive

Change the Windows page file size to 16 GB and prevent drive C from being

Change Windows Power Plan to "High Performance"

Run the CPU-Z on the server to confirm that the processor is running at full speed

Change the drive letter of the disc drive to Z:

5. Change the NETBIOS name on the server to the permanent name of the required server.

6. Use the built-in function of Windows Server 2012 R2 to install. NET 3.51

7. 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 obtain all required updates.

9. Fragment of drive C

Use scheduled tasks to automatically fragment drive C on a weekly basis

The new drive cannot be automatically added to the scheduler.

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

11. Add the server to the corresponding Windows Domain

12. Activate windows on the server

13. Install the latest version of Dell OMSA on the server (I have never used this item)

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 server firmware and driver

15. Use Dell OMSA to create a RAID array for the LUN

Create a LUN and go to the Logical Disk Manager to create/format the drive.

II. Create arrays and Luns in the order shown below

General PERC settings in Dell OMSA

Use smart images for RAID 10 Arrays

II. No pre-read Cache

III. Enable write-back Cache

IV. Cache Policy should be enabled

V. 64 K allocation Unit

16. Use Windows Logical Disk Manager to create a Logical Disk

After using OMSA to create an array, open the Disk Manager.

You will see the "initialize disk" dialog box

Make sure you use the GPT partition Style

17. check to make sure that the new logical drive is visible in Windows Resource Manager.

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

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

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

21. Create the following folder on each drive

Data 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 the SQL Server service account

Execute Volume Maintenance Tasks

Lock Memory Page

23. Install SQL Server 2012 Enterprise Edition

Make sure that no reboot is pending; otherwise, SQL Server 2012 cannot be installed.

Install only the SQL Server 2012 components required for this instance

C. Authentication in Mixed Mode

Set sa password to Strong Password

II. Add yourself as an 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 SQL Server proxy service to auto start

G. Set the default directory to the corresponding 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 of SQL Server 2012

25. Install SQL Server 2012's latest cumulative update 6

Accumulative updates can be obtained from this location:

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

Manually fragment the C: Drive after installation

If you are using SSD, you do not need to do so.

26. Change SQL Server 2012 instance-level attributes

A. Enable optimize for ad hoc workloads

This will allow SQL Server to use less memory for the first execution to store the temporary query plan

B. Set the maximum degree of 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 the trace flag 3226 as the startup option in the SQL Server Configuration Manager.

This prevents successful database backup messages from being recorded in SQL Server error logs.

E. add trace flag 1118 as the startup option in the SQL Server Configuration Manager.

This will help ease configuration contention in tempdb

F. enable database mail on the instance

Email Notification for SQL Server proxy alerts and SQL Server proxy job failures

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

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

It also depends on the installed SQL Server Component

II. The following are some sample values:

1.96GB total RAM: set the maximum server memory to 87000

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

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

H. Create three more TempDB data files in the T: \ TempDB directory. A total of four tempdb files (no need to align with the number of CPUs at the beginning)

The size of all TempDB data files should be 4096 MB

Set automatic growth to 1024 MB

II. The TempDB log file should be 1024 MB

27. Confirm that you can ping the SQL Server computer from another computer in the domain

28. use SQL Server 2012 Configuration Manager to confirm that the instance has enabled TCP/IP

29. Confirm that you can remotely connect to the SQL Server instance using SSMS on other computers

30. Create an SQL Server Operator On the instance

Use DBAdmin with email address dbadmin@yourcompany.com

31. confirm that the database email is running properly

Right-click Database mail and send Test message

32. Configure SQL Server proxy mail to use database mail

33. Create an SQL Server proxy alert for the following errors:

A. YourServerName Alert-Sev 19 error: Fatal error in resource

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

C. YourServerName Alert-Sev 21 error: Fatal error in the database Process

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

E. YourServerName Alert-Sev 23 error: Fatal error database integrity suspicious

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: irreparable Hardware Memory Corruption Detected

K. YourServerName alert-error 856: SQL Server has detected Hardware Memory Corruption, but the page has been restored

34. Here is a general script for creating these SQL Server proxy Alerts:

Make sure that each proxy alert has a response to notify the DBAdmin Operator

35. Create an SQL Server proxy job named "Maid Free System Cache" and run the following command:

Dbcc freesystemcache ('SQL Plans ');

Run at every night

36. download the latest version of Ola Hallengren's SQL Server maintenance solution script:

Http://ola.hallengren.com/

Open the MaintenanceSolution. SQL script when you connect to the instance.

Change the @ BackupDirectory variable to N: \ SQLBackups.

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

III. For each job, if the job fails, go to the "Notification" attribute window and send the job to the DBAdmin group by email.

IV. Create a running time plan for each job.

V. This is a suggested work schedule:

CommandLogCleanup Sunday

2. DatabaseBackup-SYSTEM_DATABASES-complete daily PM

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

4. DatabaseBackup-USER_DATABASES-all day at AM

5. DatabaseBackup-USER_DATABASES-records every hour

DatabaseIntegrityCheck-SYSTEM_DATABASES Saturday

7. DatabaseIntegrityCheck-USER_DATABASES Saturday

8. IndexOptimize-USER_DATABASES on Sunday

9. File cleanup at AM on Sunday

10. sp_delete_backuphistory on Sunday

11. sp_purge_jobhistory on Sunday.

Summary

I have marked the best practices that I personally think are important in red. However, hyperthreading and turbo-boost are enabled above.

I think hyper-threading should be enabled only when the customer's system can use the redundant logical CPU according to the customer's actual situation. Based on experience, it is usually advantageous for the OLTP system to enable hyper-threading. However, for some report queries, enabling hyper-threading may have adverse effects.
For more information, see: 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. The best practice is to align with the number of CPU cores. But now the optimization has been done, so you don't need to set that much

MBR and GPT

GPT indicates the GUID partition table. (GUID indicates a globally unique identifier ). This is a new standard that is gradually replacing MBR. It complements UEFI-UEFI is used to replace the old BIOS, while GPT replaces the old MBR. The GUID Partition Table is called because each partition on your drive has a globally unique identifier. On an MBR disk, the partition and startup information are stored together. If this part of data is overwritten or damaged, it will be troublesome. In contrast, GPT stores multiple copies of this part of information on the entire disk, so it is more robust and can recover this part of information that is damaged. GPT also saves the Cyclic Redundancy checksum (CRC) for the information to ensure its integrity and correctness-if the data is damaged, GPT will detect these damages, and recover from other locations on the disk. MBR is powerless on these issues-only when the problem occurs can you find that the computer cannot be started, or the disk partitions are missing.
In short, GPT is more advanced and robust, and GPT is recommended.

There is no dispute over other options. What should be observed as far as possible.

The above is all the content of this article and I hope it will help you. If you are interested, please refer to: oracle Database startup analysis, Druid usage instructions for database connection pools, differences between oracle rac and distributed databases, etc. If you have any questions, please feel free to leave a message, welcome to our site for discussion.