Table Partitioning

* It is a new feature introduced in SQL Server 2005.
* While working with large tables, generally we face the following problems
             * Less performance.
             * Pressure increases on the hardware where the table is created.
             * Index size also increases.
             * We have to rebuild or reorganize the complete index always.

             * To overcome all these problems we can divide the table horizontally into more than on  part.       

                This process is called table partitioning.
* Dividing the table into segments horizontally is table partitioning.
* Each part is called partition. All the partitions consists of same structure.
* Each partition can be stored in different files/filegroups.
* One of a performance improvement technique.
* We can split the table into parts depending on some column values.

Steps

1) Define a partition function
 * Here we will mention the ranges of partitions.
 syn:
 CREATE PARTITION FUNCTION <function_Name> (data type)
 AS RANGE LEFT/RIGHT FOR VALUES (V1,V2,....)  

 * Here, data type is the column's type which is used to implement partitions.
  i.e. partition key data type.

 LEFT:
 Specifies that partition values will be less than or equal to the values defined in the Partition Function.

 RIGHT:
 Specifies that partition values will be less than the values defined in the Partition Function.

 EX:

 CREATE PARTITION FUNCTION MyPartitionRange (INT)
 AS RANGE LEFT FOR VALUES (1,2)  

 This functions allows to create 3 partitions.
 First partition consists of values  <=1
 Sec        >1 and <=2
 Third        >2

2) Define a partition scheme from the partition function

 * It defines the locations for partitions. i.e. which partition should be maintain in which file group.
 * We can maintain each partition in a seperate filegroup or all partitions in a single file group.

 syn:

 CREATE PARTITION SCHEME <name> AS
 PARTITION <partition_functionname>
 ALL TO (fg1,fg2,.........)

3) Create the partition Table

 CREATE TABLE <tname>
        (
        colname type,
               PartCol type
        )
 ON
 <partition_schemename> (PartCol)
EX:
Step1: Creating partition Function.

CREATE PARTITION FUNCTION MyPartitionRange (INT)
AS RANGE LEFT FOR VALUES (1,2)  

Step2: Creating partition scheme.

CREATE PARTITION SCHEME MyPartitionScheme AS
PARTITION MyPartitionRange
ALL TO ([PRIMARY])

Step3: Creating partition table

CREATE TABLE MyPartitionedTable
       (
       i INT ,
       s CHAR(8000) ,
       PartCol INT
       )
 ON
 MyPartitionScheme (PartCol)

Step4: To display partition details

SELECT * FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

Step5: Inserting sample rows

INSERT MyPartitionedTable (i, s, PartCol) SELECT 1, 'a', 1
INSERT MyPartitionedTable (i, s, PartCol) SELECT 2, 'a', 2
INSERT MyPartitionedTable (i, s, PartCol) SELECT 3, 'a', 2
INSERT MyPartitionedTable (i, s, PartCol) SELECT 4, 'a', 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 5, 'a', 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 6, 'a', 3
INSERT MyPartitionedTable (i, s, PartCol) SELECT 7, 'a', 4

Step6: Verifying the partitions

SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

Step7: To find the given value falls in which partition

SELECT PartitionNo = $partition.MyPartitionRange(6)

What Operations Can Be Performed on Partitioned Data
---------------------------------------------------------------------
1. SPLIT: Insert a boundary in an existing partition to create a new partition.

 * If the size of a partition becomes very large then we can split it into two           partitions.
 syn:
 ALTER PARTITION FUNCTION <function_name>() split RANGE (value)
 Ex:
 ALTER PARTITION FUNCTION MyPartitionRange () split RANGE (3)

 select *
 FROM sys.partitions
 WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

2. SWITCH: Swap a populated table or partition with an empty table or partition.
--------
Step1:

select * into MyNewPartition from MyPartitionedTable where 1=3

--copying rows present in 3rd partition into MyNewPartition

ALTER TABLE MyPartitionedTable switch PARTITION 3 TO MyNewPartition

--STEP3:
select * from MyNewPartition

--step4: Now 3rd partition becomes empty.

SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID('MyPartitionedTable')

--Copying Rows present in a table into required partion

Step1:

select * into MyNewPartition from MyPartitionedTable where 1=3

Step2: Copy rows present in non-partitioned table into 4th partition

ALTER TABLE MyNewPartition switch TO MyPartitionedTable PARTITION 4

Note1:
 * MyNewPartition should consists of check constraint which allows the respective       partition range.
 * It should be there on same filegroup where the respective partition is present.

select * from mynewpartition

ALTER TABLE OrdersOctober2004 SWITCH TO Orders PARTITION 24
GO

3. MERGE: Combine two adjacent partitions into a single partition.
---------------------------------------------------------------

 * If the no of rows in the partitions are less then if we want then we can merge   into single partition.

CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO

ALTER PARTITION FUNCTION myRangePF1 ()
MERGE RANGE (100);

Database Mirroring

Requirements

1.       SQL Server 2005 with SP1 or SQL Server 2008

2.       Database should be in FULL recovery model.

3.       Service Broker should be enabled on the database.

4.       Both the servers should have either Enterprise or standard editions.

5.       Both the servers should have same edition.

6.       Witness server can have any edition.

Configuring Mirroring – Steps

1.       Configuring security and communication between instances

a.       Configuring endpoint

b.      Creating logins for other servers service accounts

c.       Grant connect permission to this logins on endpoints

2.       Create mirror database

a.       Take full backup from principle server and restore it in mirror server with NORECOVERY.

3.       Establish mirroring session using ALTER DATABASE command

Steps

1.       Go to SSMS

2.       Connect 2 or 3 instances

For example

                CLASS2\sql2K8                  -              Principal

                CLASS2\FIRST                    -              Mirror

                CLASS2\THIRD                   -              Witness

3.       Note down the above instances service accounts

a.       CLASS2\SQL2K8                                (CLASS2\KAREEM)

b.      CLASS2\FIRST                                    (CLASS2\KAREEM)

c.       CLASS2\THIRD                                   (CLASS2\SQLUSER)

4.       Verify both Principal and Mirror has same editions or not i.e.  Enterprise or Standard.

By running the following command in both the servers

selectserverproperty('edition')

5.       Go to Principal server and create a sample database (In real time environment we have to use existing database) with the name OptimizeSQL

6.       Create one sample table in the database with some rows.

7.       Take FULL and Transaction Log Backup of OptimizeSQL database in principal server.

usemaster

go

backup database OptimizeSQL to disk='\\Class2\backups\OptimizeSQL.bak'

go

backup log OptimizeSQL to disk='\\Class2\backups\OptimizeSQL.bak'

go

8.       Go to Mirror Server and restore database by using the Recovery State WITH NORECOVERY

RESTOREDATABASE OptimizeSQL

FROMDISK='\\Class2\backups\OptimizeSQL.bak'

WITH FILE= 1,

MOVE'OptimizeSQL'

TO'C:\Program Files\Microsoft SQL Server\MSSQL10.FIRST\MSSQL\DATA\OptimizeSQL.mdf',

MOVE'OptimizeSQL_log'

TO'C:\Program Files\Microsoft SQL Server\MSSQL10.FIRST\MSSQL\DATA\OptimizeSQL_1.ldf',NORECOVERY

GO

RESTORE LOG OptimizeSQL

FROM DISK=N'\\Class2\backups\OptimizeSQL.bak'

WITH FILE= 2,NORECOVERY

GO

9.       Configuring Mirroring –

10.   Go to Principal Server à Right Click on database OptimizeSQLà Tasks à Mirror

11.   Click on Configure Securityà Click Next

12.   Select Yes if you have witness instance otherwise select No.

13.   Next àNext

14.   Select principal instance à Next

15.   Click on Connect select Mirror Server instance name

16.   Select Connect à Next

17.   Once again click on Connect button select Witness Server instance name (CLASS2\THIRD) à Next

18.   Enter service accounts

19.   Click Next à Finish

20.   Close.

21.   Select “Do Not Start Mirroring”.

22.   Select Start Mirroring

23.   Check the status à OK

FAQ: - While configuring Mirroring what errors you have faced?

Answer:

1.       Error No: 1418

Solution:

Before starting Mirroring check that logins are created for the respective instance service accounts. If they are not created create manually and grant connect permission on Endpoint, then start mirroring again.

Points to Remember

1.       One job is created on both the servers 

Database Mirroring Monitor Job

2.       Default Partner Timeout is 10Sec.

3.      How can you say that both the dbs are 100% sync?

a.      We can view unsent log and unrestored log values. If both are 0 then 100% sync. (In Mirroring Monitor)

b.       We can view Mirroring Failover LSN and Replication LSN with sys.database_mirroring. Both should be same.

4. Mirroring states

1.      Synchronizing

2.      Synchronized

3.      Disconnected (If mirror or principal failed)

4.      Suspended (If the principal is un available or unable to send transactions to mirror)

5.      Pending Failover – If the unsent log is >0.

5. To change mirroring timeout (Run in principal server)

            alterdatabase OptimizeSQL SETPARTNER TIMEOUT 30

Monitoring Mirroring

·        We can monitor mirroring using the following options

o   Using MSDB tables and Views

o   Using Database Mirroring Monitor

o   Using Performance Monitor

o   Using Profiler

1.      Using MSDB tables and Views

o   To view complete details of mirroring

select*from sys.database_mirroring

o   To view mirroring endpoint details

Select * from sys.database_mirroring_endpoints

o    To view about Principal, mirror server details and mirroring state run the following query in witness server

Select * from sys.database_mirroring_witnesses

FAQ: - What are the major new features introduced in Mirroring 2008 version?

1.       Auto Page Repair.

select*fromsys.dm_db_mirroring_auto_page_repair

2.       Transactions are sending to Mirror by compressing.

·         To view total bytes send from principal and total bytes received at mirror we can use (run in witness server)

                        select*from sys.dm_db_mirroring_connections              

2.      Using Database Mirroring Monitor

o   We can monitor the following features

                                                * Unsent Log     (at principal)

                                                * Unrestored Log(at mirror)

                                                * Transaction Rate

                                                * Commit Overhead (Transactions applied rate at mirror)                         

Ex:  Go to principal server and run the following query

                                use OptimizeSQL

go

declare @n int=100

while @n<=1000000

begin

                insert emp values(@n,'Rajesh',60)

                set @n+=1

end 

                b. Right click on OptimizeSQLdb --> Tasks -->Launch Database Mirroring Monitor

                                c. Select "Database Mirroring Monitor"

                                d. Click on Register Mirror databases

                                e. Click on Connect and select Mirror Server

                                f. Select the database OptimizeSQL --> OK

                                g. Observe the parameters by refreshing (F5) the monitor.

Configuring Thresholds

·         Go to Mirroring Monitor à Select “Warnings” tab à Set Thresholds à

3.      Using Performance Monitor

a.      We can monitor the following counters for Mirrored databases

* We can use the performance object called

                                    "<instanceName>: Database Mirroring"

                        * Counters which we have to observe regularly

                                    * Bytes sends/sec

                                    * Log Harden time (Commit overhead)

                                    * Total sends/sec

                                    * Transaction delay (Principal)

                                    * Pages Sends/sec

Steps

1.      Start à run à perfmon

2.      Add counter (Ctrl + I) or Click on +symbol, present on toolbar.

3.      Add required counters by selectingMSSQL$SQL2K8:DatabaseMirroringperformance object

4.       To view the changes run the previous script.

Performing Fail Over

 1.       Fail over process depends on Operating Modes.

2.       If the operating mode is "High safety with automatic failover" then witness server makes mirror db online automatically within the configured timeout.

3.       In case of other operating modes we have to perform fail over manually.

In High Performance

1.      Run the following command in mirror server

ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

2.      Transfer the logins.

3.      Make the database available to the users and applications.

In High Protection

1.      Run the following commands in Mirror server

ALTER DATABASE <dbname> SET PARTNER OFF;  (To break mirroring)

2.      Database comes into restoring state run the following command to take it online

RESTORE DATABASE <dbname> WITH RECOVERY

Threads created for database mirroring

The kinds of threads that a server instance creates for a database mirroring session depend partly on the mirroring roles that the server instance is performing. A given session has some or all of the following threads:

·         One global thread for database mirroring communications. This thread is started by Service Broker.

·         If the server instance is acting as a mirroring partner (whether it is the principal server or mirror server):

·         One thread per mirrored database for event processing.

·         One thread per mirrored database for asynchronous tasks (such as log send or log write) that would otherwise block the event thread.

·         Whenever the instance is acting as a mirror server:

·         One redo manager thread, which submits log for redo, performs page read-ahead, lock reacquisition, and so on.

·         In SQL Server Standard, one redo thread per mirror database, or in SQL Server Enterprise, one redo thread per mirror database for every four CPUs. These threads perform the actual log redo.

·         If the instance is acting as a witness:

·         One global thread for processing the witness messages for all mirroring sessions in which the instance is acting as the witness.

Impact of Pausing a Session on the Principal Transaction Log

At any time, the database owner can pause a session. Pausing preserves the session state while removing mirroring. When a session is paused, the principal server does not send any new log records to the mirror server. All of these records remain active and accumulate in the transaction log of the principal database. As long as a database mirroring session remains paused, the transaction log cannot be truncated. Therefore, if the database mirroring session is paused for too long, the log can fill up.

Quorum

Quorum is a relationship that exists when two or more server instances in a database mirroring session are connected to each other. Typically, quorum involves three interconnected server instances. When a witness is set, quorum is required to make the database available. Designed for high-safety mode with automatic failover, quorum makes sure that a database is owned by only one partner at a time.

Three types of quorum are possible:

·         A full quorum includes both partners and the witness.

·         A witness-to-partner quorum consists of the witness and either partner.

·         A partner-to-partner quorum consists of the two partners.

Possible Failures during Database Mirroring

·         As part of mirroring generally we have two types of errors

·         Soft errors

·         Hard Errors

·         Soft Errors

·         Errors identified by SQL Server service i.e. sqlservr.exe is called soft error.

• Network errors such as TCP link time-outs, dropped or corrupted packets, or packets that are in an incorrect order.
• A hanging operating system, server, or database state.
• A Windows server timing out.

·         Hard Errors

·         Errors identified by windows and notified to sqlservr.exe file are called hard errors.

• A broken connection or wire
• A bad network card
• A router change
• Changes in the firewall
• Endpoint reconfiguration
• Loss of the drive where the transaction log resides
• Operating system or process failure

Manual Fail Over Error

Could not write a checkpoint record in database ID 8 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.

Msg 3167, Level 16, State 3, Line 1

RESTORE could not start database 'Hospital'.

Msg 3167, Level 16, State 1, Line 1

RESTORE could not start database 'Hospital'.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Msg 9002, Level 17, State 2, Line 1

The transaction log for database 'Hospital' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases