RSS

Monthly Archives: December 2011

Identifying Which Databases Have Index Fragmentation Problems

If Not Exists(Select [object_id] From sys.tables Where name = N’dba_indexDefragLog’)
Begin
— Drop Table dbo.dba_indexDefragLog
Create Table dbo.dba_indexDefragLog
(
indexDefrag_id    int identity(1,1)   Not Null
, databaseID        int                 Not Null
, databaseName      nvarchar(128)       Not Null
, objectID          int                 Not Null
, objectName        nvarchar(128)       Not Null
, indexID           int                 Not Null
, indexName         nvarchar(128)       Not Null
, partitionNumber   smallint            Not Null
, fragmentation     float               Not Null
, page_count        int                 Not Null
, dateTimeStart     datetime            Not Null
, durationSeconds   int                 Not Null
Constraint PK_indexDefragLog Primary Key Clustered (indexDefrag_id)
)

Print ‘dba_indexDefragLog Table Created’;
End

If ObjectProperty(Object_ID(‘dbo.dba_indexDefrag_sp’), N’IsProcedure’) = 1
Begin
Drop Procedure dbo.dba_indexDefrag_sp;
Print ‘Procedure dba_indexDefrag_sp dropped’;
End;
Go
CREATE PROCEDURE [dbo].[dba_indexDefrag_sp]

/* Declare Parameters */
@minFragmentation     FLOAT           = 5.0
/* in percent, will not defrag if fragmentation less than specified */
, @rebuildThreshold     FLOAT           = 30.0
/* in percent, greater than @rebuildThreshold will result in rebuild instead of reorg */
, @executeSQL           BIT             = 1
/* 1 = execute; 0 = print command only */
, @DATABASE             VARCHAR(128)    = Null
/* Option to specify a database name; null will return all */
, @tableName            VARCHAR(4000)   = Null  — databaseName.schema.tableName
/* Option to specify a table name; null will return all */
, @onlineRebuild        BIT             = 1
/* 1 = online rebuild; 0 = offline rebuild; only in Enterprise */
, @maxDopRestriction    TINYINT         = Null
/* Option to restrict the number of processors for the operation; only in Enterprise */
, @printCommands        BIT             = 0
/* 1 = print commands; 0 = do not print commands */
, @printFragmentation   BIT             = 0
/* 1 = print fragmentation prior to defrag;
0 = do not print */
, @defragDelay          CHAR(8)         = ’00:00:05′
/* time to wait between defrag commands */
, @scanMode             NVARCHAR(8)     = N’Limited’
/* scan level to be used with dm_db_index_physical_stats. Options are DEFAULT, NULL, LIMITED, SAMPLED, or DETAILED. The default (NULL) is LIMITED */
, @debugMode            BIT             = 0
/* display some useful comments to help determine if/where issues occur */
AS
/*********************************************************************************
Name:       dba_indexDefrag_sp

Author:     Michelle Ufford, http://sqlfool.com

Purpose:    Defrags all indexes for one or more databases

Notes:

CAUTION: TRANSACTION LOG SIZE MUST BE MONITORED CLOSELY WHEN DEFRAGMENTING.

@minFragmentation     defaulted to 10%, will not defrag if fragmentation
is less than that

@rebuildThreshold     defaulted to 30% as recommended by Microsoft in BOL;
greater than 30% will result in rebuild instead

@executeSQL           1 = execute the SQL generated by this proc;
0 = print command only

@database             Optional, specify specific database name to defrag;
If not specified, all non-system databases will
be defragged.

@tableName            Specify if you only want to defrag indexes for a
specific table, format = databaseName.schema.tableName;
if not specified, all tables will be defragged.

@onlineRebuild        1 = online rebuild;
0 = offline rebuild

@maxDopRestriction    Option to specify a processor limit for index rebuilds

@printCommands        1 = print commands to screen;
0 = do not print commands

@printFragmentation   1 = print fragmentation to screen;
0 = do not print fragmentation

@defragDelay          time to wait between defrag commands; gives the
server a little time to catch up

@scanMode             scan level to be used with dm_db_index_physical_stats.
Options are DEFAULT, NULL, LIMITED, SAMPLED, or
DETAILED. The default (NULL) is LIMITED

@debugMode            1 = display debug comments; helps with troubleshooting
0 = do not display debug comments

Called by:  SQL Agent Job or DBA

Date        Initials Description
—————————————————————————-
2008-10-27  MFU         Initial Release for public consumption
2008-11-17  MFU         Added page-count to log table
, added @printFragmentation option
2009-03-17  MFU         Provided support for centralized execution,
, consolidated Enterprise & Standard versions
, added @debugMode, @maxDopRestriction
, modified LOB and partition logic
2009-05-12  JAP         Added @scanMode
*********************************************************************************
Exec dbo.dba_indexDefrag_sp
@executeSQL           = 0
, @minFragmentation     = 80
, @printCommands        = 1
, @debugMode            = 1
, @printFragmentation   = 1
, @database             = ‘AdventureWorks’
, @tableName            = ‘AdventureWorks.Sales.SalesOrderDetail’;
*********************************************************************************/

SET NOCOUNT ON;
SET XACT_Abort ON;
SET Quoted_Identifier ON;

BEGIN

IF @debugMode = 1 RAISERROR(‘Dusting off the spiderwebs and starting up…’, 0, 42) WITH NoWait;

/* Declare our variables */
DECLARE   @objectID             INT
, @databaseID           INT
, @databaseName         NVARCHAR(128)
, @indexID              INT
, @partitionCount       BIGINT
, @schemaName           NVARCHAR(128)
, @objectName           NVARCHAR(128)
, @indexName            NVARCHAR(128)
, @partitionNumber      SMALLINT
, @partitions           SMALLINT
, @fragmentation        FLOAT
, @pageCount            INT
, @sqlCommand           NVARCHAR(4000)
, @rebuildCommand       NVARCHAR(200)
, @dateTimeStart        DATETIME
, @dateTimeEnd          DATETIME
, @containsLOB          BIT
, @editionCheck         BIT
, @debugMessage         VARCHAR(128)
, @updateSQL            NVARCHAR(4000)
, @partitionSQL         NVARCHAR(4000)
, @partitionSQL_Param   NVARCHAR(1000)
, @LOB_SQL              NVARCHAR(4000)
, @LOB_SQL_Param        NVARCHAR(1000);

/* Create our temporary tables */
CREATE TABLE #indexDefragList
(
databaseID        INT
, databaseName      NVARCHAR(128)
, objectID          INT
, indexID           INT
, partitionNumber   SMALLINT
, fragmentation     FLOAT
, page_count        INT
, defragStatus      BIT
, schemaName        NVARCHAR(128)   Null
, objectName        NVARCHAR(128)   Null
, indexName         NVARCHAR(128)   Null
);

CREATE TABLE #databaseList
(
databaseID        INT
, databaseName      VARCHAR(128)
);

CREATE TABLE #processor
(
[INDEX]           INT
, Name              VARCHAR(128)
, Internal_Value    INT
, Character_Value   INT
);

IF @debugMode = 1 RAISERROR(‘Beginning validation…’, 0, 42) WITH NoWait;

/* Just a little validation… */
IF @minFragmentation Not Between 0.00 And 100.0
SET @minFragmentation = 5.0;

IF @rebuildThreshold Not Between 0.00 And 100.0
SET @rebuildThreshold = 30.0;

IF @defragDelay Not Like ’00:[0-5][0-9]:[0-5][0-9]’
SET @defragDelay = ’00:00:05′;

/* Make sure we’re not exceeding the number of processors we have available */
INSERT INTO #processor
EXECUTE XP_MSVER ‘ProcessorCount’;

IF @maxDopRestriction IS Not Null And @maxDopRestriction > (SELECT Internal_Value FROM #processor)
SELECT @maxDopRestriction = Internal_Value
FROM #processor;

/* Check our server version; 1804890536 = Enterprise, 610778273 = Enterprise Evaluation, -2117995310 = Developer */
IF (SELECT SERVERPROPERTY(‘EditionID’)) In (1804890536, 610778273, -2117995310)
SET @editionCheck = 1 — supports online rebuilds
ELSE
SET @editionCheck = 0; — does not support online rebuilds

IF @debugMode = 1 RAISERROR(‘Grabbing a list of our databases…’, 0, 42) WITH NoWait;

/* Retrieve the list of databases to investigate */
INSERT INTO #databaseList
SELECT database_id
, name
FROM sys.databases
WHERE name = IsNull(@DATABASE, name)
And database_id > 4 — exclude system databases
And [STATE] = 0; — state must be ONLINE

IF @debugMode = 1 RAISERROR(‘Looping through our list of databases and checking for fragmentation…’, 0, 42) WITH NoWait;

/* Loop through our list of databases */
WHILE (SELECT COUNT(*) FROM #databaseList) > 0
BEGIN

SELECT TOP 1 @databaseID = databaseID
FROM #databaseList;

SELECT @debugMessage = ‘  working on ‘ + DB_NAME(@databaseID) + ‘…’;

IF @debugMode = 1
RAISERROR(@debugMessage, 0, 42) WITH NoWait;

/* Determine which indexes to defrag using our user-defined parameters */
INSERT INTO #indexDefragList
SELECT
database_id AS databaseID
, QUOTENAME(DB_NAME(database_id)) AS ‘databaseName’
, [OBJECT_ID] AS objectID
, index_id AS indexID
, partition_number AS partitionNumber
, avg_fragmentation_in_percent AS fragmentation
, page_count
, 0 AS ‘defragStatus’ /* 0 = unprocessed, 1 = processed */
, Null AS ‘schemaName’
, Null AS ‘objectName’
, Null AS ‘indexName’
FROM sys.dm_db_index_physical_stats (@databaseID, OBJECT_ID(@tableName), Null , Null, @scanMode)
WHERE avg_fragmentation_in_percent >= @minFragmentation
And index_id > 0 — ignore heaps
And page_count > 8 — ignore objects with less than 1 extent
OPTION (MaxDop 1);

DELETE FROM #databaseList
WHERE databaseID = @databaseID;

END

CREATE CLUSTERED INDEX CIX_temp_indexDefragList
ON #indexDefragList(databaseID, objectID, indexID, partitionNumber);

SELECT @debugMessage = ‘Looping through our list… there”s ‘ + CAST(COUNT(*) AS VARCHAR(10)) + ‘ indexes to defrag!’
FROM #indexDefragList;

IF @debugMode = 1 RAISERROR(@debugMessage, 0, 42) WITH NoWait;

/* Begin our loop for defragging */
WHILE (SELECT COUNT(*) FROM #indexDefragList WHERE defragStatus = 0) > 0
BEGIN

IF @debugMode = 1 RAISERROR(‘  Picking an index to beat into shape…’, 0, 42) WITH NoWait;

/* Grab the most fragmented index first to defrag */
SELECT TOP 1
@objectID         = objectID
, @indexID          = indexID
, @databaseID       = databaseID
, @databaseName     = databaseName
, @fragmentation    = fragmentation
, @partitionNumber  = partitionNumber
, @pageCount        = page_count
FROM #indexDefragList
WHERE defragStatus = 0
ORDER BY fragmentation DESC;

IF @debugMode = 1 RAISERROR(‘  Looking up the specifics for our index…’, 0, 42) WITH NoWait;

/* Look up index information */
SELECT @updateSQL = N’Update idl
Set schemaName = QuoteName(s.name)
, objectName = QuoteName(o.name)
, indexName = QuoteName(i.name)
From #indexDefragList As idl
Inner Join ‘ + @databaseName + ‘.sys.objects As o
On idl.objectID = o.object_id
Inner Join ‘ + @databaseName + ‘.sys.indexes As i
On o.object_id = i.object_id
Inner Join ‘ + @databaseName + ‘.sys.schemas As s
On o.schema_id = s.schema_id
Where o.object_id = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
And i.index_id = ‘ + CAST(@indexID AS VARCHAR(10)) + ‘
And i.type > 0
And idl.databaseID = ‘ + CAST(@databaseID AS VARCHAR(10));

EXECUTE SP_EXECUTESQL @updateSQL;

/* Grab our object names */
SELECT @objectName  = objectName
, @schemaName   = schemaName
, @indexName    = indexName
FROM #indexDefragList
WHERE objectID = @objectID
And indexID = @indexID
And databaseID = @databaseID;

IF @debugMode = 1 RAISERROR(‘  Grabbing the partition count…’, 0, 42) WITH NoWait;

/* Determine if the index is partitioned */
SELECT @partitionSQL = ‘Select @partitionCount_OUT = Count(*)
From ‘ + @databaseName + ‘.sys.partitions
Where object_id = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
And index_id = ‘ + CAST(@indexID AS VARCHAR(10)) + ‘;’
, @partitionSQL_Param = ‘@partitionCount_OUT int OutPut’;

EXECUTE SP_EXECUTESQL @partitionSQL, @partitionSQL_Param, @partitionCount_OUT = @partitionCount OUTPUT;

IF @debugMode = 1 RAISERROR(‘  Seeing if there”s any LOBs to be handled…’, 0, 42) WITH NoWait;

/* Determine if the table contains LOBs */
SELECT @LOB_SQL = ‘ Select Top 1 @containsLOB_OUT = column_id
From ‘ + @databaseName + ‘.sys.columns With (NoLock)
Where [object_id] = ‘ + CAST(@objectID AS VARCHAR(10)) + ‘
And (system_type_id In (34, 35, 99)
Or max_length = -1);’
/*  system_type_id –> 34 = image, 35 = text, 99 = ntext
max_length = -1 –> varbinary(max), varchar(max), nvarchar(max), xml */
, @LOB_SQL_Param = ‘@containsLOB_OUT int OutPut’;

EXECUTE SP_EXECUTESQL @LOB_SQL, @LOB_SQL_Param, @containsLOB_OUT = @containsLOB OUTPUT;

IF @debugMode = 1 RAISERROR(‘  Building our SQL statements…’, 0, 42) WITH NoWait;

/* If there’s not a lot of fragmentation, or if we have a LOB, we should reorganize */
IF @fragmentation < @rebuildThreshold Or @containsLOB = 1 Or @partitionCount > 1
BEGIN

SET @sqlCommand = N’Alter Index ‘ + @indexName + N’ On ‘ + @databaseName + N’.’
+ @schemaName + N’.’ + @objectName + N’ ReOrganize’;

/* If our index is partitioned, we should always reorganize */
IF @partitionCount > 1
SET @sqlCommand = @sqlCommand + N’ Partition = ‘
+ CAST(@partitionNumber AS NVARCHAR(10));

END;

/* If the index is heavily fragmented and doesn’t contain any partitions or LOB’s, rebuild it */
IF @fragmentation >= @rebuildThreshold
BEGIN

/* Set online rebuild options; requires Enterprise Edition */
IF @onlineRebuild = 1 And @editionCheck = 1
SET @rebuildCommand = N’ Rebuild With (Online = On’;
ELSE
SET @rebuildCommand = N’ Rebuild With (Online = Off’;

/* Set processor restriction options; requires Enterprise Edition */
IF @maxDopRestriction IS Not Null And @editionCheck = 1
SET @rebuildCommand = @rebuildCommand + N’, MaxDop = ‘ + CAST(@maxDopRestriction AS VARCHAR(2)) + N’)’;
ELSE
SET @rebuildCommand = @rebuildCommand + N’)’;

SET @sqlCommand = N’Alter Index ‘ + @indexName + N’ On ‘ + @databaseName + N’.’
+ @schemaName + N’.’ + @objectName + @rebuildCommand;

END;

/* Are we executing the SQL?  If so, do it */
IF @executeSQL = 1
BEGIN

IF @debugMode = 1 RAISERROR(‘  Executing SQL statements…’, 0, 42) WITH NoWait;

/* Grab the time for logging purposes */
SET @dateTimeStart  = GETDATE();
EXECUTE SP_EXECUTESQL @sqlCommand;
SET @dateTimeEnd  = GETDATE();

/* Log our actions */
INSERT INTO dbo.dba_indexDefragLog
(
databaseID
, databaseName
, objectID
, objectName
, indexID
, indexName
, partitionNumber
, fragmentation
, page_count
, dateTimeStart
, durationSeconds
)
SELECT
@databaseID
, @databaseName
, @objectID
, @objectName
, @indexID
, @indexName
, @partitionNumber
, @fragmentation
, @pageCount
, @dateTimeStart
, DATEDIFF(SECOND, @dateTimeStart, @dateTimeEnd);

/* Just a little breather for the server */
WAITFOR Delay @defragDelay;

/* Print if specified to do so */
IF @printCommands = 1
PRINT N’Executed: ‘ + @sqlCommand;
END
ELSE
/* Looks like we’re not executing, just printing the commands */
BEGIN
IF @debugMode = 1 RAISERROR(‘  Printing SQL statements…’, 0, 42) WITH NoWait;

IF @printCommands = 1 PRINT IsNull(@sqlCommand, ‘error!’);
END

IF @debugMode = 1 RAISERROR(‘  Updating our index defrag status…’, 0, 42) WITH NoWait;

/* Update our index defrag list so we know we’ve finished with that index */
UPDATE #indexDefragList
SET defragStatus = 1
WHERE databaseID       = @databaseID
And objectID         = @objectID
And indexID          = @indexID
And partitionNumber  = @partitionNumber;

END

/* Do we want to output our fragmentation results? */
IF @printFragmentation = 1
BEGIN

IF @debugMode = 1 RAISERROR(‘  Displaying fragmentation results…’, 0, 42) WITH NoWait;

SELECT databaseID
, databaseName
, objectID
, objectName
, indexID
, indexName
, fragmentation
, page_count
FROM #indexDefragList;

END;

/* When everything is said and done, make sure to get rid of our temp table */
DROP TABLE #indexDefragList;
DROP TABLE #databaseList;
DROP TABLE #processor;

IF @debugMode = 1 RAISERROR(‘DONE!  Thank you for taking care of your indexes! &nbsp;:)’, 0, 42) WITH NoWait;

SET NOCOUNT OFF;
RETURN 0
END

GO

——————————————————————-

Exec

dbo.dba_indexDefrag_sp

@executeSQL = 0, @minFragmentation = 80

, @printCommands = 1, @debugMode = 1, @printFragmentation = 1, @database =NULL, @tableName =NULL;

 
Leave a comment

Posted by on December 28, 2011 in Index

 

How to find a table when you don’t know this table in any database?

DECLARE @table sysname, @SQL NVARCHAR(MAX)
SET @table = ‘Table_Name’ —– Replace Table_Name by your table name you want to find it
SET @SQL = ”
SELECT @SQL = @SQL + ‘;
IF EXISTS (SELECT 1 from ‘ + QUOTENAME(name) + ‘.INFORMATION_SCHEMA.Tables WHERE Table_Name = @table
AND TABLE_TYPE = ”BASE TABLE”)

PRINT ”Table ‘ + @table + ‘ found in ‘ + name + ””

FROM sys.databases

EXECUTE sp_executeSQL @SQL, N’@table sysname’, @table

 
 

Convert Row To Column

We can Convert row to column by Twoways (Select Case – Pivot) let’s see How can i make this

Way No One

—Create Table

create Table States
(
id int primary key identity,
City nvarchar (20),
PersonName nvarchar (20)
)

—-Insert Data

insert into States values (‘Egypt’,’MOstafa’)
insert into States values (‘Egypt’,’Sayed’)
insert into States values (‘Egypt’,’Refay’)
insert into States values (‘London’,’Jon’)
insert into States values (‘London’,’Better’)
insert into States values (‘London’,’Cristian’)

—–Convert row to column

SELECT dbo.States.id,
(CASE City WHEN ‘Egypt’ THEN
(PersonName )  ELSE ‘No Name’ END) AS Egypt
,(CASE City WHEN ‘London’ THEN
(PersonName)ELSE ‘No Name’END) AS London
from States

Way No Two

—Create Table

CREATE TABLE [dbo].[Visit_Count_City](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[city] [nvarchar](50) NULL,
[VisitCount] [bigint] NULL
)

—–Insert Data

INSERT INTO visit_count_city VALUES (‘Mostafa’,’Sudia Arabia’,2)
INSERT INTO visit_count_city VALUES (‘Mostafa’,’Egypt’,2)
INSERT INTO visit_count_city VALUES (‘Mostafa’,’India’,2)
INSERT INTO visit_count_city VALUES (‘syaed’,’Sudia Arabia’,2)
INSERT INTO visit_count_city VALUES (‘Ahmed’,’Quter’,2)
INSERT INTO visit_count_city VALUES (‘Ali’,’Egypt’,2)
INSERT INTO visit_count_city VALUES (‘Mohamed’,’India’,2)

—-Convert row to column

SELECT * FROM
( SELECT p.NAME,p.city,p.VisitCount FROM dbo.Visit_Count_City AS p)datatable
PIVOT
(
SUM (VisitCount)
FOR city IN
(Egypt,[Sudia Arabia],Quter,India))PivotTable
go

 
Leave a comment

Posted by on December 26, 2011 in Script

 

Mirroring SQL Server Part 3

in this part we will learn how to make resume and pasue to the mirroring session how to check the status to your database mirroring then how to make remove to the Mirroring Session

Suspend\Resume Database Mirroring Session

  • Why we need to Pass [suspend] database mirroring session ?
  1. For rolling upgrade [if I want to upgrade my mirroring server must be pass mirroring then upgrade then resume it again]
  2. Fix problem hardware or software problem
  3. Automatic page repair
  4. Forced Services [you can use it when the principle server down and the mirroring server is online you will >>>Connect to the mirror server >>>>Issue the following statement >>>>>ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS where <database_name> is the mirrored database. >>>> The mirror server immediately transitions to principal server, and mirroring is suspended. >>>> then you will go to fix the problem in principle server and when it return to work you will make resume to the mirroring server ]

How you can make suspend \Resume Mirroring session?

Go to Principle Server [Elmasry-PC\Principle]

1-    Check your Mirroring mode [Right Click on Mirrorin_DB >>>> Tasks >>>> Mirror >>>> you will see the mode is high performance mode because the last example make in this mode ]

2-    Now pause the mirroring DB the SQL will ask you Are you want to pass mirroring of this database? Answer YES

Paused mirroring Session

3-    Now Check the status to your database

4-    Now Right click on Mirroring_DB >>>> Tasks >>>> Launch Database mirroring monitor  you will see the database is suspended in the principle server and the mirroring server and you can check this by the time from the history this toll is very good to make check your mirroring session from time to another time

5- Now I will make some transaction on my database Mirroring_Db in principle server to the unsent log 
CREATE TABLE test_unsent_after_suspend
(ID INT , NAME NVARCHAR(50))
GO
DECLARE @id INT
SET @id = 1
 WHILE @id < 1000
 BEGIN
 INSERT INTO test_unsent_after_suspend VALUES(@id , ‘Mostafa Elmasry SQL DBA’)
 SET @id = @id +1
 END
 6- Let’s go to check the unsent log from the mirroring monitor

unsent Log

 7- i will go now to return the priciple server to work by make resume to the Mirroring_DB [same stsps like pause]

 

Remove Mirroring Session

1-      In principle server  right click on Mirrorin_DB >>> tasks >>> mirror >>> Remove Mirror

2-      Delete Endpoint from 3 Servers [principle-Mirroring-Witness] >>>Serrver objects >>>Endpoints>>>Database Mirroring >>>>Select your Endpoint .

3-      Refresh the Mirroring server you will see the Mirroring_Db status is restoring if you want to open it and use it you can make it by this Code

Wait me My friend in the Mirroring Part 4 (How to Create mirroring by Code)

Good Luck

 
1 Comment

Posted by on December 25, 2011 in Mirroring SQL Server 2008

 

Mirroring in SQL Server Part 2

Mirroring Setup Step by Step

  • Let’s start to setup mirroring server in SQL server 2008 R2 in the first I setup SQL Server 2008R2 and 3 instance in SQL Server 2008

1- Principle instance (Elmasry-PC\Principle)

2- Mirroring instance (Elmasry-PC\Mirroring)

3- Witness Instance (Elmasry-PC\Principle)

  • In my example I will make mirroring type high availability (Synchronize mode)
  • In principle server I will create database Mirroring_DB
  • Take full backup Mirroring_DB.bak
  • then take log Transaction backup Mirroring_DB.trn
  • Make restore to this backup full mirroring server with No recovery option
  • Then right click on Mirroring_DB >>Tasks>>Restore>>Transaction Log
  • Then Select your transaction log backup (Mirroring_DB.trn )
  • Don’t forget restore with no recovery option

 

High Availability Mode [Automatic Failover]

Principle Server

  • Right click on database Mirroring_Db >> tasks >> Mirror >> Configure Security
  • You will ask if you want to setup witness server or not? select yes.
  • Then you will ask to select your principle server [connect to Elmasry-PC\Principle] and make the port is 5021 and the endpoint name is Principle then Click next
  • You will ask again to select your Mirroring server [connect to Elmasry-PC\Mirroring] and make the port is 5022 and the endpoint name is mirroring then click next.
  • You will ask again to select your witness server [connect to Elmasry-PC] and make the port is 5023 and the endpoint name is Witness then click next .
  • In this step he will ask you to put your user name in your windows and this user must be add in the SQL Server so I write my username is [my pc name\windows username] [Elmasry-PC\Elmasry] .then click finish
  • SQL server after you click finish it will make configuration to the Principle Server , Mirroring Server , Witness Server
  • After this configuration is finish SQL server will give you massage if you want to Start mirroring or not select start and Waite few mints then mirroring will start and you see the status is [Synchronized: the databases are fully synchronized]

  • Now go to make refresh to the servers [principle – mirroring]

Principle Server [Elmasry-PC\Principle]

1- Create Table Name Employee

CREATE TABLE Employee
(Emp_Id INT NOT NULL PRIMARY KEY IDENTITY,
Emp_Name NVARCHAR(50) NOT NULL,
Tel NVARCHAR(12) NULL)

2- Insert data in this table

INSERT INTO dbo.Employee
( Emp_Name, Tel )
VALUES ( N’Mostafa’, — Emp_Name – nvarchar(50)
N’0172788327′ — Tel – nvarchar(12)
) , (‘Mohamed’ , ‘015876565645’)

3- Now if you select data from Employee Table you will see 2 rows

4- Right click on the principle server and make stop to the server

Mirroring Server [Elmasry-PC\Mirroring]

1- Refresh the server (you will see the database change from mirroring to principle database and if you open the database you will see the Employee table and the 2 rows

2- Now try to insert in this table another 2 rows

INSERT INTO dbo.Employee
( Emp_Name, Tel )
VALUES ( N’Omar’, — Emp_Name – nvarchar(50)
N’0172788327′ — Tel – nvarchar(12)
) , (‘Kemo’ , ‘015876565645’)

3- Stop the server and start the principle server [Elmasry-PC\Principle] you will see the database is mirroring make stop to the Mirroring server [Elmasry-PC\Mirroring] you will see the difference the mirroring database in the principle server will change to Principle sever and if you open the Employee table you will see 4 rows not 2 because the 2 rows you insert into database in mirroring server transfer automatically to another server

Mnaual Failover

Before I start to explain Mirroring High Safety mode I will explain something is very good that’s is manual Failover Yes you can make manual failover in the previous Example I make Failover (meaning change the server principle will be the mirroring and the mirroring will be the principle) I make this operation but Automatically when I make stop to the server and this operation make  Automatically because I make mirroring [High Availability] but in the manual failover you can make the same operation by your hand :

1-    Go to principle server then right click and select tasks >>>>> Mirror

2-    Then Click on Failover you will see the massage Click yes

3-    Then make refresh to the server you will see the change 

See Mirroring Part 3 https://mostafaelmasry.wordpress.com/2011/12/25/mirroring-sql-server-part-3/

Good Luck

 
1 Comment

Posted by on December 22, 2011 in Mirroring SQL Server 2008

 

Delete files in your Pc by SQL Server

If you have program write his log in files in your PC and you want to delete this file by your program you can make job in sql server to do this operation by ‘Ole Automation Procedures’ in SQL Server

in the first we need to enable ‘Ole Automation Procedures’ usind sp_configure

exec sp_configure ‘Ole Automation Procedures’, 1
go
reconfigure
go

After enabling it we can delete the files with Ole Automation Procedures which using FSO (File System Object) from SQL Server and we need to pass method name ‘DeleteFile’ in the tsql script. You can delete all the files or specific files as well. you can use this code to delete all temp in your drive

DECLARE

@Result int

DECLARE

@FSO_Token int

EXEC

@Result =sp_OACreate‘Scripting.FileSystemObject’, @FSO_Token OUTPUT

EXEC

@Result =sp_OAMethod@FSO_Token,‘DeleteFile’,NULL,‘D:\TestFolder\*.txt’

EXEC

@Result =sp_OADestroy@FSO_Token

 
 

Mirroring in SQL Server 2008 Part 1

Introduction to mirroring

  • Database mirroring is ( log shipping + replication ) – some of issues in log shipping and replication.
  • In database mirroring section you have 3 servers (principle, mirroring, and witness).
  • The database will be in the principle server and the application will be connect to the principle server then you will take backup (Full + log) from the database in the principle server the make restore to this backup with no recovery option in the mirroring. server so the record will copy from the principle server to the mirror server.
  • Witness server will be monitoring to the principle and the mirroring server.
  • You can setup the principle server in domain and the mirroring server in another domain

What happens if the principle server down in high availability mode?

if the principle server down the mirroring server will be the new principle server and the application will be connect automatically to the new principle server (mirroring server)

What happened when the application connect to the database in the principle server?

When the Application connect to the principle server and users make any transaction on this database the record will write in log puffer memory in principle server then write it in log file in database then the log puffer send the record to the log puffer memory to the mirroring server then the log puffer write this record in log file in database. Then the mirror server send to the principle server massage to know the principle the transaction is succeed then the principle tell the application the transaction is succeed.

Operation modes

You can setup the mirroring server in 3 modes

  1. High availability.

In high availability you need 3 servers (principle server, mirroring server, witness server) so in this operation mode the application connect on the principle server then the transaction write in log file then the principle server send the record to the mirror server in log file the mirror send to the principle (the record is commit) then the principle send to the application the transaction is committed

  1. High safety.

In high safety mode we don’t need to the witness server we will need in this section to the principle server and the mirror server only so there’s no automatic failover in high safety mode. And the operation will be same in the high availability mode [application connect on the principle server then the transaction write in log file then the principle server send the record to the mirror server in log file the mirror send to the principle (the record is commit) then the principle send to the application the transaction is committed ]

Remarks: the difference between high availability mode and high safety mode is Automatic failover in high availability will found Automatic failover but in high safety will not found Automatic failover. So if the principle server down you have problem because you will make manually start the mirroring server to be principle server. Not like in high availability mode this operation make automatically because this operation mode have automatic failover.

  1. High performance.

In high performance mode you need 2 server (principle server , mirror server) like high safety mode . So the difference between the high performance mode and high safety mode is in high performance the application send the transaction to the principle server in log file then the principle send the record to the mirror server and send to the application the transaction is committed so the principle server don’t wait the mirror server like (high safety mode and high availability mode)

 

Operation mode

servers

Automatic failover

Principle wait the mirror

High availability

3 servers

Yes

Yes

High safety

2 servers

No

Yes

High performance

2 servers

No

No

 In the finale I explain what is mirroring? , what is the operation mode in mirroring? , what is the difference between operation mode in mirroring? , remember I tell you high safety and high performance not need witness server but you can create witness server in this operation mode but it will make problems whit you so don’t make witness server in high safety mode and high performance mode .

Mirroring requirements:

1-    The database and file location should be same.

2-    Collection and master code page should be same in the principle and mirror server.

3-    Database name should be same in the principle and mirror server.

4-     You cannot mirror more the 10 databases in 32 bit servers you can in 64 bit server but not recommended.

5-     You cannot use attach/de attach you can use backup and restore.

6-     Ports in mirroring should be opening in firewall or close the firewall.

7-     Services account In SQL and SQL agent should be same in all servers.

8-     The mirroring not support the cross database transaction & distributed transaction log

9-     SQL server number should be same but you can setup principle server on SQL Server 2008 service pack 1 and the mirror on SQL Server services pack two

Advantages of database mirroring  :

1-     Does not require special hardware (such as shared storage, heart-beat connection) and cluster ware, thus potentially has lower infrastructure cost

2-     Database mirroring supports full-text catalogs

3-     Hardware and software upgrade that’s very easy.

Remark: If you want upgrade the mirror server from SQL server 2008 R2 to denial you must pass the mirroring in this section then after upgrade start the mirroring again

4-     Increases the data protection (disaster recovery).

5-     Increases the database availability if you use the Sync mode.

6-     Cost of database mirroring is less than clustering.

7-     It’s robust and efficient than log shipping and replication.

8-     Failover is fast compare to cluster

9-     Mirror server can be used to host databases for other applications not like clustering.

Disadvantage of database mirroring:

1-     Mirroring doesn’t support file stream.

2-     Mirror server is not available for database read only.

3-     Mirror server working in database level not in server level not like clustering (in clustering failover  you make this in server level on all database in this server in logins in jobs like this)

Mirroring enhancement in SQL Server 2008 from 2005:

Reference: [http://www.sqlserver-training.com/database-mirroring-enhancements-in-sql-server-2008-from-2005/-]

1)   Database mirroring automatic page repair.

  • If a page on the principle or mirror server is corrupt, it is automatically replaced with the corresponding copy on its partner
  • Some page types cannot be automatically repaired:
    • File header pages
    • Database boot page
    • Allocation pages

2)   Compressed Data flow

  •  Data Flow between the principle and mirror server is now compressed to improve performance.

 3)   Manual Failover

  • Manual failover no longer require a database restart

4)   Log performance

  • log-send buffers
  • Page read-ahead
  • Write-ahead on the incoming log stream on the mirror server

Part 2 >>>https://mostafaelmasry.wordpress.com/2011/12/22/mirroring-in-sql-server-part-2/

 
2 Comments

Posted by on December 3, 2011 in Mirroring SQL Server 2008