Database server installation and configuration How to build a database dedicated server

Theoretical basis

Virtual dedicated server

Database server is one of the most widely used server types, and many enterprises have to purchase database server in the process of informatization construction. Database server is mainly used for storing, querying and retrieving information inside the enterprise, so it needs to be paired with a special database system, which has high requirements on the compatibility, reliability and stability of the server.

Virtual dedicated server hosting


1. Basic Concepts

Virtual dedicated server price


The database server is actually a server with a single database, which is applied to the Internet or intranet. A database server is a database management system software that runs on one or more server computers in a local area network, and the database server provides services for customer applications, such as query, update, transaction management, indexing, caching, query optimization, security, and multiuser access control. Database software has many kinds of large database software, such as oracle,db2,sybase, dedicated server de,medium-sized SQL Server, and usually for personal web site, such as MySQL small database software.


Buy virtual dedicated server

2, enterprise business to the database server requirements

Dedicated server
(1) The database server should have a strong load-carrying capacity. When the enterprise needs to query some information, its background processing system (database server) is about to start querying, and will find the feedback to the user. When a large number of users in the query information,dedicated server hosting, if the database server does not have a strong load-carrying capacity, is unable to meet their needs.





(2) The database server should have a strong real-time response ability. When the data information is retrieved, it will be fed back to the user, if the database server does not have a strong ability to respond, it will result in very slow feedback information, and even caused the crash of the database server.





(3) The database server has a strong ability to expand. Information in the database will change at any time, now is the information age, a large number of data need to input, processing, etc., therefore, database server expansion ability should not be neglected.





(4) Availability and reliability of the database server. The database server has the ability to run uninterrupted for 365 days to meet the needs. Of course, it's not too realistic for a server to really keep running like this. As a result, many enterprise database servers are not only one, usually have multiple units, and also equipped with a backup server and so on.





Large enterprises require a higher level of database server performance and scalability, and when they build a database system, they not only use multiple database servers, but also storage systems. Small and medium-sized enterprises are somewhat different, they need database server performance does not need to be as strong as a large enterprise database server, the number of requirements is not so much, it is easier to select the server.





3. Advantages of database server





(1) Reduce the amount of programming





The database server provides a standard interface API for data manipulation.





(2) Database Security guarantee Good





The database server provides tools for monitoring performance, concurrency control, and more. The DBA is responsible for authorizing access to the database and network management.





(3) Data reliability management and recovery good





The database server provides a unified database backup and management tool for recovering, starting, and stopping databases.





(4) Making full use of computer resources





The database server divides the data management and processing work from the client, so that the resources of each computer on the network can be used.





(5) Improved system performance





Can significantly reduce network overhead.





Coordinate operations to reduce resource competition and avoid deadlocks.





Provides an online query optimization mechanism.





(6) Easy Platform expansion





Multi-processor (same type) horizontal scaling.





Horizontal scaling of multiple server computers.





Vertical scaling: Servers can be ported to more powerful computers without addressing redistribution of data.





4. SQL Server 2000 benefits as a database server





(1) Microsoft SQL Server 2000 can provide the database services required for very large systems.





A large server may have thousands of users connected to an instance of SQL Server 2000 at the same time. SQL Server 2000 provides comprehensive protection for these environments, with security measures to prevent problems from occurring. SQL Server 2000 also efficiently allocates available resources, such as memory, network bandwidth, and disk I/O, among multiple users.





A very large Internet site can separate its data on multiple servers, allowing the processing load to spread across multiple computers, allowing the site to serve thousands of concurrent users.





You can run multiple instances of SQL Server 2000 on a single computer. For example, an organization that provides database services for many other organizations can run a single instance of SQL Server 2000 for each customer organization, and all instances can be centralized on a single computer. In this way, data is separated from each customer organization, while the service organization simply manages one server computer, thereby reducing the cost.





SQL Server 2000 applications can run on computers that have SQL Server 2000 installed. The application connects to SQL Server 2000 through Windows interprocess communication (IPC) components, such as shared memory, rather than over the network. This enables SQL Server 2000 to be applied to small systems where the application must store data locally.





shows an instance of SQL Server 2000 that is a database servers for large Web sites and traditional client/server systems



Large WEB sites and enterprise-level data processing systems typically generate a large amount of database processing, which exceeds the support capabilities of a single computer. In these large systems, the database service is provided by a set of database servers that comprise the database service layer. SQL Server 2000 does not support the creation of a database service layer with a balanced load aggregation, but supports the mechanism of data storage across autonomous server components. Although each server needs to be managed separately, each server within the group can share the database processing load. A set of autonomous servers that share the same workload is called a federation of servers.





(2) Desktop database system





Not only does SQL Server 2000 work effectively as a powerful database server, but the same database engine is used in applications that need to store standalone databases locally on the client. SQL Server 2000 can dynamically configure itself to efficiently use the resources available on client desktops or laptops without having to dedicate a single database administrator to each client. Application vendors can also embed SQL Server 2000 as an application's data storage component in an application.





When a client uses a local SQL Server 2000 database, the application connects to an instance of the local database engine in a way that is very similar to the database engine running on a remote server that is connected across the network. The main difference between the two is that the local connection is through the local IPC (such as shared memory), and the remote connection must pass through the network.





Displays the use of SQL Server 2000 in the Desktop database system.























Training Content





First, the practical training environment





1, the student machine every two people a group One;





2, the operating system installed Windows 2000Server;





3, SQL Server2000 installation program;










Second, practical training requirements





1, before the training to do a good job on the preparation of the training, in accordance with the content of the training, carefully review the knowledge related to this practice training, complete the preparation of the training content;





2, can conscientiously independently complete the training content, master SQL Server 2000 environmental requirements and installation process;





3, master the SQLServer2000 server registration and configuration methods and skills;





4, master the creation and management of disk backup;





5, master the backup database and database recovery method;





6, master the database server security management;





7, after the actual training to do a good job summary, according to the actual training situation to complete the summary report.










Third, practical training steps:





(i), SQL Server 2000 database server installation and configuration





1, the CD-ROM of SQL Server 2000 into the CD-ROM drive, if there is Autorun function, the start screen will appear, or run the Autorun program directly, the Start screen appears, 1 shows.

















Figure 1 Start Screen











2. Select SQL Server 2000 Simplified Chinese Enterprise Edition, open the Install SQL Server 2000 Enterprise Edition window as shown in 2, and select the installation content from.









































Figure 2 SQL Server 2000 Enterprise Edition





3. Select "Install SQL Server 2000 Components" to open the Installation Server 2000 Components window shown in 3.

















Figure 4-3 Installation Components





The following three options appear on the screen that opens.





Install Database server: Starts the SQL Server Setup program, which contains a screen that selects the installation options.





Install Analysis Services: Install Analysis Services on the computer that processes the OLAP cube.





Install English Query: Install English Query on the computer that is running the English Query application.





4. Select "Install Database Server" and a welcome dialog box appears in Figure 4.





























, Figure 4: Welcome dialog box





5. Press Next step to enter 5 to select the Computer name window.



































Figure 5 Entering the computer name





In Figure 5, there are several options:





Local computer: By default, the name in the edit box is the name of the local computer (the computer that is running the Setup program). For a local installation, accept the default values and click the Next button.





Remote computer: Enter the computer name for remote installation, or click the Browse button to locate the remote computer.





Virtual computer: Enter the name of a new or existing virtual SQL Server to be managed. This option is available only when Microsoft Cluster service (MSCS) is detected on a Windows NT or Windows 2000 Enterprise operating system.





Browse: Click the browse button to locate the remote computer. This button is available only when the remote computer option is selected.





6. Select the "Local Computer" option, and the installation selection dialog shown in 6 appears.



































Figure 6 Installation Options





You can select one of the three options on the screen to run the Setup program:





Create a new instance of SQL Server or install Client tools: Creates a new instance of SQL Server 2000, which is the default instance or named instance. In addition, this option allows you to install only client tools on any operating system other than Microsoft Windows 95 using any version of SQL Server 2000 CD-ROM.





To upgrade, delete, or add components to an existing instance of SQL Server: This option allows you to upgrade, delete, or add components to an existing instance of SQL Server. Existing instances include earlier versions (SQL Server version 6.5 and SQL Server version 7.0) and the installation of SQL Server 2000 instances.





Advanced options: Select advanced options for cluster maintenance, unattended installation, and registry rebuild. This installation mode should be used for initial installation, and does not require the use of advanced options for installation. The content in the advanced options can be adjusted after the installation is complete.





7, if it is the first time to install SQL Server, you should choose to create an instance and client, here we choose this option to continue. Enter the user information interface, shown in 7.

















Figure 7 User Information





The User Information installation box prompts you to enter your name and company name. These fields are required to be entered.





When installing on a network, be sure to provide the name of the user who is responsible for using or administering the server.











8. Enter the name and company name and click the Next button. Read the license agreement, users should read them carefully before continuing with the installation, and accept the terms click Yes to continue.











Figure 8 Software License Agreement





9, choose the type of installation, a total of 3 options, 9 is shown.























Figure 9 Installation Definition





Client Tools only: Installs client-side relational database administration tools only. This option contains client tools and client connectivity components that manage SQL Server. In addition, this option allows you to select additional components to install. If SQL Server is already installed on another machine, you can install only client tools for access to SQL Server on other machines.





Server and Client Tools: This option executes the install Server and client tools to create a relational database server with administrative capabilities. Selecting the Server and Client Tools option displays all the additional installation options.





Connect only: Installs only the relational database client connectivity components, including the MDAC 2.6 (Microsoft Data Access Components) required to connect to the SQL Server 2000 named instance. This option provides only the connection tool and does not provide client tools or other components.





10. In the "Installation Definition" window, select the "Server and Client Tools" option to install. We need to install both the server and the client so that on the same machine we can do all the relevant things. Press "Next" to see the Select Instance Name dialog box shown in 10. Select either the default option or the name of the instance you are naming.





























Figure 10 Selecting the instance name





11. Select the installation type and path. The SQL Server Setup program provides three installation types in the Installation Type dialog box. In addition, you can modify the installation location of the program and data files in the dialog box. As shown in 11.





Typical: Install the entire SQL Server with the default installation option. It is recommended that most users use this installation.





Minimum: Installs the minimum configuration required to run SQL Server. It is recommended that users of computers with minimum free disk space use this installation.





Custom: Installs SQL Server and can change any or all of the default options. Use a custom installation to select components and subcomponents, or to change settings for collations, service accounts, authentication, or network libraries.











Figure 11 Installation type and path





12. Select the "typical" installation option and specify "Destination folder". The default installation location for program and data files is "C:\Program Files\Microsoft Sqlserver\". The author because the C drive is the system area, D disk is the application area, selected D disk. The Service Account dialog box appears, as shown in 12.





























Figure 12 Service Account Settings





Using the Service Account screen in Setup, assign the login account to two SQL Server services: SQL Server and SQL Server Agent. You can use a local system account or a domain user account, and two services can use the same account.





The default setting is "Use the same service and start each service automatically." To use the default settings, enter the domain name password, and then click Next.





You can also customize the settings for each service. You can enter the same login account for two services, or you can specify an account for each service separately. To change the set of options on the Service Account screen later, run the Services application on the Windows Control Panel.





The options are set as follows:





"Use the same account for each service. Start the SQL Server service automatically. As the default option. An account is used for both SQL Server and SQL Server agents. These services start automatically when the operating system starts.





"Customize settings for each service." Allows you to use different settings for two services.





Services: Select the service you want to customize settings for.





SQL Server: Select this option to customize the settings for the Microsoft SQL Server service.





SQL Server Agent: Select this option to customize the settings for the Microsoft SQL Server Agent service.





"Service Settings": Select the desired service settings.





Use Local System account: The Local System account does not require a password or network access for Windows NT 4.0, and it may restrict SQL Server installation from interacting with other servers.





Use domain user account: The domain user account uses Windows authentication settings and connects to SQL Server. By default, the account information for the domain user account that is currently logged on to the computer is displayed.





User name: Accept or change the domain user name.





Password: Enter the domain password.





Domain: Accept or change the domain name.





Auto-start service: Selecting this option will automatically start the service when the operating system starts. You can use this option only if you customize the settings for each service.





13. Select "Use the same account for each service, start the SQL Server service automatically", the server settings select "Use Local System account", and if you need to "use a domain user account", add the user to the Windows Server's native Administrators group. Click the "Next" button to eject the "Authentication Mode" window.























Figure 13 Authentication Mode





14. Select "Mixed mode (Windows authentication and SQL Server Authentication)" and set the password for the administrator "sa" account. If you are only trying to learn, you can set the password to NULL for easy login. If it is a real application system, then you must set up and keep the password! If you need more security, you can select Windows Authentication mode, in which case only local and domain users of Windows Server can use SQL Server. Click the Next button to bring up the "Start Copying Files" dialog box window.

















Figure 14 Start copying files





15. Click the "Next" button to enter the "Select License Mode" dialog box, prompting the user to select the Customer license Agreement method. SQL Server supports two types of license agreements: Processor License and per customer. The processor license method requires that each connection to this server has a processor access license. Per Customer mode each computer that accesses SQL Server has a client access License. Generally select the "per customer" mode and enter the number of licenses.











Figure 15 Choosing a licensing model





16. Click the "Continue" button to install the program and start the installation process automatically.





























Figure 16 Installation process





17, when the installation program to complete the file replication and system configuration, the Installation Complete dialog box appears, click the Finish button to end the installation process.





























Figure 17 Installation Complete





18, check the installation: if the SQL Server Service Manager can start and shut down properly after installation, the finance indicates that the database server is installed properly.























Figure 18 Starting SQL Server Service Manager











(ii)Server registration and configuration for SQL Server 2000





Example 1 establish the database server alias "Mylocaldbs", connect the local database server, complete the server registration.





The following are the steps:





1. Start SQL Server Service Manager





Use the menu "start"? " Program "?" Microsoft sql Server "?" Service Manager or the icon in the lower-right corner of the screen to start SQL Server Service Manager. Click the Start/Continue button to start the service.





2. From "Start"? " Program "?" Microsoft sql Server "?" The server Network Utility launches the Server Network Utility, as shown in 19. Select TCP/IP in the Enabled Protocols list box under General Options to indicate communication with the managed database server over the TCP/IP protocol. Click the Properties button to configure.











Figure 19 Configuring the Server network Communication protocol





3, in the window shown in 20, you can see the default port value is 1433, there is no need to change, only if you install multiple database server instances on the same computer to modify the configuration, click the OK button.



























































Figure 20 Configuring the TCP/IP protocol port





4. From "Start"? " Program "?" Microsoft sql Server "?" The Client Network Utility launches the Client Network Utility, as shown in 21. Under Common options, select TCP/IP in the Enable protocols in order list box, and then click the Properties button to configure the client.









































Figure 21 Client TCP/IP protocol configuration





5, in the window that appears to check the port value of the client connection database server, the requirements must be consistent with the port value used by the database server, there is no need to change the default setting value (1433), click the OK button.





6. Next, establish a connection between the client and the database server. In the window shown in 21, select the Aliases option page, 22, and click the Add button.





























Figure 22 Setting up a client database server alias





7, the window appears 23, according to the configuration of the database server to enter the appropriate information. "Server alias" is the server code to be managed, enter "Mylocaldbs", "Network Library" is the client and Server Connection network communication protocol, here Select "TCP/IP", and server-side settings consistent, the default port is set by the previous 1433; "Server name" You can enter the IP address of the database server or enter the server name (that is, the name of the destination database server you want to connect to), where you can enter the name of the server that is started locally, and then click the OK button.



































Figure 23 Setting connection information for the database server





8. In the window that appears, click the OK button. The connection configuration information for the database server is now complete.





9, then start Enterprise management, to register the server. Use the menu "start"? " Program "?" Microsoft sql Server "?" Enterprise Manager "to start.






10. In the 24 window, expand the Microsoft SQL Server subkey, select the server group, right-click, and choose the new SQL Server registration option from the shortcut menu.





Figure 24 Creating a new server registration





11. Click the Next button directly in the Register SQL Server Wizard window that appears.





12. The database server name on the SQL Server 2000 Autodiscover network and the database server alias configured with Client Network Utility appear in the Available Servers list box on the left side of the window shown in 25. Select the previously established server with the alias Mylocaldbs, click the Add button to add it to the Add Server list box to the right, and then click the Next button.



































Figure 25 Selecting the server to register





13. In the window that appears in the Select authentication mode, 26, select the "System administrator ..." option, which indicates that the account and permissions for logging on to the database service are assigned by the database administrator. If you select another option, the server's operating system assigns access to the account and permissions to the database server. Click the Next button.





























Figure 26 Server Authentication Mode selection





14. In the "Select Connection Options" window shown in 27, select "Auto-entry with my SQL Server account information". Enter your user name and password in the text box, and note that you will enter the settings when you install the database system. When you are finished, click the Next button.





























Figure 27 Server logon account settings





15. The Select SQL Server Group window appears, as shown in 28. Select Add SQL Server in existing QL server group here and click the Next button.







































































Figure 28 Adding a server to a server group





16. Click the Finish button directly in the "Complete registration" window.























Figure 29 Completing the registration





17, the next computer will follow the registration information and the server to establish a connection and pass authentication. After the registration is successful, the 30 window appears and click the Close button. The server registration is complete. You can activate a newly registered server by double-clicking it in Enterprise Manager.

















Figure 30 Server Registration succeeded





18. Start Enterprise Manager, select the registered database server, right-click, choose Properties from the shortcut menu that appears, and 31 the Properties dialog box shown. In this dialog box, you can configure the various aspects of SQL Server.





Here we use the default settings data.





























Figure 31 Configuring the Server's Properties dialog box











(iii) backup and recovery of databases





1. Define Backup devices





Example 1: Create a backup device in Enterprise Manager with a logical device named Student_backup and a physical device named E:\student_bak\full.bak (if the Student_bak directory does not exist on the e-packing directory, it should be established first)





(1) Open Enterprise Manager, expand the server you want to operate (here is cqhh), right-click on the backup in the Administration folder (or click on the system's Action menu) to select the "New backup Device" command, the following dialog box appears:





























Figure 32 Creating a new backup device





In the dialog box, enter the logical device name for the backup device in the Name text box student_backup: Second, select the device type by using the Radio box (this is the default device because there is no tape device on the local computer), enter or tap "..." in the File name text box. button to determine the physical device name of the backup device, which is the disk file path and filename E:\student_bak\full.bak for the backup data; The disk backup device was created successfully by clicking the OK button.



































Figure 33 Creating a new backup device











Example 2: Use the system stored procedure sp_addumpdevice to create a disk device whose logical device name is Stud_logbak and the corresponding physical device is named D:\student_bak\bak1.bak





In Enterprise Manager, a full backup of the database Stu (new database Stu)





(1). Open Enterprise Manager, expand the CQHH server and database folders, right-click the Stu database, point to the All Tasks submenu, and select the Backup Database command, which appears as shown in the Backup Database dialog box:





























Figure 34 Backing up the database





(2) Select the database you want to back up in the database box, and by default, the database that you just right-clicked is selected.





(3) Enter an easy-to-identify name for the backup in the "Name" box;





(4) Enter a descriptive information (optional) for the backup in the Description box, and this example enters the first full backup of the student database;





(5) Backup options Click the database-full button to first make a full database backup, which is the basis for all backups;





(6) For the purpose option, click the Disk radio button, and then click the Add button to the dialog box that appears:



































Figure 35 Selecting a backup destination





Select "File name" in this dialog box to radio button The backup contents of the database using a temporary backup file, select "Backup Device" radio button means to use a permanent existing backup device or create a new backup device to store the backup contents of the database, this example selects the device Student_backup now, and click The OK button returns to:





Figure 36 Backing up a database----Selecting a backup device





(7) Under Override options for the diagram, do one of the following: Click Append to Media to append the backup to any existing backup on the backup device, and click Overwrite existing media to override the contents of the backup device. This example selects overwrite existing media because the device is used for the first time in this case, so select it to initialize and identify the settings;





(8) The Schedule check box is used to set the backup schedule, that is, to schedule the backup operation to be performed at a later time or to be executed periodically (optional). If you want to set a backup schedule, select the item and click the button on the right. The dialog box shown in the diagram appears. In this dialog box, you can set up 4 backup schedules: Database backups automatically whenever the SQL Server agent starts, database backups when the CPU is empty, and a one-time database backup, a database backup by a certain period, and the Change button on the right. In the dialog box that appears, set the date frequency and period at which the backup work occurs. In particular, it is important to emphasize that the SQL Server Agent service must have been started before selecting the topic. This check box is not selected in this example.























Figure 37 Editing the schedule





(9) Select the "Options" tab to enter the page as shown:















































Figure 38 Backing Up the Database tab











(10) In the Verify that the backup device is verified by setting the Verify Backup after completion check box. The purpose of the verification is to ensure that all of the database's dwellings are saved to the backup device correctly and without errors;





(11) Check the backup media to prevent accidental rewriting by setting "Check media set name and backup set expiration";





(12) If you are using backup media for the first time, or if you want to change an existing media label, under the Media Set Label box, select the Initialize and identify media check box, and then enter the media set name and media set description. The period can be initialized and identified only when the media is rewritten;





(13) After completing the settings in the General dialog box and the Options dialog box, click the OK button and press the OK button in the subsequent Database Backup success message box to complete a full backup of the database.





3. Restore the Database





1) Recover database with Enterprise Manager





Example: Delete the Stu database, and then restore it in Enterprise Manager.





Before the database was deleted, three backups were created: Full database backup, two tables created after the backup (NEW1,NEW2), a first differential backup of the database, a new table (NEW3,NEW4) After the differential backup, and a second differential backup. So the Stuscore database can only be restored to the time of the second differential backup. Recover Stuscore Database policy: Restore a full database backup to restore the second differential backup. The specific recovery steps are:





(1) Open Enterprise Manager, expand the CQHH server, right-click on the database folder, select "Restore Database" in "All Tasks" from the popup shortcut menu, and the dialog box appears:





























Figure 39 Restore Database dialog box





(2) In the Restore dialog box, enter the STU database into the database, and then select the "From Device" radio button, as shown in:





Figure 40 Restore Database dialog box-from device





(3) Click on the Select device in the diagram, if the device text box in the diagram already has the device you need to omit this step. The following dialog box appears:



































Figure 41 Selecting the Restore Database dialog box





(4) Select the device in the diagram (here is the disk), then click the "Add" button to enter the dialog box shown:























Figure 42 Selecting the Restore Destination dialog box





(5) Select "Backup Device" radio button in the dialog box, then select the Student_backup device in the list box, click "OK" button to go back to the previous diagram, and then click "OK" in the diagram to return to the previous diagram (the figure has changed)





(6) in the diagram after the above operation, select "Restore Backup Set" radio buttons "Database Complete" radio button. (If the device number for the full backup of the database you want to restore is not 1, also click "View Content" to re-select). Now select the Option tab of the Restore Database dialog box and enter:



































Figure 43 Restore Database dialog box-options





(7) The system shows the data file name and physical file name (which can be changed) of the restored database in the diagram. The main option here is "restore complete status", in this case you should select "Enable the database to continue running, but cannot restore other transaction logs" radio button. Now click OK to restore the database. In the information box that appears after the recovery has completed successfully, click OK in the message box.





Figure 44 Restore Complete





Now back to the Enterprise Manager main window, you can see that the STU database is saved.











(iv) SQL Server server security management





1. View the security authentication mode of SQL Server in Enterprise Manager





In the SQL Server container, right-click the current instance, and then clicking Properties, the SQL Server Properties (Configuration) dialog box appears, and the Security page is selected as shown in:



































Figure SQL Server properties (config)





You can see the current authentication mode for SQL Server and Windows Mixed mode;





2. Use Enterprise Manager to create a login account Logina in SQL Server and give it the system Administrator role





(1) Locate your server in the SQL Server container of Enterprise Manager, select "Security" below, select "Login" below, right click and select "New Login" in the pop-up menu, as shown in:





Figure 46 New Login





(2) Select SQL Server Authentication, enter Logina in the Name text box, enter the password for the login in the Password text box, select the server Roles page, and select the first server role, System Administrators, as shown in:









































Figure 47 New Login-server role





(3) Click "OK" button, the system will prompt "Confirm password", re-enter the password you just set, click "OK" to complete the login role creation. As shown in 48:

















Database server installation and configuration How to build a database dedicated server