Shulou(Shulou.com)06/02 Report--

Xiaosheng blog: http://xsboke.blog.51cto.com

-Thank you for your reference. If you have any questions, you are welcome to communicate.

I. brief introduction of permissions

II. Brief introduction to backup

III. Introduction to replication

IV. Cases

.

I. authority

1. The security mechanism of SQL Server

1) Security mechanism of client

2) Security mechanism of server

Account number and password of the login instance

Authentication mode

Permission role

3) the security mechanism of database

"database user" who accesses the database

Permission role

4) the security mechanism of data objects

Query, update, insert, delete

two。 Login permissions settin

1) Authentication mode of SQL Server

A) Windows authentication mode

Use Windows users and passwords

Suitable for internal use of LAN, such as Windows domain environment

B) SQL Server and Windows authentication mode (mixed authentication mode)

User names are created and stored through SQL Server

2) Login permission setting

A) set up login account

Windows user or SQL Server user.

B) use of SA accounts

Set complex password

It is recommended that you disable SA and create a superuser with the same privileges as SA.

3. Access permission settin

1) Server level settings: valid for servers only, invalid for databases and tables

N acts on the entire instance

N access instance

N create database, manage and audit login accounts and other administrative rights in the instance

2) Database level setting: valid for a single database only

N Features:

Acting on a single database

Query, update, backup and restore the database.

N Database users:

A mapping of the login account.

N built-in database user

Dbo: database owner

Guest: guest user.

3) object-level permission settings

N more detailed permissions, acting on tables, views, stored procedures, etc.

N is table authorization

N for database authorization

N authorize stored procedures

II. Backup

1. Backup Typ

1) full backup

Back up the entire database, part of the transaction log, database structure and file structure

Is the basis for any other backup type.

2) differential backup

Back up all data that has changed since the last full backup

3) transaction log backup

Mainly T-SQL statements, recording all the changes to the database, equivalent to incremental backup, support point-in-time restore.

two。 Restore operation

1) you must select a full backup each time you restore the database

2) if all differential backups are used after a full backup, you only need to select the last differential backup after selecting the full backup.

3) if transaction log backups are used after a full backup, you need to select each backup to restore

3. Backup equipment

1) Media supported by SQL Server: disks, tapes, etc.

2) backup Devic

Physical backup device: the name used by the operating system to identify the backup device

Such as C:\ Backups\ Accounting\ Full.bak

Logical backup device: a user-defined alias that identifies a physical backup device

3) advantages of using backup devices: you can query the information of previous backups

III. Replication

1. Introduction to replication

1) function

Synchronize the data and update the data on the main database to other databases

2) advantages

Save cost and reduce workload

3) composition: publisher, distributor, subscriber

Note:

a. Publisher: master database.

b. Distributor: the publisher distributes data to the subscriber through the distributor (the distributor is generally not independent and is on the same machine as the publisher)

c. Subscriber: the server that receives synchronization data.

two。 Note when configuring replication:

L the replicated table must have a primary key

L login mode must be mixed mode (when copying offsite)

L the agent service must be enabled, and the agent service is related to automation (automatic data synchronization)

Port 1433 of l sql server must be open

The TCP/IP protocol in the network configuration in the l sql server configuration Manager is required

1) release method

L Snapshot publication:

Data from the publisher can only be synchronized to the subscriber, but the data cannot be updated.

L transaction publishing:

Data can be published to the subscriber, and when the table is changed at the publisher, the subscriber will update, but when the subscriber updates the data, the publisher will not synchronize, which is one-way

L merge publication:

It takes up a lot of resources, but it is possible for the publisher to update the data, the subscriber to update the data, and the publisher to update the data.

Transactional publications with updatable subscriptions:

New features added to version 05

Explanation: generally speaking, the latter two publishing methods are rarely used, because they generally do not allow both parties to update data, and generally use transactional publishing. When doing transactional publishing, you must first do a snapshot release (according to the wizard settings).

2) there are two subscription methods

a. Make a push subscription at the Distributor, also known as a mandatory subscription (at the Distributor).

b. Pull subscription (used at the subscriber).

3) statements to be executed after the instance name is modified:

Sp_dropserver instance name of the original database: the instance name of the database defaults to the name of the computer, so when the computer name changes, the previous instance needs to be deleted or the new instance name cannot be recognized (brackets'[] 'are required if the instance name has special characters)

Sp_addserver new instance name, local: apply the new instance name. If it is a local computer, you must add', local', to the instance name and restart the instance.

3. Specific implementation process

1) New replication on the publisher

2) choose a subscription method

IV. By column

Summary of this example

For example: publisher and Distributor are: WIN-B8RVQT412MQ

Subscriber: hangzhou

Requirements: 1. Use the replication function to synchronize the shop table on WIN-B8RVQT412MQ to hangzhou

two。 Use transaction snapshots (only publisher update data is synchronized to the subscriber, not vice versa)

3. Use push subscriptions (for consistency)

Note: through experiments, it is found that although the subscriber updates the data using the transaction snapshot, the publisher will not synchronize, but the subscriber can update the data of the synchronized table, just out of sync.

1. What you need to do after changing the computer name (because the database may not be recognized after changing the computer name)

two。 The first step in replication is to open port 1433 on the subscription and distributor to ensure database connectivity (we have the distribution and publisher installed together).

3. Turn on the proxy service for the publisher and subscriber, respectively, because the proxy service involves automation.

4. Check whether the TCP/IP protocol is enabled in the configuration tool of SQL SERVER, otherwise it will affect the connectivity between the distributor and the subscriber.

5. The login mode must be mixed mode. It is best to restart the instance after the mode is modified.

6. The replicated table must have a primary key, as shown in the following example.

7. Start publishing this table (this example is a transaction publication. For more information, please see the detailed documentation)

8. Start creating a new subscription (this is a push subscription, so you can create it at the distributor. Here are only a few points to pay attention to)

9. Verify that the replication was successful.

10. Verify that the publisher updates the data and that the subscriber is synchronized.

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.