MS SQL Server 2012 - Database Mirroring Setup - using SSMS

http://thedbavault.blogspot.in/2012/10/ms-sql-server-2012-database-mirroring.html

Database mirroring is a technology solution for increasing the availability of a SQL Server database. it is implemented on a per-database basis and works with databases that use full recovery model. With the launch of SQL Server 2012 and introduction of AlwaysOn HADR feature, Microsoft plans to remove Database Mirroring capabilities in future versions of Microsoft SQL Server. It is better for DBAs not to deploy Database Mirroring  feature in new development work and plan to modify applications that currently use this feature and convert them to AlwaysOn Availability Groups as the enterprise upgrades to SQL Server 2012. 

Setting up database mirroring in MS SQL Server 2012 is similar to previous versions. Information in this guide is for reference only, for all new developments MS SQL Server 2012 AlwaysOn HADR options are preferred.

Implementing Database Mirroring:

Step 1: Confirm User database is set to use 'FULL' recovery model.

Database Mirroring requires the recovery model of the principal database to be in FULL recovery model.

Click Start | All Programs | Microsoft SQL Server 2012 | SQL Server Management Studio . 

Connect to the Principal Server <<PrincipalServerName\InstanceName>>

In the Object Explorer pane, expand the Databases node. 

Right click on <<Database>> and select Properties . 

When the Database Properties displays click Options . 

Confirm the Database Recovery Model is set to Full . Click OK .

Step 2: Backup the Principal Database

In the Object Explorer pane, click New Query . 

Run the following T-SQL command to backup database and log file:

BACKUP DATABASE <<database name>> TO DISK=’<<path>>’ WITH INIT;

BACKUP LOG  <<database name>> TO  DISK=’<<path>>’ WITH INIT;

This can also be done graphically through the SSMS as shows in the screenshots below:

Step 3: Prepare the Mirror Database

Using Windows Explorer create a folder on the mirror server & copy the .BAK and .TRN file from the Principal Server to the Mirror Server. 

Using SSMS, connect to the server that will host the mirror database and restore the database in recovery mode, to allow the mirror to be created.

In the Object Explorer pane, click New Query . Run the following T-SQL command:

RESTORE DATABASE <<database name>> FROM DISK=’<<path>>’ WITH NORECOVERY, REPLACE ;

RESTORE LOG <<database name>> FROM DISK=’<<path>>’ WITH NORECOVERY;

In the Object Explorer pane, expand the Databases node, and then note that the database is in Restoring mode.

Important: NORECOVERY option is critical for this step because the database needs to be able to apply additional transaction log backups as part of the mirroring process.

Here is how you can do the same graphically through SSMS as shown in the screenshots below:

Step 4: Setting up Database Mirroring

1. In SQL Server Management Studio, right- click the <<database name>>, click Tasks, and then click Mirror . 

2. In the Database Properties dialog box, click Configure Security .

3. On the Configure Database Mirroring Security Wizard page, click Next . 

4. On the Include Witness Server page, select Yes, then click Next .

5. On the Mirror Server Instance page, select <<MirrorServerName\InstanceName>>, and then click Connect . 

6. On the Mirror Server Instance page, click Next . 

7. On the Witness Server Instance page, <<WitnessServerName\InstanceName>>, and then click Connect . 

8. On the Witness Server Instance page, click Next .

9. On the Service Accounts page, in the Principal, Witness, and Mirror boxes, type <<Domain>>\<<SQLServiceAccount>> .

10. On the Service Accounts page, click Next . 

11. On the Complete the Wizard page, click Finish.

12. On the Configuring Endpoints page, click Close when the configuration completes.

13. In the Database Properties dialog box, click Start Mirroring .

14. When the database mirroring starts, note the synchronization status. Click OK . 

Congratulations! Database Mirroring setup is complete.  Mirroring configuration for the primary database is high -safety mode. The <<database name>> is now collecting all transactions and applying them to the Secondary instance before they are committed in the Primary instance. 

Database Mirror Monitor:

The Database Mirroring Monitor is part of the SQL Server Monitor. It is launched from SQL Server Management Studio.

To launch the Database Mirroring Monitor, connect to the principal server instance, in Object Explorer, click the server name to expand the server tree.

Expand Databases, and select the database to be monitored.

Right-click the database, select Tasks, and then click Launch Database Mirroring Monitor.

In the Database Mirroring Monitor dialog box, click Register Mirrored Database to register one or more mirrored database.

Database Mirroring Monitor is used to monitor any subset of the mirrored databases on a server instance. It helps in verifying whether database mirror configuration is in good condition and how well data is flowing in the database mirroring session. It is also useful for troubleshooting the cause of reduced data flow.

Find some text in stored procedure

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%upperquart%'
ORDER by OBJECT_NAME(object_id)

SQL Server CROSS APPLY and OUTER APPLY

http://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

Problem

SQL Server 2005 introduced the APPLY operator, which is very much like a join clause and which allows joining between two table expressions i.e. joining a left/outer table expression with a right/inner table expression. The difference between join and APPLY operator becomes evident when you have a table-valued expression on the right side and you want this table-valued expression to be evaluated for each row from the left table expression. In this tip I am going to demonstrate what APPLY operator is, how it differs from regular JOINs and what are few of its applications.

Solution

The APPLY operator allows you to join two table expressions; the right table expression is processed every time for each row from the left table expression. As you might have guessed, the left table expression is evaluated first and then right table expression is evaluated against each row of the left table expression for final result-set. The final result-set contains all the selected columns from the left table expression followed by all the columns of right table expression.

The APPLY operator comes in two variants, the CROSS APPLY and the OUTER APPLY. The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression. In other words, the right table expression returns rows for left table expression match only.  Whereas the OUTER APPLY operator returns all the rows from left table expression irrespective of its match with the right table expression.  For those rows for which there are no corresponding matches in right table expression, it contains NULL values in columns of right table expression. So you might now conclude, the CROSS APPLY is semantically equivalent to INNER JOIN (or to be more precise its like a CROSS JOIN with a correlated sub-query) with a implicit join condition of 1=1 whereas OUTER APPLY is semantically equivalent to LEFT OUTER JOIN.

You might be wondering if the same can be achieved with regular JOIN clause then why and when to use APPLY operator? Though the same can be achieved with normal JOIN, the need of APPLY arises if you have table-valued expression on right part and also in some cases use of APPLY operator boost the performance of your query. Let me explain you with help of some examples.

Script #1 creates a Department table to hold information about departments. Then it creates an Employee table which hold information about the employees. Please note, each employee belongs to a department, hence the Employee table has referential integrity with the Department table.

Script #1 - Creating some temporary objects to work on...

USE [tempdb] GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Employee]') AND type IN (N'U')) BEGIN    DROP TABLE [Employee] END GO IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[Department]') AND type IN (N'U'))BEGIN    DROP TABLE [Department] END CREATE TABLE [Department](    [DepartmentID] [int] NOT NULL PRIMARY KEY,    [Name] VARCHAR(250) NOT NULL, ) ON [PRIMARY] INSERT [Department] ([DepartmentID], [Name])  VALUES (1, N'Engineering') INSERT [Department] ([DepartmentID], [Name])  VALUES (2, N'Administration') INSERT [Department] ([DepartmentID], [Name])  VALUES (3, N'Sales') INSERT [Department] ([DepartmentID], [Name])  VALUES (4, N'Marketing') INSERT [Department] ([DepartmentID], [Name])  VALUES (5, N'Finance') GO CREATE TABLE [Employee](    [EmployeeID] [int] NOT NULL PRIMARY KEY,    [FirstName] VARCHAR(250) NOT NULL,    [LastName] VARCHAR(250) NOT NULL,    [DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID), ) ON [PRIMARY] GO INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (1, N'Orlando', N'Gee', 1 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (2, N'Keith', N'Harris', 2 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (3, N'Donna', N'Carreras', 3 ) INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID]) VALUES (4, N'Janet', N'Gates', 3 )

First query in Script #2 selects data from Department table and uses CROSS APPLY to evaluate the Employee table for each record of the Department table. Second query simply joins the Department table with the Employee table and all the matching records are produced.

Script #2 - CROSS APPLY and INNER JOIN

SELECT * FROM Department D CROSS APPLY    (    SELECT * FROM Employee E    WHERE E.DepartmentID = D.DepartmentID    ) A GO SELECT * FROM Department D INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID GO

If you look at the results they produced, it is the exact same result-set; not only that even the execution plan for these queries are similar to each other and has equal query cost, as you can see in the image below. So what is the use of APPLY operator?  How does it differ from a JOIN and how does it help in writing more efficient queries. I will discuss this later, but first let me show you an example of OUTER APPLY also.

The first query in Script #3 selects data from Department table and uses OUTER APPLY to evaluate the Employee table for each record of the Department table.  For those rows for which there is not a match in Employee table, those rows contains NULL values as you can see in case of row 5 and 6. The second query simply uses a LEFT OUTER JOIN between the Department table and the Employee table.  As expected the query returns all rows from Department table; even for those rows for which there is no match in the Employee table.

Script #3 - OUTER APPLY and LEFT OUTER JOIN

SELECT * FROM Department D OUTER APPLY    (    SELECT * FROM Employee E    WHERE E.DepartmentID = D.DepartmentID    ) A GO SELECT * FROM Department D LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID GO

Even though the above two queries return the same information, the execution plan is a bit different. Although cost wise there is not much difference, the query with the OUTER APPLY uses a Compute Scalar operator (which has an estimated operator cost of 0.0000103 or almost 0% of total query cost) before Nested Loops operator to evaluate and produce the columns of Employee table.

Now comes the time to see where the APPLY operator is really required. In Script #4, I am creating a table-valued function which accepts DepartmentID as its parameter and returns all the employees who belong to this department. The next query selects data from Department table and uses CROSS APPLY to join with the function we created.  It passes the DepartmentID for each row from the outer table expression (in our case Department table) and evaluates the function for each row similar to a correlated subquery. The next query uses the OUTER APPLY in place of CROSS APPLY and hence unlike CROSS APPLY which returned only correlated data, the OUTER APPLY returns non-correlated data as well, placing NULLs into the missing columns.

Script #4 - APPLY with table-valued function

IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[fn_GetAllEmployeeOfADepartment]') AND type IN (N'IF'))BEGIN    DROP FUNCTION dbo.fn_GetAllEmployeeOfADepartment END GO CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment(@DeptID AS INT)  RETURNS TABLE AS RETURN    (    SELECT * FROM Employee E    WHERE E.DepartmentID = @DeptID    ) GO SELECT * FROM Department D CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO SELECT * FROM Department D OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID) GO

So now if you are wondering, can we use a simple join in place of the above queries? Then the answer is NO, if you replace CROSS/OUTER APPLY in the above queries with INNER JOIN/LEFT OUTER JOIN, specify ON clause (something as 1=1) and run the query, you will get "The multi-part identifier "D.DepartmentID" could not be bound."  error. This is because with JOINs the execution context of outer query is different from the execution context of the function (or aderived table), and you can not bind a value/variable from the outer query to the function as a parameter.  Hence the APPLY operator is required for such queries.

So in summary the APPLY operator is required when you have to use table-valued function in the query, but it can also be used with an inline SELECT statements.

Now let me show you another query with a Dynamic Management Function (DMF). Script #5 returns all the currently executing user queries except for the queries being executed by the current session. As you can see the script below, the sys.dm_exec_requests dynamic management view is being CROSS APPLY'ed with the sys.dm_exec_sql_text dynamic management function which accepts a "plan handle" for the query and the same "plan handle" is being passed from the left/outer expression to the function to work and to return the data.

Script #5 - APPLY with Dynamic Management Function (DMF)

USE master GO SELECT DB_NAME(database_id) AS [Database], [text] AS [Query]  FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.plan_handle) st WHERE session_Id > 50           -- Consider spids for users only, no system spids. AND session_Id NOT IN (@@SPID)  -- Don't include request from current spid.

Please note the [text] column in the above query returns the all queries submitted in a batch; if you want to see only active (currently executing) query you can use statement_start_offset and statement_end_offset columns to trim the active part of the query. Tim Ford has provided a very good explanation of usage of these columns in his How to isolate the current running commands in SQL Server tip.

As I told you before there are certain scenarios where a query with APPLY operator performs better than a query with regular joins but I am not going to delve into much details rather here are some articles which discuss this topic in greater details.

• INNER JOINS vs CROSS APPLY
• Using CROSS APPLY to optimize joins on BETWEEN conditions

Please note, APPLY operator is not an ANSI operator but rather an extension of SQL Server T-SQL (available in SQL Server 2005 and above), so if you plan to port your database to some other DBMS take this into your considerations.

Get Numeric Value From Alpha Numeric String

http://blog.sqlauthority.com/2008/10/14/sql-server-get-numeric-value-from-alpha-numeric-string-udf-for-get-numeric-numbers-only/

CREATE FUNCTION dbo.udf_GetNumeric(@strAlphaNumeric VARCHAR(256))RETURNS VARCHAR(256)AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)BEGIN
WHILE @intAlpha > 0BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' )SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )END
END
RETURN ISNULL(@strAlphaNumeric,0)ENDGO

/* Run the UDF with different test values */SELECT dbo.udf_GetNumeric('') AS 'EmptyString';SELECT dbo.udf_GetNumeric('asdf1234a1s2d3f4@@@') AS'asdf1234a1s2d3f4@@@';SELECT dbo.udf_GetNumeric('123456') AS '123456';SELECT dbo.udf_GetNumeric('asdf') AS 'asdf';SELECT dbo.udf_GetNumeric(NULL) AS 'NULL';GO