How to debug/troubleshoot a SP in production environment?

There are times when you need to debug

some stored procedure(s) in Production

environment or to make a SP to behave

differently when you execute it via the SSMS

versus case when the SP executed by an application.

Or to make SP to behave differently from requests

that come from different version of an application

or requests that come from specific IPs and etc.

Or you may want to know what values were

provided for the input parameters of the SP?

Here some usefull SQL functions that you can use in the SPs to achieve the above:

1) APP_NAME() - Returns the application name for the current session if set by
   the application. You can set it in the connection string of the application by
   including this: "Application Name=AppName".
   By using this function you can change a SP to do X if it executed from Server A
   and to do Y if it executed by Server Y or if it executed via the SSMS.
   Simply set the application name for different value in Server X and Y.
   By using this function you can also upload SPs changes without any downtime
   and with full backward compatibility.

2) CONNECTIONPROPERTY('client_net_address') - Returns the Client IP address.

3) CONNECTIONPROPERTY('local_net_address')  - Returns the IP address of the server.
   For example, if you connect to production SQL server from the SSMS in QA/DEV
   SQL Server then it will return the IP of that QA/DEV SQL Server machine.

By using these functions listed above you can add logic (If or CASE statements)
to the SP and you can troubleshoot/debug the SP without running the SQL Profiler.

SELECT APP_NAME() AS Application_Name,
               CONNECTIONPROPERTY('local_net_address') AS local_net_address,
               CONNECTIONPROPERTY('client_net_address') AS client_net_address
GO

How to find stored procedure(s) that scan particular index

When it comes to performance issues, we can get a lot from querying
the cached plans of stored procedures, for example:

  -  we can find which stored procedures(SPs) scan an index
  -  in which SPs we have bookmark lookups/ missing indexes
  -  find SPs where different spool operators being used
  -  get SPs which contain parallel execution
  -  check if an index being used in some SP(s)

and more.










Let`s start with the basic. By using the query below you can get
an execution plan of a stored procedure, open it and analyze it.

-- Get execution query plan in XML format
SELECT  OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,cp.usecounts AS ExecutionCount,
                 qp.query_plan AS QueryPlan
FROM     sys.dm_exec_cached_plans AS cp
                 CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
                 CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE  cp.objtype='Proc'
                AND st.dbid=DB_ID(DB_NAME()) -- Current DB
                AND OBJECT_NAME(st.objectid,st.dbid) = 'SPName'

By running the next query we can find out which stored procedures
scan particular index.

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS ns)
SELECT OBJECT_SCHEMA_NAME( qp.objectid ) AS [schema_name],
              OBJECT_NAME(qp.objectid ) AS [object_name],
              qp.query_plan
FROM    sys.dm_exec_cached_plans as p
             CROSS APPLY sys.dm_exec_query_plan( plan_handle ) as qp
             CROSS APPLY qp.query_plan.nodes('//ns:IndexScan/ns:Object[@Database="[DBNAME]"]
                        [@Schema="[dbo]"][@Table="[TblName]"][@Index="[IndexName]"]' ) plan_xml(x)

Important note: The XML is case sensitive, so insert the table/index name
exactly as they defined, pay attention to capital letters.

The following query can help you to check if an index being used,
this can be helpful when you want to delete an index.

WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),

CachedPlansCTE (DatabaseName,SchemaName,TableName,IndexName,QueryText,QueryPlan,[object_name])
AS
(
SELECT ObjNode.op.value(N'@Database',N'varchar(128)') AS DatabaseName,
              ObjNode.op.value(N'@Schema',N'varchar(128)') AS SchemaName,
              ObjNode.op.value(N'@Table',N'varchar(128)') AS TableName,
              ObjNode.op.value(N'@Index',N'varchar(128)') AS IndexName,
              cp.[text] AS QueryText,
              cp.query_plan AS QueryPlan,
              cp.[object_name]
FROM (
           SELECT query.[text],qp.query_plan,OBJECT_NAME(qp.objectid ) as [object_name]
           FROM    sys.dm_exec_cached_plans as p
                         OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
                         OUTER APPLY sys.dm_exec_query_plan(plan_handle) as qp
           WHERE  query.[text] NOT LIKE '%sys%'
                         AND p.cacheobjtype ='Compiled Plan'
                         AND p.objtype='Proc'
) cp
CROSS APPLY cp.query_plan.nodes(N'//Object') ObjNode (op)
)
SELECT  [object_name],DatabaseName,SchemaName,TableName,IndexName,QueryPlan,QueryText
FROM    CachedPlansCTE
WHERE  (IndexName like '%IndexName%')
              AND (QueryText not like '%insert%')
              AND (QueryText not like '%update%')
OPTION (MAXDOP 1)


Source: http://sqlconcept.com/2011/07/12/how-to-query-the-cached-plans-xml/
http://blogs.sqlserver.org.au/blogs/greg_linwood/archive/2008/04/27/1229.aspx

Slow Transaction Log restore in standby mode (Log Shipping)

I am always saying that crisis situations
are good for your knowledge.

They can contribute to your
understating how things are working.




Consider the following, you have Log Shipping configured in Standby mode and
you noticed that sometimes the 'Restoring…' takes long time than usual.

So, why restoring 100-200 MB transaction log takes 30-40 minutes on the
secondary server in Logged Shipped database?

In short, due to long running transactions and Standby mode.

Now more in details:

First we need to understand what Standby means and how it works.
When you restore transaction log(s) you have 3 options:

1) WITH RECOVERY
2) WITH NORECOVERY
3) WITH STANDBY

In Log shipping, you can choose between options 2 or 3.

When restoring transaction log(s) with option "RECOVERY", the SQL Server will
undo All uncommitted transactions, the database will be accessible but you will not
be able to restore more transaction logs.

When restoring transaction log(s) with option "NORECOVERY", the SQL Server
will leave the uncommitted transactions as is and you will not be able to access the
database.

When restoring transaction log(s) with the "STANDBY" option, the SQL Server will
go over the transaction log and save all uncommitted transactions to a standby data
file with .tuf extension. (TUF stands for Transaction Undo File). The database will be
accessible and in read-only mode.

You can ask why it needs the tuf file. Well, when you restore with STANDBY mode
the database stays accessible, right? Meaning you can connect to it and view the
data  (just read, because it in Read-Only mode).
But how it possible, what about uncommitted transactions?

You will not see the uncommitted data even if you read the data in
"READ Uncommitted Isolation Level". Thus is, because the SQL Server
performs UNDO for all uncommitted transactions and saves their data to
the tuf file. It simply saves the entire content of the pages that it had to undo
and in some cases the SQL Server can save not only the data rows but the all pages.

The tuf file will be used when you restore the next transaction log file.
First, the SQL Server will REDO all the uncommitted transactions that in the tuf file
(apply the content of the undo file to the data files) and only then continue to restore
the next transaction log.

Important note: the tuf file will contain the uncommitted transactions not just from
the latest transaction log but uncommitted transactions in all previous transaction
logs that have been restored.

Now after we understand how the STANDBY option works, let`s see what happening
when you rebuild indexes in the primary server and suppose that the rebuild of some
table spreads over more than one backed up transaction log. For example:
the rebuild takes 20 minutes and you backup the transaction log every 5 minutes.

I think you already got it. The secondary server upon restoring the first transaction
log that contains the start of the index rebuild will save the uncommitted data to
the tuf file, in our case, if we rebuild clustered index, it will save a lots of data to
the tuf file. And upon restoring the next transaction log, it will redo all the
uncommitted transactions saved in the tuf file and continue the restoring, but in
our case the index operation is not finished in the second transaction log file,
so SQL Server will again UNDO the rebuild index and save it to the standby file
(tuf file), now it will contain uncommitted data of the 2 transaction logs. And this
process continues and continues until the last transaction log with index rebuild
data will be restored.

I hope it helped you to understand the reason for the slow restore of the transaction
logs in the secondary logged shipped database.

Thanks for reading.

4 ways to efficiently monitor DB Mirroring with/without email/SMS Alerts

A setup of DB Mirroring (See my post here) cannot be full without
a setup of DB Mirroring monitoring.
I want to share with you 4 ways to monitor the DB Mirroring:

1) Querying the "sys.database_mirroring" catalog view and sending Email Alerts.
2) Using SQL Server Events Notifications, State-change (WMI) events.
3) Setting up Performance Threshold Events in the "Database Mirroring Monitor".
4) Setting up SQL Performance counters related to DB Mirroring.

Now more in details:

1) Querying the "sys.database_mirroring" catalog view and sending Email Alerts

We can query the sys.database_mirroring catalog view for checking the status
of the mirrored databases, and in case the DB status is not in valid state, like
SYNCHRONIZED or SYNCHRONIZING, we will send an email and/or SMS.

DECLARE @DBMirroringState VARCHAR(30),@DB_ID INT,@ErrorMessageToSend VARCHAR(100)
DECLARE @MirroredDatabases TABLE (DatabaseID INT, mirroring_state_desc VARCHAR(30))

-- get status for mirrored databases
INSERTINTO @MirroredDatabases(DatabaseID,mirroring_state_desc)
SELECT database_id, mirroring_state_desc
FROM    [sys].[database_mirroring]
WHERE  mirroring_role_desc IN ('PRINCIPAL','MIRROR')
             AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')

WHILE EXISTS (SELECT TOP 1 DatabaseID FROM @MirroredDatabases WHERE mirroring_state_desc IS NOT NULL)
BEGIN
     SELECT TOP 1
                   @DB_ID = DatabaseID,
                   @DBMirroringState = mirroring_state_desc
     FROM    @MirroredDatabases

    SET @ErrorMessageToSend = 'DBMirroring Error on DB:'+CAST(DB_NAME(@DB_ID) AS VARCHAR)+
                                           ',DBState='+@DBMirroringState

   -- Send Email
   EXEC msdb.dbo.sp_send_dbmail @profile_name='XXXXX',@recipients='DBA@Company.com',
                                              @body = @ErrorMessageToSend,@subject = @ErrorMessageToSend
  -- Send SMS
  -- put here code to send SMS

   DELETE FROM @MirroredDatabases WHERE DatabaseID = @DB_ID
END

Source: http://www.mssqltips.com/tip.asp?tip=1859

--------------------------------------------------------------------------------------------------------------------------------------------------------------
The second way is:
2) Using SQL Server Events Notifications, State-change (WMI) events
--------------------------------------------------------------------------------------------------------------------------------------------------------------
There is a way in the SQL Server to create an Alert for various inner events.
We will create an alert for specific DB mirroring events.

When a mirroring session changes from one state to another, SQL Server generates
a WMI event of type DATABASE_MIRRORING_STATE_CHANGE.
We will create WMI-based alert by querying the DATABASE_MIRRORING_STATE_CHANGE.

For example:
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6

In the Principal Server we will check for following events:

   - Connection with Mirror Lost
   - Mirroring Suspended

In the Mirror Server for following alerts:

  -  Connection with Principal Lost
  -  Mirroring Suspended

You can setup such alerts via the SSMS GUI or by executing a script.
In SSMS, go to "SQL Server Agent", right click on Alerts and choose "New Alert"
and define it as follow, for example:

And do not forget to specify to send the alert email:

or you can run a script, for example:

USE [msdb]

GO
EXEC msdb.dbo.sp_update_alert
         @name=N'DB Mirroring State Change: Connection with Mirror Lost',
         @message_id=0,
         @severity=0,
         @enabled=1,
         @delay_between_responses=0,
         @include_event_description_in=1,
         @database_name=N'',
         @notification_message=N'',
         @event_description_keyword=N'',
         @performance_condition=N'',
         @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
         @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6',
         @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification
         @alert_name=N'DB Mirroring State Change: Connection with Mirror Lost',
         @operator_name=N'DBA', @notification_method = 1
GO

Source: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/how-to-monitor-database-mirroring

3) Setting up Performance Threshold Events in the "Database Mirroring Monitor"

With these alerts, most of us should be familiar.
We can setup the thresholds in the "Database Mirroring Monitor":

As the result, SQL server will create "Database Mirroring Monitor Job" on
Principal and Mirror servers. This job will run every minute (by default) and update
undocumented database mirroring status table in the msdb database.

Now, after setting the thresholds, you need to create alert(s) using SQL Server Agent:

1. In SSMS, connect to the principal or mirror server instance for which you want to define an alert.
2. Expand the SQL Server Agent folder in Object Explorer.
3. Right-click Alerts and select New Alert.
4. In the New Alert dialog box, type a name you can use to identify the alert, such as "DB Mirroring: Unsent log warning."
5. Select Error number and type the number from Table below.
6. In Select a page, click Response to specify the actions to take when the event occurs, such as executing a job or notifying an operator.
7. In Select a page, click Options to specify how often to send a notification on this event, the format of the message sent to operators, and other options.

 Event IDs for Performance Warnings :

The first two warnings in table above should be defined in the Principal Server,
the rest in the Mirror Server.

Now very important note that can save you time,

When you define such alerts, do not specify the database, choose "All Databases",

Otherwise the alert will not occur (not fire) and the email will not be sent.
The SQL Server will write the alert to the Windows Event log and to
SQL Server Error Log, but SQL Server will not be able to recognize it
and will not raise the alert, because it has some bug recognizing such alerts

where database name was specified.
(The event logged as a server-level event and not a database-level event)

I have this bug in SQL Server 2008 R2 ENT with CU6 and Microsoft does not
provided a workaround yet.

http://connect.microsoft.com/SQLServer/feedback/details/657230/alerting-on-database-mirroring-events

If you have more that one mirrored database in one SQL Server instance and you
want to define the alerts per database, then you will have a problem when you
choose "All Databases".

Also when the alert defined with "All Databases", an email is sent without

specifying the database that exceeded its threshold.

4) Setting up SQL Performance counters related to DB Mirroring.

We can fetch SQL performance counters related to DB Mirroring from DMV called

"sys.dm_os_performance_counters".

I recommend to fetch following counters:

- Redo Queue KB :         The number of bytes of transaction log that remains to be

                                          applied to the mirror to roll it forwards.

                                              

- Log Send Queue KB:   The number of bytes in the log that has not been sent to

                                         the mirror

SELECT   [counter_name] as CounterName,[cntr_value] as CounterValueFROM      sys.dm_os_performance_counters

WHERE   [object_name] LIKE ('%Database Mirroring%')

                 AND [counter_name] IN ('Log Send Queue KB','Redo Queue KB')

                 AND [instance_name]='TestDB'

 

For example, you can query this DMV every minute and save the values in CACTI.

In the Cacti you will see nice graphs:

This method is for long run monitoring, to recognize trends and etc.

If you have other way, please share.

One more thing that good to know and understand:

In asynchronous mirroring the transaction log can fill up as transactions
cannot be backed up until they have been applied on the mirror.

For more info:

http://msdn.microsoft.com/en-us/library/ms365781.aspx

http://msdn.microsoft.com/en-us/library/ms408393.aspx

http://technet.microsoft.com/en-us/library/cc966392.aspx

SQL Jokes cont.

To continue the of SQL Jokes below

http://madebysql.blogspot.com/2009/07/sql-joke.html

http://madebysql.blogspot.com/2010/02/types-of-dbas.html

Here are some more:








-----------------------------------------------------------------------------------
Q: Why do you never ask SQL people to help you move your furniture?
A: They sometimes drop the table
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Q: How do you spell GOD?
A: DBA
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Q: When did God create the DBA ?
A: The day before he had his rights revoked.
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Q: Why do DBAs often confuse Halloween and Christmas?
A: Because '31 Oct' = '25 Dec'
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Q1: What did the DBA say to the Developer?
A: It doesn’t matter, he wasn’t listening anyway.
Q2: What did the Developer say to the DBA?
A: It doesn’t matter, the answer was no.
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
Joins are what RDBMS's do for a living.
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
"NULL is the Chuck Norris of the database - nothing can be compared to it."
-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------
DBA = Default Blame Acceptor
-----------------------------------------------------------------------------------

Do you have more? You are invited to share.

Is the % of an index fragmentation in Log shipped / Mirror database equal to the Primary/Source database?

Suppose there is Log shipping or Mirror between database X and database Y.
The mdf/ldf file(s) of the X DB and Y DB located in different storages.
I take some index in the primary db X and estimate its fragmentation by
using “dm_db_index_physical_stats” and do the estimation of that index
also in database Y.

SELECT  dm.object_id AS objectid,              
               dm.index_id AS indexid,
               QUOTENAME(i.name) AS name,
               dm.partition_number AS partitionnum,
               dm.avg_fragmentation_in_percent AS Fragmentation,
               i.fill_factor AS [Fillfactor],
               dm.page_count,
               CASE WHEN dm.alloc_unit_type_desc <> 'IN_ROW_DATA'
                        OR (t.lob_data_space_id = 1 AND dm.index_type_desc <> 'NONCLUSTERED INDEX')
                       THEN 'OFF' ELSE 'ON'
              END as [Online]
FROM    [sys].[dm_db_index_physical_stats] (DB_ID(), NULL, NULL , NULL, 'LIMITED') dm
              INNER JOIN [sys].[tables] t ON dm.object_id = t.object_id
              INNER JOIN [sys].[indexes] i ON dm.object_id = i.object_id AND dm.index_id = i.index_id
WHERE  dm.avg_fragmentation_in_percent > 0
              AND dm.page_count > 1000
              AND dm.index_id > 0

Let`s assume that the db X and Y are fully synchronized,
meaning all transactions from db X were transferred to db Y.

The question is: Is the % of the index fragmentation equal in db X and Y?

The answer is Yes.
Because every log record gets replayed on the secondary.
Every page split and every allocation is fully logged and then replayed
exactly the same on the secondary.

You may ask, what for you need to know the % of fragmentation
on secondary server. Well, first of all, estimation of the % of fragmentation
by using the “dm_db_index_physical_stats” in Production site may cause to
IO load. So we can estimate the fragmentation on the secondary server.
Secondary, it is always good to know and understand how it is working.

Here more info:
http://www.sqlsoldier.com/wp/sqlserver/whatdoesredonemean

Database Mirroring between a Server in Domain and Server outside domain (workgroup)

When you setup a Database Mirroring via the GUI in SSMS, you are
requested to specify the fully qualified domain names (FQDN) of the servers.
What about the cases when you need to configure the Database Mirroring

between two servers that not in a domain at all or the 2 servers on different

domains with no trust relationships or one server in domain and

the second is not. The setup of a DRP site whould be classic example.
You can enter IP addresses in the GUI wizard or

replace the IPs with some Server names that you put in the HOST file

it will work only if both servers in the same domain, in other cases you will

probably fail to setup the Mirroring, because SQL Server will try to use

Windows Authentication between the servers (By default the Configure

Database Mirroring Security Wizard always uses Windows Authentication)

You will get an error like this:

  

The server network address“TCP://SQLServer:5023″ cannot be reached or

does not exist.Check the network address name and that the ports for the local

and remote endpoints are operational.(Microsoft SQL Server, Error: 1418)

 

So how to setup the Database Mirroring when the two Servers not in the same

domain or the two servers are workgroups or one server in a domain and other not?

Well, SQL Server allows configuring database mirroring under mixed mode

authentication using SQL Server logins with the added security of using certificates. 

In English, you will have to create SQL Server Logins/Database Users and

the Mirroring EndPoint with authentication that use Certificate and not

Windows Authentication.

Note: The explanation below is dealing with mirroring configuration without
the witness server, please refer to links below if your configuration includes
witness server, in general all the steps for Principal/Mirror server should be
done also for the witness server.

Here is the highlight summary of the steps:
All the steps below should be done in each server instance(Principal/Mirror)
SQL scripts for each step you can find at the end,
and I suppose the you already restored the DB (and transaction logs, if needed)
in Mirror SQL Server with NORECOVEY of course.

1.In the master database, create a database master key.
2.In the master database, create an encrypted certificate.
3.Create a mirroring endpoint using the created certificate.
4.Back up the certificate to a file and copy it to the other server.

After you complete the steps above in both servers, do the following
also in both servers in master database:

5.Create a SQL Server login in each Server
6.Create a database user for created login.
7.Associate the certificate with the database user created in step 6.
8.Grant CONNECT permission on the SQL Server login for created
   mirroring endpoint.

Now let`s see the SQL Scripts,
note that the scripts have different numeration then general steps above.

-------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance */
-------------------------------------------------------------------------------------------------------
-- Step 1 :
--                    CREATE MASTER KEY,CREATE CERTIFICATE
--                   CREATE ENDPOINT,BACKUP CERTIFICATE to file
---------------------------------------------------------------------------------
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
 --Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE PrincipalServerCertificate
WITH SUBJECT = 'PrincipalServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
 --Create the database mirroring endpoint for the principal server using the certificate for authentication
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING(AUTHENTICATION = CERTIFICATE PrincipalServerCertificate
,ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE PrincipalServerCertificate
TO FILE = 'c:\PrincipalServerCertificate.cer'
GO

 --------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Mirror Instance */
---------------------------------------------------------------------------------------------------------------------------
-- Step 2 :
--              CREATE MASTER KEY,CREATE CERTIFICATE,
--              CREATE ENDPOINT,BACKUP CERTIFICATE to file
----------------------------------------------------------------------------------
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepassword'
GO
--Create the server-based certificate which will be used to encrypt the database mirroring endpoint
CREATE CERTIFICATE MirrorServerCertificate
WITH SUBJECT = 'MirrorServer certificate',
START_DATE = '20110601',
EXPIRY_DATE = '20500101'
GO
CREATE ENDPOINT MirroringEndPoint
STATE = STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)
FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE MirrorServerCertificate,
ENCRYPTION = REQUIRED ALGORITHM RC4,ROLE = ALL)
GO
BACKUP CERTIFICATE MirrorServerCertificate TO FILE = 'c:\MirrorServerCertificate.cer';
GO

------------------------------------------------------------------------------------------------------------------------
/* Execute this against the Principal Instance.    
     The MirrorServerCertificate.cer needs to be copied on the Principal Server.
*/
-------------------------------------------------------------------------------------------------------------------------
-- Step 3 :
--               CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Mirror server)
--               and grant the created User to it.
------------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN MirrorLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER MirrorUser FOR LOGIN MirrorLogin
GO
CREATE CERTIFICATE MirrorServerCertificate AUTHORIZATION MirrorUser
FROM FILE ='c:\MirrorServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [MirrorLogin]
GO


/* Execute this against the Mirror Instance.  
    The PrincipalServerCertificate.cer needs to be copied on the Mirror Server.
*/
----------------------------------------------------------------------------------------------------------------------
-- Step 4 :
--                CREATE LOGIN,CREATE USER,CREATE CERTIFICATE (from Principal server)
--                and grant the created User to it.
----------------------------------------------------------------------------------------------------------------------
USE MASTER
GO
CREATE LOGIN PrincipalLogin WITH PASSWORD = 'somepassword'
GO
CREATE USER PrincipalUser FOR LOGIN PrincipalLogin
GO
CREATE CERTIFICATE PrincipalServerCertificate AUTHORIZATION PrincipalUser
FROM FILE = 'c:\PrincipalServerCertificate.cer'
GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint TO [PrincipalLogin]
GO

/* Execute this against the Mirror Instance.*/
------------------------------------------------------------------------------------------------
-- Step 5 :
--             Prepare the mirror server for the database mirroring session
------------------------------------------------------------------------------------------------
ALTER DATABASE TESTDB
SET PARTNER = 'TCP://PrincipalServerIP5022'
GO

/* Execute this against the Principal Instance.*/
-------------------------------------------------------------------------------------------------
-- Step 6 :
--            Prepare the Principal server for the database mirroring session
-------------------------------------------------------------------------------------------------
ALTER DATABASE TestDB
SET PARTNER = 'TCP://MirrorServerIP:5023'
GO
-- Set the Mirroring to Asynchronous mode (if needed)
ALTER DATABASE TestDB SET PARTNER SAFETY OFF
GO

That`s all after Step 6 the mirroring shoul be initialiazed.

Troubleshooting:

1)  Ensure the Principal and Mirror Server listening to defined mirroring endpoints.
     They should be listening after you define the endpoints and their state is Started.
      You can use the following command in Principal Server
       netstat -na | find "5022"
       and this in Mirror Server
       netstat -na | find "5023"

       Of course, you can set the port numbers to be different then in this example.

2) Make sure firewall is not blocking the port and the firewall is allowing traffic both directions

3) Check the SQL log for errors


Mirroring Monitoring problems:

In our case, which is, Principal Server in a Domain and the Mirror Server
is not, when launching the "Database Mirroring Monitor" we had the following error:
  














If the Monitor was executed in the Principal Server, then there was an error
saying that the Mirror Server not connected. And vice versa when the Monitor
was launched from Mirror Server.
Going to "Manage Server Instance Connections" option

and choosing SQL Authentication in the connection of Mirror Server not helped,
it gave the following errors

or
"SQL Server replication requires the actual server name to make a connection to
the server. Connections through a server alias, IP address, or any other alternate
name are not supported."

It seems that SQL Server tries to connect to the Mirror server by using
the Server Name and not IP.
Adding the mapping to HOST file solved the problem.

---------------------------------------------------------------------------------------
To see how to monitor the DB Mirroring from every angle,
please my post here.
---------------------------------------------------------------------------------------
Also, I want to share the Clean up scripts,
they are needed when the mirroring is not working and you want to start
over again or if you are not sure in each step you stopped and etc.

-----------------------------------------------------------------------
-- Clean up / check scrips for the PRINCIPAL/MIRROR SQL Server
-----------------------------------------------------------------------
/*
------------------------------------------------------------------------------------------------------------
Drop master key
----------------------------------------------------------------------------------------------------------

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

-- check if the database master key is encrypted by the service master key.
SELECT is_master_key_encrypted_by_server ,*
FROM sys.databases
WHERE name='master'

select name, principal_id, algorithm_desc, create_date
from master.sys.symmetric_keys
where name like '%databasemaster%'


DROP MASTER KEY

--------------------------------------------------------------------------------------------------------
-- DROP ENDPOINT
--------------------------------------------------------------------------------------------------------

DROP ENDPOINT MirroringEndPoint

SELECT name, role_desc, state_desc
FROM sys.database_mirroring_endpoints

--------------------------------------------------------------------------------------------------------
-- DROP CERTIFICATE
--------------------------------------------------------------------------------------------------------


DROP CERTIFICATE PrincipalServerCertificate
DROP CERTIFICATE MirrorServerCertificate

SELECT *
FROM sys.certificates

-------------------------------------------------------------------------------------------------------
-- DROP USER
-------------------------------------------------------------------------------------------------------

DROP USER MirrorUser

SELECT * FROM sys.sysusers;

--------------------------------------------------------------------------------------------------------
-- DROP LOGIN
-------------------------------------------------------------------------------------------------------

DROP LOGIN MirrorLogin

SELECT * FROM sys.server_principals 

*/

For more information see following links
http://technet.microsoft.com/en-us/library/ms191477.aspx

http://www.mssqltips.com/tip.asp?tip=1705

http://blogs.msdn.com/b/john_daskalakis/archive/2009/01/15/9320483.aspx