Database Mirroring between Servers outside domain (workgroup)

Database Mirroring between Servers outside domain (workgroup)

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 MirrorLoginGO
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 PrincipalLoginGO
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 TestDBSET PARTNER = 'TCP://MirrorServerIP:5023'
GO
-- Set the Mirroring to Asynchronous mode (if needed)
ALTER DATABASE TestDB  SET PARTNER SAFETY OFF
GOThat`s all after Step 6 the mirroring should be initialized.

SQL Server Queries With Hints

Problem

Sometimes the database administrator wants a quick inventory of all queries in use on the system that meet some criteria. One that I've come across a few times is to get a feel for all of the queries being used that involve hints, whether they are table hints (like NOEXPAND), index hints (such as FORCESEEK), or query hints (for example, MAXDOP). The first instinct by many is to manually parse all of the code in sys.sql_modules. This is problematic because searching for terms using a wildcard can yield false positives (since they can be included in table names or they can be mentioned in comments), and not all queries are run from stored procedures and views - some are sent in an ad hoc manner through applications. Even code stored in applications is not always searchable; for example, queries can be dynamically generated in application code or by ORMs. Now, that code shouldn't contain hints, but it does. The next instinct is to use a server-side trace (or worse yet, profiler). This is casting a pretty wide net, can place a significant load on your server, and still relies on very loose parsing rules.

Solution

For *some* hints, there is - at least I believe - a better answer, and that is parsing the query plans that have been cached on the system. It has some caveats, and there are some hints it doesn't cover, but for a few options it can be quite effective. It uses the output from the DMV sys.dm_exec_cached_plans and the functions sys.dm_exec_sql_text and sys.dm_exec_text_query_plan (which outputs the plan XML in plain text, making it simpler to grep). In its simplest form, you can derive the query along with the text version of the plan for all cached plans in the following way:

SELECT t.[text], qp.query_plan 
    FROM sys.dm_exec_cached_plans AS p
    CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
    CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp;

If you inspect a few plans for queries that use hints, you will see that the plan itself does yield some clues about when these options are used. Some common hints you can derive from the XML (this is not meant to be an exhaustive list):

<QueryPlan      NonParallelPlanReason="MaxDOPSetToOne"
-- this query used OPTION (MAXDOP 1) --^^^^^^^^^^^^^^
-- or perhaps was forced using server maxdop settings or resource governor
<IndexScan Ordered="0" ForcedIndex="0" ForceScan="1" NoExpandHint="0">
-- this query used WITH (FORCESCAN) --------------^
<IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="1">
-- this query used WITH (NOEXPAND) --------------------------------^
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="1" ...>
-- this query used WITH (FORCESEEK) --------------------------------------^
<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="1"
-- this query uses WITH (FORCESEEK(index(col))) ------------^-------------^
<IndexScan      Ordered="0" ForcedIndex="1" ForceScan="0" NoExpandHint="0">
-- this query used WITH (INDEX = ...) ---^

XML-heads are probably already looking at that and visualizing how they can write a quick XQuery to derive that information, but since you can get a non-XML version of the plan, you don't necessarily have to become an XML guru to perform this parsing. For example, the following will capture, with a high degree of accuracy, all query plans in the cache that used any of the above hints:

SELECT [Query] = t.[text], [Database] = DB_NAME(t.dbid), qp.query_plan,
    [ForceSeek]  = CASE WHEN qp.query_plan   LIKE '%ForceSeek="1"%'                 THEN 1 ELSE 0 END,
    [ForceScan]  = CASE WHEN qp.query_plan   LIKE '%ForceScan="1"%'                 THEN 1 ELSE 0 END,
    [NoExpand]   = CASE WHEN qp.query_plan   LIKE '%NoExpandHint="1"%'              THEN 1 ELSE 0 END,
    [ForceIndex] = CASE WHEN qp.query_plan   LIKE '%ForcedIndex="1" ForceSeek="1"%' THEN 1 ELSE 0 END,
    [NoLock]     = CASE WHEN UPPER(t.[text]) LIKE '%NOLOCK%'                        THEN 1 ELSE 0 END,
    [MaxDop]     = CASE WHEN qp.query_plan   LIKE '%<QueryPlan%[^<]%"MaxDopSet%' 
                       AND UPPER(t.[text])   LIKE '%MAXDOP%'                        THEN 1 ELSE 0 END
FROM 
    sys.dm_exec_cached_plans AS p
    CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
    CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp
WHERE 
    t.[text] NOT LIKE '%dm_exec_cached_plans%' -- to keep this query out of result
    AND
    (
      qp.query_plan LIKE '%ForceSeek="1"%'
      OR qp.query_plan LIKE '%Forcescan="1"%'
      OR qp.query_plan LIKE '%NoExpandHint="1"%'
      OR qp.query_plan LIKE '%ForcedIndex="1" ForceSeek="1"%'
      OR UPPER(t.[text]) LIKE '%NOLOCK%'
   OR (qp.query_plan LIKE '%<QueryPlan%[^<]%"MaxDopSet%' AND UPPER(t.[text]) LIKE '%MAXDOP%')
    ) 
    --AND t.[dbid] = DB_ID() -- to limit results, but may be too exclusionary
;

I say high degree of accuracy because this isn't perfect. It could produce false positives for queries like this:

    SELECT * FROM dbo.table /* WITH (NOLOCK) */
    SELECT * FROM dbo.RenoLocksmiths;

But it will *not* return false positives for queries like this, which would come out of a brute force parsing match:

    SELECT * FROM dbo.table /* Forceseek="1" */
    SELECT * FROM dbo.[WITH (FORCESEEK)];

There are some other disclaimers and caveats you should keep in mind:

• There are several hints that aren't exposed in a deterministic way in the query plan, such as OPTION (FORCE ORDER), OPTION (FAST n), and join hints like INNER LOOP JOIN, so - like NOLOCK - these may also need to rely on parsing the query text as opposed to relying on the plan XML.
• The DMV only exposes plans that are currently in the cache - the pool disappears when you restart or run DBCC FREEPROCCACHE (or other things that can cause this indirectly), and individual plans can get aged out. To avoid this, you can take snapshots of the above query periodically using methodology similar to this tip.
• Certain hints are actually self-defeating for this purpose, e.g. OPTION (RECOMPILE) prevents the plan from being cached at all.
• If you are using the optimize for ad hoc workloads setting, you may not be able to discover queries associated with single-use plans if they have, in fact, only been run once.

Intermediate SSIS Workflow Management – Step 7 of the Stairway to Integration Services

Introduction

In the previous installment, we built a new SSIS package, took a first look at scripting and precedence constraints in SQL Server Integration Services, and examined the MaxConcurrentExecutables package property.
In this article we will examine, demonstrate, and test “On Success”, “On Completion”, and “On Failure” functionality of precedence constraints.

Constraint Evaluation

If you have been playing along at home, open the My_First_SSIS_Project solution and the Precedence.dtsx SSIS package. Connect a precedence constraint from Script Task 1 to Script Task 2 as shown in Figure 1:


Figure 1

A Quick Review

Near the end of Basic SSIS Workflow Management – Step 6 of the Stairway to Integration Services I included this important blurb:

The semantics here are important. The precedence constraint originates at Script Task 1 and terminates at Script Task 2. The originating task connects to the precedence constraint startpoint which has the “half-bubble” where it connects to the originating task. The endpoint has an arrow where it connects to the terminating task.

Precedence constraints evaluate. They test one or more conditions and, if the conditions are true, the constraint allows the terminating task to start execution.

On Success

To demonstrate, execute the package in the Business Intelligence Development Studio (BIDS) debugger. A message box from Script Task 1 appears first as shown in Figure 2:


Figure 2
When you click the OK button on the message box, Script Task 1 succeeds – turning green to indicate success – and the precedence constraint between Script Task 1 and Script Task 2 evaluates to True. When the precedent constraint evaluates, Script Task 2 executes and displays a message box as shown in Figure 3:


Figure 3
When you click the OK button on the Script Task 2 message box, the Precedence.dtsx package completes and succeeds as shown in Figure 4:


Figure 4
The precedence constraint between Script Task 1 and Script Task 2 evaluates because it is configured to evaluate “On Success” and Script Task 1 succeeded.
Stop the debugger.

On Completion (Success)

Right-click the precedence constraint to see configuration options available on the context menu. At the top we find an “Edit…” option followed by Success, Failure, and Completed as show in Figure 5:


Figure 5
If you click Completion, the precedence constraint changes color, from green to blue as show in Figure 6:


Figure 6
The precedence constraint between Script Task 1 and Script Task 2 is now configured to evaluate “On Completion”. It no longer matters whether Script Task 1 succeeds or fails. As long as Script Task 1 completes execution, the precedence constraint will evaluate and Script Task 2 will execute.
Execute the package in the SSIS debugger to test. Once Script Task 1 succeeds, Script Task 2 will execute as shown in Figure 7:


Figure 7
Stop the debugger.
I can hear you thinking: “Wait a minute, Andy. This only tested ‘On Completion’ precedence with Success. How do we know it works on Failure?” That is an excellent question. The answer is, “you do not know that ‘On Completion’ work on Failure… yet.” So let’s test that.
How do we test “On Failure” execution?

On Completion (Failure)

First, we need a way to generate a failure. Open the Script Task 1 Editor and click the “Edit Script” button. Did you know message boxes can return values? They return integer values that represent the type of button pressed to acknowledge and close the message box. To implement this functionality, change the script contained in Public Sub Main() to the following VB code:

Public Sub Main()
        Dim sTaskName As String = Dts.Variables("TaskName").Value.ToString
        Dim iResponse As Integer = MsgBox("Succeed " & _

         sTaskName & "?", MsgBoxStyle.YesNo, sTaskName)

        If iResponse = MsgBoxResult.Yes Then
            Dts.TaskResult = ScriptResults.Success
        Else
            Dts.TaskResult = ScriptResults.Failure
        End If
    End Sub

The second and third lines of code are really a single statement concatenated by the “& _” at the end of the first line. The statement declares the variable iResponse as an Integer and sets it to the value returned from the MsgBox function. The MsgBox function returns a value that corresponds to the message box button pressed by the user.  The If-Then-Else conditional on the fifth through ninth lines of code respond to the iResponse Integer value; instructing the Script Task to return Success if a Yes button is pressed (MsgBoxResult.Yes), otherwise return Failure.
Close the VB Script editor and then click the OK button to close the Script Task Editor. Execute the package in the BIDS debugger. If you receive no error, your first message box should display as shown in Figure 8:



Figure 8
If you click the “Yes” button, the On Completion precedence constraint will evaluate and Script Task 2 will execute. But this is the use case we demonstrated earlier. If you click the “No” button, Script Task 2 will also execute as shown in Figure 9:



Figure 9
If you followed my instructions precisely during the previous article and this article, there is a possibility you will not see the message box shown in Figure 9 for Script Task 2; but instead see a message box that looks remarkably similar to the new message box we coded for Script Task 1. Why? If you recall, we copied and pasted Script Task 1 to create Script Task 2. Sometimes, enough of the metadata that identifies a copied Script Task will cause code from one script task to execute in the context of a copied and pasted second script task. It is rare, but it happens. To achieve the desired results, open the editor for Script Task 2 and edit its script by adding a line feed somewhere (where you add the line feed really isn’t important). This change will cause BIDS to “re-evaluate” the script embedded in Script Task 2. The script task should then perform as designed.
Now that we know “On Completion” works whether the preceding task succeeds or fails, let’s retest “On Success”!

Retesting On Success

Right-click the precedence constraint and set it to Success. It should change color from blue to green as shown in Figure 10:



Figure 10
When prompted to succeed Script Task 1 click the “No” button to cause it to fail. As shown in Figure 11, Script Task 2 should not execute:



Figure 11
We can use the “On Success” precedence constraint to stop execution if an exception or error occurs. Cool. Also, our snappy Script Task 1 code will allow us to test “On Failure” conditions. Let’s do that now!

On Failure

Stop the debugger and right-click the precedence constraint and select “Failure” as shown in Figure 12:



Figure 12
The precedence constraint will change color to red to indicate it is configured to evaluate on the failure of the preceding task as shown in Figure 13:



Figure 13
Execute the package in the BIDS debugger. When prompted to succeed Script Task 1, click the “No” button. Script Task 2 should execute as shown in Figure 14:



Figure 14
Click the Restart button, shown in Figure 15:



Figure 15
When the package restarts and the Script Task 1 prompt displays, click the “Yes” button to succeed Script Task 1. The precedence constraint, configured to evaluate “On Failure” of Script Task 1, does not evaluate and Script Task 2 does not execute as shown in Figure 16:



Figure 16
Stop the debugger.