Log Shipping

Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. An optional third server instance, known as the monitor server, records the history and status of backup and restore operations and, optionally, raises alerts if these operations fail to occur as scheduled.

Log shipping consists of three operations:

  1. Back up the transaction log at the primary server instance.
  2. Copy the transaction log file to the secondary server instance.
  3. Restore the log backup on the secondary server instance.

The log can be shipped to multiple secondary server instances. In such cases, operations 2 and 3 are duplicated for each secondary server instance.

A log shipping configuration does not automatically fail over from the primary server to the secondary server. If the primary database becomes unavailable, any of the secondary databases can be brought online manually.

You can use a secondary database for reporting purposes. For more information, see Using Secondary Servers for Query Processing.

In addition, you can configure alerts for your log shipping configuration.

The primary server in a log shipping configuration is the instance of the SQL Server Database Engine that is your production server. The primary database is the database on the primary server that you want to back up to another server. All administration of the log shipping configuration through SQL Server Management Studio is performed from the primary database.

The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning.

The secondary server in a log shipping configuration is the server where you want to keep a warm standby copy of your primary database. A secondary server can contain backup copies of databases from several different primary servers. For example, a department could have five servers, each running a mission-critical database system. Rather than having five separate secondary servers, a single secondary server could be used. The backups from the five primary systems could be loaded onto the single backup system, reducing the number of resources required and saving money. It is unlikely that more than one primary system would fail at the same time. Additionally, to cover the remote chance that more than one primary system becomes unavailable at the same time, the secondary server could be of higher specification than the primary servers.

The secondary database must be initialized by restoring a full backup of the primary database. The restore can be completed using either the NORECOVERY or STANDBY option. This can be done manually or through SQL Server Management Studio.

The optional monitor server tracks all of the details of log shipping, including:

  • When the transaction log on the primary database was last backed up.
  • When the secondary servers last copied and restored the backup files.
  • Information about any backup failure alerts.

The monitor server should be on a server separate from the primary or secondary servers to avoid losing critical information and disrupting monitoring if the primary or secondary server is lost. A single monitor server can monitor multiple log shipping configurations. In such a case, all of the log shipping configurations that use that monitor server would share a single alert job.

Important note Important
Once the monitor server has been configured, it cannot be changed without removing log shipping first.

For more information, see Monitoring Log Shipping.

Log shipping involves four jobs, which are handled by dedicated SQL Server Agent jobs. These jobs include the backup job, copy job, restore job, and alert job.

The user controls how frequently log backups are taken, how frequently they are copied to each secondary server, and how frequently they are applied to the secondary database. To reduce the work required to bring a secondary server online, for example after the production system fails, you can copy and restore each transaction log backup soon after it is created. Alternatively, perhaps on a second secondary server, you can delay applying transaction log backups to the secondary database. This delay provides an interval during which you can notice and respond to a failure on the primary, such as accidental deletion of critical data.

Backup Job

A backup job is created on the primary server instance for each primary database. It performs the backup operation, logs history to the local server and the monitor server, and deletes old backup files and history information. By default, this job will run every 15 minutes, but the interval is customizable.

When log shipping is enabled, the SQL Server Agent job category “Log Shipping Backup” is created on the primary server instance.

SQL Server 2008 Enterprise and later versions support backup compression. When creating a log shipping configuration, you can control the backup compression behavior of log backups. For more information, see Backup Compression (SQL Server).

Copy Job

A copy job is created on each secondary server instance in a log shipping configuration. This job copies the backup files from the primary server to a configurable destination on the secondary server and logs history on the secondary server and the monitor server. The copy job schedule, which is customizable, should approximate the backup schedule.

When log shipping is enabled, the SQL Server Agent job category “Log Shipping Copy” is created on the secondary server instance.

Restore Job

A restore job is created on the secondary server instance for each log shipping configuration. This job restores the copied backup files to the secondary databases. It logs history on the local server and the monitor server, and deletes old files and old history information. The SQL Server job category “Log Shipping Restore” is created on the secondary server instance when log shipping is enabled.

On a given secondary server instance, the restore job can be scheduled as frequently as the copy job, or the restore job can delayed. Scheduling these jobs with the same frequency keeps the secondary database as closely aligned with the primary database as possible to create a warm standby database.

In contrast, delaying restore jobs, perhaps by several hours, can be useful in the event of a serious user error, such as a dropped table or inappropriately deleted table row. If the time of the error is known, you can move that secondary database forward to a time soon before the error. Then you can export the lost data and import it back into the primary database.

Alert Job

If a monitor server is used, an alert job is created on the monitor server instance. This alert job is shared by the primary and secondary databases of all log shipping configurations using this monitor server instance. Any change to the alert job (such as rescheduling, disabling, or enabling the job) affects all databases using that monitor server. This job raises alerts (for which you must specify alert numbers) for primary and secondary databases when backup and restore operations have not completed successfully within specified thresholds. You must configure these alerts to have an operator receive notification of the log shipping failure. The SQL Server Agent job category “Log Shipping Alert” is created on the monitor server instance when log shipping is enabled.

If a monitor server is not used, alert jobs are created locally on the primary server instance and each secondary server instance. The alert job on the primary server instance raises errors when backup operations have not completed successfully within a specified threshold. The alert job on the secondary server instance raises errors when local copy and restore operations have not completed successfully within a specified threshold.

The following figure shows a log shipping configuration with the primary server instance, three secondary server instances, and a monitor server instance. The figure illustrates the steps performed by backup, copy, and restore jobs, as follows:

  1. The primary server instance runs the backup job to back up the transaction log on the primary database. This server instance then places the log backup into a primary log-backup file, which it sends to the backup folder. In this figure, the backup folder is on a shared directory—the backup share.
  2. Each of the three secondary server instances runs its own copy job to copy the primary log-backup file to its own local destination folder.
  3. Each secondary server instance runs its own restore job to restore the log backup from the local destination folder onto the local secondary database.

The primary and secondary server instances send their own history and status to the monitor server instance.

Configuration showing backup, copy, & restore jobsTo enable log shipping

Posted in Uncategorized | Leave a comment

Table Partitioning – Các Khái Niệm Cơ Bản

Bài Table Partitioning Trong SQL Server giới thiệu về kỹ thuật phân đoạn của SQL Server và cũng giới thiệu qua các khái niệm xung quanh kỹ thuật này, như partition function, partition schema, filegroup. Bài viết này đề cập chi tiết hơn các khái niệm trên để giúp bạn hiểu thêm về kiến trúc partitioning của SQL Server.

Partition Function
Bước đầu tiên bạn cần làm khi thực hiện phân đoạn là tạo partition function để định nghĩa giá trị biên của các đoạn. Partition function không giống với các hàm UDF thông thường (bạn không thể gọi trực tiếp), mà chỉ dùng vào mục đích phân đoạn. Nó đưa ra định nghĩa về mặt logic, rằng mỗi đoạn chứa dải giá trị từ đâu đến đâu chứ không qui định các đoạn được lưu trữ như thế nào, cũng như tên trường dùng để phân đoạn là gì.
Ví dụ 1: phân đoạn dựa vào dãy số

CREATE DATABASE MyPartDB
GO
USE PartTest
GO
CREATE PARTITION FUNCTION MyPartFunc_1(INT) AS RANGE LEFT FOR VALUES (1000, 2000, 3000)
Partition Partition 1 Partition 2 Partition 3 Partition 4
Dải giá trị giá trị <= 1000 1000< giá trị <= 2000 2000 < giá trị <= 3000 3000 < giá trị

Ví dụ 2: phân đoạn theo năm

CREATE PARTITION FUNCTION MyPartFunc_2(DATETIME) AS RANGE RIGHT FOR VALUES ('2010-01-01', '2011-01-01')
Partition Partition 1 Partition 2 Partition 3
Dải giá trị giá trị < 01/01/2010 01/01/2010<= giá trị < 01/01/2011 01/01/2011 <= giá trị

Bạn có thể thấy ở ví dụ 1 chỉ có ba giá trị liệt kê trong hàm để tạo ra bốn đoạn. Tương tự ở ví dụ 2, chỉ có hai giá trị được dùng để tạo ra ba đoạn. Việc phân đoạn bảng giống như khi bạn cắt một sợi dây, trong đó partition function định nghĩa vị trí cho các nhát cắt, và để cắt sợi dây làm n đoạn bạn chỉ cần n-1 nhát cắt.
Trong hai ví dụ trên bạn có để ý đến từ khóa “LEFT” ở hàm MyPartFunc_1 và “RIGHT” ở hàm MyPartFunc_2? Mỗi partition function phải thuộc một trong hai kiểu, LEFT hoặc RIGHT. Như ở ví dụ trên, hàm MyPartFunc_1 có kiểu LEFT và hàm MyPartFunc_2 có kiểu RIGHT. Hai kiểu hàm này liên quan đến việc qui định giá trị biên thuộc về đoạn nào, đoạn bên trái hay bên phải. Với hàm kiểu LEFT, giá trị biên thuộc về đoạn phía trước nó (bên trái), như với hàm MyPartFunc_1 các giá trị biên 1000, 2000, và 3000 luôn thuộc về đoạn phía bên trái của chúng. Ngược lại với hàm kiểu RIGHT, các giá trị biên luôn thuộc về đoạn tiếp theo, tức là phía bên phải (hãy quan sát dải giá trị của các đoạn trong hàm MyPartFunc_2).
Việc phân chia làm hai loại hàm như vậy giúp bạn định nghĩa dải giá trị cho đúng với yêu cầu. Khi dải giá trị là các số rời rạc (discrete) như kiểu INT thì bạn chỉ cần một kiểu hàm và có thể chuyển đổi qua lại dễ dàng bằng cách tăng/giảm giá trị biên đi 1, do đó kiểu hàm không quá quan trọng. Như với ví dụ 1 ở trên, tôi có thể viết lại thành hàm kiểu RIGHT như sau:

CREATE PARTITION FUNCTION MyPartFunc_3(INT) AS RANGE RIGHT FOR VALUES (1001, 2001, 3001)

Tuy nhiên với kiểu dữ liệu có dải giá trị liền nhau, như DATETIME hay FLOAT, vấn đề trở nên phức tạp hơn và bạn cần chọn kiểu hàm cho đúng. Như ví dụ 2 ở trên, yêu cầu đặt ra là các giá trị thuộc về năm 2009 trở về trước vào một đoạn, và thời khắc đầu tiên của năm 2010 đã thuộc về đoạn sau, do đó dùng hàm RIGHT là thích hợp. Nếu dùng hàm LEFT bạn cần chuyển qua đại để thế này:

CREATE PARTITION FUNCTION MyPartFunc_4(DATETIME) AS RANGE LEFT FOR VALUES ('2009-12-31 23:59:59.999', '2010-12-31 23:59:59.999')

trong đó ’2009-12-31 23:59:59.999′ và ’2010-12-31 23:59:59.999′ tương ứng là thời điểm cuối cùng của năm 2009 và 2010 theo độ phân giải thời gian của hệ thống (các giá trị trên đây chỉ là ví dụ, tôi không rõ SQL Server chính xác đến bao nhiêu phần giây).

Partition Scheme
Sau khi định nghĩa dải giá trị cho các đoạn, việc tiếp theo là tạo partition scheme để định nghĩa không gian lưu trữ cho chúng. Partition scheme ánh xạ từng đoạn đã được định nghĩa trong partition function vào các filegroup (Các filegroup này cần được tạo trước khi tạo partition scheme):

CREATE PARTITION SCHEME MyPartScheme_1 AS PARTITION MyPartFunc_1 TO (FG1, FG2, FG3, FG4)
Partition Partition 1 Partition 2 Partition 3 Partition 4
Dải giá trị giá trị <= 1000 1000< giá trị <= 2000 2000 < giá trị <= 3000 3000 < giá trị
Filegroup FG1 FG2 FG3 FG4

Về nguyên tắc bạn có thể gán tất cả các đoạn vào một filegroup, nhưng làm như vậy là vô hiệu hóa nhiều lợi điểm của việc phân đoạn. Trên thực tế bạn nên giành riêng mỗi filegroup cho một đoạn.

Filegroup
Filegroup được đưa vào SQL Server đã lâu, từ trước khi có partitioning. Khái niệm filegroup tương tự như folder trong windows, chỉ là một tên logic để nhóm các data file chứ không bản thân nó chứa dữ liệu. Filegroup chứa một hoặc nhiều data file và các data file này mới thực chứa dữ liệu. Khi mới tạo database, một filegroup có tên PRIMARY tự động được tạo và chứa data file chính (.mdf). Tuy nhiên bạn không nên dùng PRIMARY filegroup vào việc phân đoạn mà nên tạo filegroup mới.

-- tạo filegroup
ALTER DATABASE MyPartDB ADD FILEGROUP FG1
ALTER DATABASE MyPartDB ADD FILEGROUP FG2
ALTER DATABASE MyPartDB ADD FILEGROUP FG3
ALTER DATABASE MyPartDB ADD FILEGROUP FG4

-- thêm data file vào mỗi filegroup

ALTER DATABASE MyPartDB ADD FILE (NAME = N'F1', FILENAME = N'D:\DATA\MyPartDB_F1.ndf') TO FILEGROUP FG1
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F2', FILENAME = N'D:\DATA\MyPartDB_F2.ndf') TO FILEGROUP FG2
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F3', FILENAME = N'D:\DATA\MyPartDB_F3.ndf') TO FILEGROUP FG3
ALTER DATABASE MyPartDB ADD FILE (NAME = N'F4', FILENAME = N'D:\DATA\MyPartDB_F4.ndf') TO FILEGROUP FG4

Phân đoạn bảng
Sau khi các bước trên hoàn tất, giờ bạn có thể tạo bảng và đồng thời phân đoạn nó:

CREATE TABLE dbo.MyTablePart(
MyID INT IDENTITY PRIMARY KEY,
MyData VARCHAR(100)
) ON MyPartScheme_1(MyID)

Trong ví dụ trên, việc phân đoạn được gói gọn ở mệnh đề “ON MyPartScheme_1(MyID)”, và bảng được phân đoạn thông qua partition scheme MyPartScheme_1 dựa vào trường MyID. Khi đó trường MyID được gọi là partition key. Nếu bạn đã quen với việc dùng mệnh đề “ON some_filegroup_name” khi tạo bảng để chỉ định filegroup cho nó, bạn sẽ thấy việc phân đoạn chỉ đơn giản là thay tên filegroup bằng một partition scheme. Nói cách khác là thay vì chỉ định lưu dữ liệu vào một filegroup cụ thể, thì nay vào một partition scheme, và thông qua đó dữ liệu được phân đoạn.

Một vài lưu ý
– Ở đây bạn có thể thấy một sự phân cấp rất rõ ràng:
Trong một database bạn có thể tạo nhiều partition function.
Mỗi partition function có thể được dùng cho nhiều partition scheme
Đến lượt mỗi partition scheme lại được dùng cho nhiều bảng.

– Một bạn hỏi qua email muốn phân đoạn dựa vào mã tỉnh thành, ví dụ Hà nội (HN) vào một đoạn, Sài gòn (SG) vào một đoạn, và các tỉnh thành khác vào một đoạn. Bạn không thể áp dụng trực tiếp partition function lên các giá trị này vì chúng không tạo ra một dải tăng tuần tự. Ví dụ Bình dương (BD) đứng trước Hà nội, còn Quảng ninh (QN) đứng sau Hà nội do đó không thể nằm chung trong một đoạn. Trong trường hợp này bạn cần tạo một ID cho mỗi tỉnh thành ở dạng số (ví dụ HN=1, SG=2, else=3,4,5…) và phân đoạn trên ID này.
– Việc phân đoạn chỉ có thể được thực hiện trên một dải giá trị duy nhất. Có những trường hợp bạn muốn phân đoạn dựa vào 2 hoặc nhiều trường, ví dụ phân đoạn hóa đơn bán hàng theo năm và trong mỗi năm tiếp tục phân chia các đơn hàng có trị giá hơn 1 triệu vào một đoạn và nhỏ hơn 1 triệu vào một đoạn. Khi đó bạn cần tạo một computed field đại diện cho hai trường kia và phân đoạn dựa vào computed field này.
Phiên bản áp dụng: SQL Server 2005 trở lên

Posted in Uncategorized | Leave a comment

Partitioning a SQL Server Database Table

Looking to optimize the performance of your SQL Server database? If your database contains very large tables, you may benefit from partitioning those tables onto separate filegroups. This technology, introduced in SQL Server 2005, allows you to spread data onto different physical disks, leveraging the concurrent performance of those disks to optimize query performance.

Partitioning a SQL Server database table is a three-step process:

  1. Create the partition function
  2. Create the partition scheme
  3. Partition the table

The remainder of this article explores each of those steps in further detail.

Step 1: Creating a Partition Function

The partition function defines [u]how[/u] you want SQL Server to partition the data. At this point, we’re not concerned with any particular table, we’re just generically defining a technique for splitting data.

We define the partitions by specifying the boundaries of each partition. For example, suppose we have a Customers table that contains information on all of our customers, identified by a unique customer number, ranging from 1 to 1,000,000. We might decide to partition that table into four equally spaced partitions, using the following partition function (I’ll call it customer_partfunc):

 CREATE PARTITION FUNCTION customer_partfunc (int) AS RANGE RIGHT FOR VALUES (250000, 500000, 750000)

These boundaries define four partitions. The first contains all values less than 250,000. The second contains values between 250,000 and 499,999. The third contains values between 500,000 and 749,999. All values greater than or equal to 750,000 go in the fourth partition.

Notice that I used the “RANGE RIGHT” clause in this example. This indicates that the boundary value itself should go in the partition on the right side. Alternatively, if I had used “RANGE LEFT”, the first partition would have included all values less than [u]or equal to[/u] 250,000; the second partition would have included values between 250,001 and 500,000, and so on.

Step 2: Creating a Partition Scheme

Once you have a partition function describing [u]how[/u] you want to split your data, you need to create a partition scheme defining [u]where[/u] you want to partition it. This is a straightforward process that links partitions to filegroups. For example, if I had four filegroups named “fg1” through “fg4”, the following partition scheme would do the trick:

 CREATE PARTITION SCHEME customer_partscheme AS PARTITION customer_partfunc TO (fg1, fg2, fg3, fg4) 

Notice that we now link a partition function to the partition scheme, but we still haven’t linked the partition scheme to any specific database table. That’s where the power of reusability comes into play. We could use this partition scheme (or just the partition function, if we desired) on any number of database tables.

Step 3: Partitioning a Table

After defining a partition scheme, you’re now ready to create a partitioned table. This is the simplest step of the process. You simply add an “ON” clause to the table creation statement specifying the partition scheme and the table column to which it applies. You don’t need to specify the partition function because the partition scheme already identifies it.

For example, if you wanted to create a customer table using our partition scheme, you would use the following Transact-SQL statement:

 CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int) ON customer_partscheme (CustomerNumber) 

That’s everything you need to know about partitioning tables in Microsoft SQL Server! Remember to leverage the power of reusability by writing generic partition functions and partition schemes that might apply to multiple tables!



Posted in Uncategorized | Leave a comment

Hello world!

Welcome to WordPress.com. After you read this, you should delete and write your own post, with a new title above. Or hit Add New on the left (of the admin dashboard) to start a fresh post.

Here are some suggestions for your first post.

  1. You can find new ideas for what to blog about by reading the Daily Post.
  2. Add PressThis to your browser. It creates a new blog post for you about any interesting  page you read on the web.
  3. Make some changes to this page, and then hit preview on the right. You can alway preview any post or edit you before you share it to the world.
Posted in Uncategorized | 1 Comment