Database Mirroring

Database Mirroring provides database redundancy by transferring data from the transaction log to another instance of SQL Server. There are several operating modes that can be used such as: high performance, high safety with automatic failover and high safety without automatic failover. Based on the operating mode, we can have complete or almost complete database redundancy. More information can be read from this MSDN library article.
Use:

• This is a good option when there is the need to have automatic failover for a database. It can be near real time failover of a database depending on the options used. Also a good choice if there is a need to provide database connectivity with minimal downtime.

Recovery:

• Recovery Time Objective (RTO) - Depending on the options used it could be almost immediately for a database, because the mirror copy will become the primary copy. Also, if you use the Failover Partner option in the connection string the application should be able to find the new server without any other configuration changes.
• Recovery Point Objective (RPO) - If there is no data loss (due to data deletion), there is minimal to no data loss during the failover. Because this is an exact copy of the primary database all transactions will be replicated to the mirror copy as they were done on the primary server.

Pros:

• It provides automatic failover. (if used with a witness)
• Snapshots of the database can be created against the mirrored copy for read only access and off-loading reporting to another server.
• It provides near real time failover of a database, depending on the options used.
• No additional cost, except for the need to have another available server for the mirror and possibly a third for the witness.
• If secondary server is used in a passive mode (only for failover), additional SQL Server licensing is not needed.

Cons:

• High safety without automatic failover setting may be network overhead.
• A third server is required for "automatic failover" (witness - SQL Server Express can be used)
• Other items need to be handled outside of mirroring such as logins, SQL Agent jobs, etc...
• Requires additional storage for mirrored copy
• If Snapshots are used for read only, the snapshot is only as current as when the snapshot was created.

Enable CLR Integration

If you call a CLR procedure or View without having the CLR Integration enabled, you will get the following error message:

Msg 6263, Level 16, State 1, Line 2
Execution of user code in the .NET Framework is disabled.
After enabling CLR, you don't need to restart the server

SP_CONFIGURE 'clr_enabled', 1
GO
RECONFIGURE
GO

Get First Day of Next and Previous Month

-- To Get First Day of Next Month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) + 1, '19000101')
as [First Day Next Month];
GO

-- To Get First Day of Previous Month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()) - 1, '19000101')
as [First Day Previous Month];
GO

Number of days in a month

Solution 1
DECLARE @Date datetime
SET @Date = '2000/02/1'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'No of Days in the month'

Solution 2

DECLARE @d DATETIME
SET @d = '2003-02-1'
select datepart(dd, dateadd(dd, -(datepart(dd, dateadd(mm, 1, @d))),dateadd(mm, 1, @d))) AS 'No of Days in the month'

Solution 3

DECLARE @d DATETIME
SET @d = '2000-02-1'
SELECT CASE WHEN MONTH(@d) = 1
THEN 31
WHEN MONTH(@d) = 2
THEN CASE WHEN (YEAR(@d) % 4 = 0 AND YEAR(@d) % 100 <> 0) OR
YEAR(@d) % 400 = 0
THEN 29
ELSE 28
END
WHEN MONTH(@d) = 3
THEN 31
WHEN MONTH(@d) = 4
THEN 30
WHEN MONTH(@d) = 5
THEN 31
WHEN MONTH(@d) = 6
THEN 30
WHEN MONTH(@d) = 7
THEN 31
WHEN MONTH(@d) = 8
THEN 31
WHEN MONTH(@d) = 9
THEN 30
WHEN MONTH(@d) = 10
THEN 31
WHEN MONTH(@d) = 11
THEN 30
WHEN MONTH(@d) = 12
THEN 31
END AS 'No of Days in the month'

Solution 4

SELECT DAY(DATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DAY(@d),@d)))) AS '

No of Days in the month

'

Unable to install SQL Server 2005 on a Windows XP machine

When we try to install SQL Server 2005 on a Windows XP with Service pack 3, we get the following error :

---------------------------
setup.exe - Application Error
---------------------------

The application failed to initialize properly (0xc0150004). Click on OK to terminate the application.

---------------------------
OK
---------------------------

We get this error message as soon as we click on the setup.exe file.

Resolution:

We can install the Dependancywalker tool and take a profiler trace in Dependancywalker. The steps are as below,

1. Download link : http://www.dependencywalker.com

2. Open the Dependencywalker

3. Go to FILE —OPEN —Select the SETUP.EXE [Ex- C:\SQLServer 2005\Servers\setup.exe]

4. Go to PROFILE from the Menubar, select “Start Profiling(F7)”

5. Select the path to save “Starting directory”

6. Select the following 2 options along with the default selection and click OK

—Log first chance exceptions

—Use full paths when logging file names

7. Reproduce the Issue

8. Stop the profiling

—Go to PROFILE from the Menubar, select “Stop Profiling(Shift + F7)”

9. To save the trace

—Go to FILE—SAVE

10. Check the Log window for Errors. We should be seeing the following error :

- Error from Dependencywalker Log window

--------------------------------------------------------------------------------------------------------------------------------------------------

SXS: Unable to resolve storage root for assembly directory x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.4053_x-ww_e6967989 in 2 tries

SXS: RtlGetAssemblyStorageRoot() unable to resolve storage map entry. Status = 0xc0150004

SXS: Unable to resolve storage root for assembly directory x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.4053_x-ww_e6967989 in 2 tries

SXS: RtlGetAssemblyStorageRoot() unable to resolve storage map entry. Status = 0xc0150004

SXS: Unable to resolve storage root for assembly directory x86_Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.4053_x-ww_e6967989 in 2 tries

SXS: RtlGetAssemblyStorageRoot() unable to resolve storage map entry. Status = 0xc0150004

--------------------------------------------------------------------------------------------------------------------------------------------------

- From the above it is clear that its failing to resolve the storage root for assembly

"Unable to resolve storage root for assembly directory x86_Microsoft.VC80.CRT 8.0.50727.4053"

Microsoft.VC80.CRT_1fc8b3b9a1e18e3b_8.0.50727.4053 points to ------> Microsoft Visual C++ 2005 version 8.0.50727.4053

So it’s clear that “Microsoft.VC80.CRT Version 8.0.50727.4053” is missing therefore we downloaded and installed the following:

“Microsoft Visual C++ 2005 Service Pack 1 Redistributable Package ATL Security Update".

http://www.microsoft.com/downloads/details.aspx?familyid=766a6af7-ec73-40ff-b072-9112bab119c2&displaylang=en

- After this we should be able to successfully install the SQL Server 2005 & apply the service pack.

Ref: http://blogs.msdn.com