DBA basic series SQLServer2014: 1. SQL Server installation and deployment (independent deployment, non-Domain environment)

Objective: with the release of SQLServer2014 on July 15, 201441, the performance of SQL server has been greatly improved, so that more and more users and DBAs are using other databases gradually turn to MSSQL. Here is a personal Blog for you to answer

Objective: with the release of SQLServer2014 on June 1, the performance of SQL server has been greatly improved. As a result, more and more users and DBAs are using other databases are gradually switching to MSSQL. Here is a personal Blog for you to answer

Purpose:

With the release of SQL Server 2014 on February 1, the performance of SQL Server has been significantly improved. As a result, more and more users and DBAs using other databases have gradually switched to MSSQL, here is a personal Blog to answer your questions about SQL Server deployment and to guide production DBAs in initial deployment (only personal experience, not Microsoft opinion ), we recommend that you compare the official best practices with MSDN after reading them.

Test Environment preparation:

Software environment:

Operating System: Windows 2012 Standard Edtion

Database: SQL Server 2014 Enterprise Edtion X64

Hardware environment:

Hyper-V: CPU: 4 Logical Core's; Memory: 16 GB

P.S. with the rapid development of hardware, it is recommended that each Core correspond to 4 GB or 8 GB of memory, and the database is a large memory-consuming user, insufficient memory will cause a large amount of disk IO and CPU overhead

Operating system installation:

(P.S. The operating system installation in the virtual machine is similar to that on the physical server. If it is a physical server, use the boot disk of the corresponding manufacturer for Driver Installation)

Put the system installation image file and start the server to start installation (the blue and quiet square icons are pretty)

Select the language to install

Go to the start preparation start Installation Wizard to perform installation settings

No intervention required for self-loading

Select the Standard Edition with GUI for installation (No Enterprise Edition for Windows 2012, the only difference between the standard edition and the data center edition is whether to build a private cloud environment, so you can select the Standard Edition for the general database server, later, we will introduce the configuration of Core Windows Server. We recommend that experienced DBAs select Core Windows Server)

Accept Microsoft's license (please respect and support genuine software. Thank you. If you are doing an experiment, you can download the 90-day evaluation version from the official website)

Select custom: Only install Windows (advanced). If you need to upgrade the operating system in the future, you are not advised to directly upgrade the operating system in the original place (recommended for personal use). Instead, you can reinstall the system and migrate the database.

In the current test environment, only one GB disk is used directly,

If the production environment is recommended:

Make local storage into a RAID: storage operating system, virtual memory files, database software files

After the system is installed, the plug-in must have at least three Luns (independent RAID ):

LUN1: RAID 10 (composed of four independent disks): used to store tempdb later

LUN2: RAID 10 or RAID 5 (recommended RAID 10): used to store database data files later

LUN3: RAID 1 or RAID 10 (composed of two independent disks): used to store database log files later

The operating system may be restarted multiple times, but manual intervention is not required. You can have a cup of coffee first.

During the automatic restart process, the operating system will prepare the general-purpose hardware driver. We recommend that you install the original driver on the server.

The installation is complete.

Reset the Administrator Password Upon first logon. The password is complex and case-sensitive.

Enter the password again to enter the Server. Now Windows 2012 installation is complete.

Configure the Windows Server Environment

Select the local server tab (Windows 2012 allows you to manage multiple servers at the same time, which is commendable)

Do five things (modify computer name, IP address, disable firewall, enable remote connection, and restart server)

Modify the computer name (note that it is best not to exceed 8 characters, otherwise it will be truncated in NETBIOS. If there are multiple servers with the first 8 characters in the same network segment, NETBIOS may not be correctly recognized)

Disable all firewalls

P.S. The database server should be directed to the end of the Intranet and prohibit any port from being mapped to the Internet.

Enable remote desktop (if you have a special hobby and want to stay in the machine room, I mean I really don't like to stay in the machine room)

Configure IP addresses (for network management allocation)

P.S. We recommend that you assign dedicated VLANs to the database server to facilitate security control.

Restart, cool. I'm not used to such a menu. I don't know if I have the same classmates.

Add roles and functions after restart

In fact, I just want to add functions. Why is it so complicated... ... Start to miss Windows 2008 R2, simple and clear, and get it done quickly

Add the following features:

. NET Framework 3.5: Required for SQL Server Installation

Telnet Server: The last Method When RDP cannot log on (Manual start is required later)

Telnet Client: used to test the port

Remote differential compression: When the bandwidth is tight, you can bind multiple NICs to one to increase the maximum bandwidth. If there is only one, data differential compression is very economical.

Multi-path IO: if there are two or more HbA cards on the server, in order to increase the throughput bandwidth with storage and prevent the storage network from spof, You need to configure it (the current test environment does not even have one HBA card, so skip it)

Do not click Next to install the SDK. Otherwise, you will be disappointed (the. NET Framework installation file is not integrated into Windows in the installation media... ... Just like Linux, leave something in the installation disk... ... )

Mount a Windows Server Installation image to specify the image file path

Now you can install

Prepare a Windows account for the SQL Server service, and the password will never expire (it is recommended that you change the password regularly. In the production environment, individuals prefer to directly use a random 32-bit GUID as the password, who can give it a try? haha)

Add the Database Engine service account prepared for SQL Server to allow locking the Memory Page to prevent memory fragments caused by memory swap between external programs and SQL services.

P.S. This is also one of the reasons why we found that SQL Server has been running for a long time. Although the system still has a number of resources to use, Windows cards are the same as normal.

Now the Basic Windows configuration has come to an end, and more configurations can be changed as needed.

Install SQL Server 2014

Go to the SQL Server 2014 installation disc and enter the topic

Select the installation tab and select the all-new SQL Server independent installation (Cluster installation will be done later)

Enter the product key

In the current test environment, I just select the evaluation version (180 days trial). In the production environment, you can enter the product key.

This function is not feasible. The SQL statement used to determine the specific version (Enterprise Edition, Standard Edition, etc.) is directly identified based on the key. If you need to change the version in the future, you do not need to reinstall it, simply replace the serial number.

Agree to SQL Server authorization

Environment self-check. If there is no problem, you will jump to the next step.

Ask if you want to obtain the latest Hotfix or other patches online through Windows Update Center.

Although Microsoft software updates do not cause any problems, we do not recommend that you install database patches automatically for security reasons. Even if you have patches, DBAs can install them manually, if any problem occurs, you can uninstall the patch or roll back the patch to restore the previous status.

Start to install the support file by yourself, without any intervention.

After automatic installation is complete, go to the next step. There is a small warning and ignore it directly.

Select SQL Server Function Installation

If you do not know which functions will be used in the future, you can choose to install them all, saving your trouble. SQL Server is good here. No matter you do not need to use it, you can directly install all the functions of the data platform, in addition, even if you need to use the same server, you do not need to pay for it.

You can use the default instance or name the instance. You can run multiple instances on the same server as needed.

Fill in the previously created Service Account (SQLAnalysis typed the wrong word, so you can understand it... ... )

Set SQL proxy service to Automatic startup: used to automatically run jobs

Set the SQL Brower service to auto start: Don't underestimate this service. This is awesome. For example, DAC needs to be used, multi-instance dynamic port resolution needs to be used, and analysis services need to be used.

The sorting rule is generally set by default. It is automatically selected based on your Windows regional language. Even if you set Chinese rules on the English version system

Similarly, if you select the American English sorting rule in the Chinese version Windows, it will automatically change to the Latin sorting rule.

Select Windows verification only (either a domain or a Windows Account) or Windows + SQL Server hybrid verification as needed

Which accounts can log on to Windows with the SQL Server administrator privilege (Integrated authentication)

After installing the Analysis Service, you have added the table model for Power BI since SQL Server2012. You can only install one of the two analysis models at a time, that is, each instance name can only locate one Analysis Service, here I chose a traditional multidimensional analysis model.

P.S. the two models deal with different business scenarios. The two models are complementary and not alternative. Just like the relational database and non-relational database, they are complementary and cannot replace one party.

Report service is default.

This replay controller and client are used for SQL database stress testing. The details are not discussed first and then discussed separately.

Leave it blank and modify the configuration file as needed.

Automatic self-check and jump to the next step

Installation overview: If you need to install more SQL Server servers in this way, you can save the configuration file in the following path. Next time, you can directly install the Server using the command line, instead of taking the next step.

Start installation. The rest is waiting.

The installation is complete. Restart the server and initialize the configuration.

Initialize SQL Server 2014

Start SSMS

Configure the maximum and minimum memory values:

Maximum configuration value = total memory-2 GB. I personally prefer to use 80% of memory first, and leave some purchase buffer time when I need to add memory later.

The minimum memory size is the same as the maximum memory size. When the SQL Server database service is started, the Book directly occupies the continuous memory segment.

The minimum query memory is 1 MB by default. We recommend that you set it to 10 MB or 100 MB if there are a large number of complex computations. If you set it too large, the memory allocation time for each session may be too long.

Maximum degree of Parallelism: The default value is 0. Parallel Computing is performed based on the number of cores,

However, if the business system is of the OLTP type, it is recommended to set it to 1, because a large number of statements are simple single-row INSERT, UPDATE, and DELETE parallel operations, which not only fails to improve performance, but leads to performance reduction.

If it is an OLAP data warehouse type, it is enough to keep the default value for the business that focuses on massive data processing.

The SQL Server Configuration Manager should be used to restart the database service, because it contains a number of startup parameters (it is recommended that you do not directly use the Windows Service Manager to perform operations on the Service)

Configure tempdb to the exclusive RAID to ensure the best I/O performance, use T-SQL script migration, and then restart the service, you can refer to the relevant articles in Blog

At the same time, increase the number of tempdb data files from one to eight or 16, and make full use of the multi-core parallel capability. The degree of parallelism mentioned in the front side is not relevant to the thread fiber.

Meanwhile, the tempdb data file is given a large initialization value to avoid frequent automatic scaling.

Keep one log file, and give a proper token to avoid blocking caused by log expansion.

Back up the Master, Model, and MSDB databases.

The Master and Model can be installed once after the first time and before and after each patch update and instance-level configuration change to the database.

MSDB is backed up after a Job is added or changed to prevent unexpected demands.

Summary of the next chapter:

SQL Server user database Initialization Configuration

Hope everything is fine for the distant girl. Good night.

Unconsciously, after 3 o'clock, I went to bed... ... If you have any questions, please kindly advise

This article is from the "Fast" blog. Be sure to keep this source