SQL Server Application Development (III)

Source: Internet
Author: User
(2) create registration: When managing remote or local hosts, you must first register the instance: 1. Right-click the selected SQLSERVER group and choose create SQLSERVER registration, dialog Box 2 is displayed. In the available servers list of the wizard, select the remote server you want to manage. The local server can be used. and then add it to the right

(2) create registration: When managing remote or local hosts, You need to first register: 1. Right-click the selected SQL server group and choose create SQL SERVER registration, dialog Box 2 is displayed. In the available servers list of the wizard, select the remote server you want to manage. The local server can be used. and then add it to the right

(2) establish registration: When managing remote or local hosts, you must first register
Method:
1. Right-click the selected "SQL server group" and select "create SQL SERVER Registration". The corresponding dialog box is displayed.
2. Select the target remote server from the list of available servers in the Wizard. You can use "." To add the local server to the list on the right and click "Next"
3. Select an authentication method, which can be divided into two types:

A. Verify with A windows identity: Verify with A windows login. After successfully logging on to the system, you can log on to SQL SERVER.

B. verify with a mix of SQL server and WINDOWS: that is, after logging on to the operating system, you also need to verify when logging on to SQL SERVER (after selecting this option, "Next" will prompt you to enter the corresponding verification account or password.) After the selection is complete, "Next"

4. Select the SQL SERVER group to be registered or create a new top-level group.

5. Click "finish". If the connection and verification are correct, the registration is successful.


(3) Establish logon and grant permissions:

1. Create an SQL server Logon account: the account required to log on to the SQL SERVER system.
Method: under the "Security" node in the Enterprise Manager, right-click "Logon" and select "New Logon". A dialog box is displayed, as shown in the following figure:

A. Regular tab: Enter the account name and the corresponding authentication method. The authentication method is the same as "create registration". You can also specify the default logon database for this account.
B. Server role: You can set the role of the account on the server. The detailed definition of the role is not explained here.

C. database Access: You can define the database that the user can access and the account alias in the database to be accessed. Only the database to be accessed can be accessed, the account alias is the name used by the account in the specified database.


2. Create a database user: Define the database that the login account can access

Method: Under the database that can be accessed, right-click "user" and select "Create Database User". The corresponding dialog box is displayed, and the account is selected, you can also name the account alias in the database.


3. grant permissions to the account on related tables in the database:

Method: In the database user list, right-click the logon and select "all tasks"-"manage permissions" from the shortcut menu ", you can grant corresponding permissions to the corresponding table and perform operations only after authorization.


VI,System database and example Database: (understanding)

(1) SQL SERVER databases are classified by purpose:
1. System Database: Manages and maintains the database necessary for SQL SERVER.

2. Example database (user database): a database created by the user. The example database is also a user database and provides some databases as examples.


(2) System database:

1. Master database: records all system-level information

2. Tempdb Database: stores all temporary tables, temporary storage procedures, and temporary tables.

3. Model database: Template used to create all databases on the System

4. Msdb Database: high-alert, job, and record operations for SQL SERVER Agents
(3) Example databases: Pubs and Northwind


VII,Create and manage databases (master)

(1) Create a database:

1. database file structure: a database must contain at least one database file and one transaction log file.

A. database file: a FILE that stores DATABASE data and DATABASE objects. a database can contain multiple DATABASE files. a database file only belongs to one DATABASE, DATABASE files can be divided into primary database files and secondary DATABASE files. The former is used to store DATABASE startup information and the extension is. mdf. The extension is. ndf.


B. TRANSACTION LOG FILE: used to record database updates. The extension is. ldf. A database can have multiple TRANSACTION LOG files.


C. file group: similar to folders, it is mainly used to allocate and manage disk space. It can be divided into the primary file group and the secondary file group file)



2. Create a database in the Enterprise Manager:

Method: Right-click "create database" on "Database". A dialog box is displayed.

1) "general" tab: You can set the database name to Chinese

2) "Data Files" tab:

A. File Name: physical file name of the database

B. Location: Physical location of the database
C. Initial size: the space when the database is created
D. File Group: The file group to which the file belongs
E. Automatic File growth: whether to allow automatic database space growth after data increases, which can be divided into % growth and MB Growth

F. Maximum file size: determines whether to limit the maximum space of the database after the data increases.

3) "transaction log" tab: set the same as the "data file" tab


(2) database shrinking:
When the database data has too much free space due to updates, the database file space is reduced to avoid space waste.

Method: Right-click the database to be operated and select "all tasks"-"shrink Database"


(3) mobile databases:

Operations performed when database files need to be transferred between different hosts. Before moving a database, you must perform the "detach" Operation on the database on the host. After the "detach" operation, copy the database file directly to the target host, and then "Attach" the database.

1. "detach" the database: Right-click the database to be operated and select "all tasks"-"detach Database"

2. "Attach" the database method: Right-click "Database", select "all tasks"-"Attach Database", and select the location of the database file.


(4) Configure Database options:
Configure database related options, right-click the database to be prepared, select "properties", and select the "options" tab. The main options are as follows:
1. access restriction: If "DB_OWNER, DBCREATOR, SYSADMIN" is selected, only the database creator and System Administrator of the database owner have the right to use the database. If "single" user is selected, indicates that the database can only be used by one user at a time.
2. Read-Only: Read-only, view, and cannot be modified.
3. ANSI Null default settings: Allow NULL values in database tables
4. Automatic shutdown: when no user uses the database, the database is automatically shut down. The database is checked regularly. When the unused space exceeds 25% of its size, the database is automatically shrunk.

(5) delete a database:
Delete unused databases, right-click the database to be deleted, and select "delete". The deleted database cannot be recovered.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.