Mirroring – Role of Witness Server and Quorum

When a witness server is set, a mirroring session (high safety mode with automatic failover mode) needs quorum to keep the database service. A quorum is the minimal relationship among all connected servers required for synchronous database mirroring session.

Now the next question that comes in mind is about the single point of failure for witness. It is not a single point of failure because if witness fails, principal and mirror will still continue to form a quorum.

Various types of quorum are possible.

Say for example

A = Principal
B = Mirror
C = Witness

Full Quorum – Both partners and witness are included - A∩B∩C

Quorum of partners – Only the two partners are included - A∩B

Quorum with witness and partner – Witness and one of the partners are included C∩(AUB)

Quorum loses sessions

If all the servers are disconnected then the session loses quorum

Now that we know all possible types of quorum, let’s see how each one affects the database and application.

If witness is disconnected when either partner goes down, the database is unavailable since quorum cannot be formed. If the session loses quorum, then the database will not be available until the quorum is re-established.

SQL Server 2005 Database Mirroring

I have been working with database mirroring feature in SQL Server 2K5 and these are some of interesting points I have noted during setting up the process.

1. In SQL Server 2005 SP1 and later versions, database mirroring is fully supported and is made available for general production use. To use database mirroring in a production environment, upgrade to SQL Server 2005 Service Pack 1 (SP1).

Source: http://support.microsoft.com/kb/907741


2. An error occurred while starting mirroringDatabase Mirroring Transport is disabled in the endpoint configuration.(Microsoft SQL Server, Error: 1486)

Solution: Run DBCC TRACEON (1400)

3. The mirror database, "XXX", has insufficient transaction log data to preserve the log backup chain of the principal database. This may happen if a log backup from the principal database has not been taken or has not been restored on the mirror database. (Microsoft SQL Server, Error: 1478)

Solution: After the full backup on the principal, you need to backup the Transaction log on the principal server. Restore the full backup on mirror in ‘NORECOVERY MODE’ and then restore the Transaction log in ‘NORECOVERY MODE’

4. Can we use database mirroring for the purpose of transferring data from production to test or development server?

In mirroring, if the first server should become unavailable, the system will failover to the mirrored database and SQL Server, helping to ensure high availability. I don't think you want to failover to test or development box.However, you can use Database Mirroring and create a Database Snapshot against the mirror. This Database Snapshot can be periodically dropped and recreated in order to give access to more up to date information. You can use this snapshot for reports or testing.

5. Can we use Database mirroring with Log Shipping?

This MS article explains it clearly

http://msdn2.microsoft.com/en-us/library/ms187016.aspx

T-SQL Convert Hex value to Integer

--Convert Hex value to int
SELECT CONVERT(INT, 0x000FFEFF)

--Convert int value to hex
SELECT CONVERT(VARBINARY(8), 1048319)

SQL Server 2005 Access to System tables

In SQL2K5 system data is hidden in resource tables and cannot be accessed. Instead, there are number of views which can provide the information.

Catalog Views – Shows metadata that describes the objects in an instance
Information Schema Views - They have the same functionality as in 2000 Provides internal view of SQL Server meta data
Compatibility Views – These are for backward compatibility. Beware that columns that previously were undocumented typically now only have NULL or 0
Dynamic Management Views – They provide inside details of SQL Server state data, which can be used to check the health of SQL Server instance. Consists of both views and table valued functions

Also from BOL, the catalog views and the compatibility views have restricted visibility, so by default they only return information about objects to which the user has some rights.

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views
http://msdn2.microsoft.com/en-us/library/ms187997.aspx

SQL Server Management Studio – Editor Color Coding

In Management studio, when you open query editor, there is a thin yellow vertical line in the left margin for each line you type. Sometimes the line disappears and sometimes it is green in color. Did you ever notice that? If not, continue reading……

These colors represent the modifications done to the file.

Yellow –modification, not saved
Green – modification, saved
No Color – no modification

Now, can you change these colors? Ofcourse, goto Tools->Options->Environment ->Fonts and Colors. You can change the colors for various display items.

SQL Server - Find primary keys in a table

To find primary key in a given table :

Option 1: sp_pkeys 'yourtablename'

Option 2: select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where table_name = 'yourtablename' and constraint_type = 'primary key'

Option 3: sp_help 'yourtablename'

To find all primary keys in database:

Use yourdatabasename
select c.COLUMN_NAME ,c.CONSTRAINT_NAME,C.table_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where CONSTRAINT_TYPE = 'PRIMARY KEY'
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

These would work for both SQL Server 2K and SQL Server 2K.

Business Intelligence Studio - Installation error

After installing SQL Server developer edition, Business Intelligence Studio starts looking for file devenv.exe.

To resolve this issue:
1. You should make sure that Visual Studio is still installed.
2. If you didn't previously have VS installed, the BI Dev Studio installation will install a VS shell called Visual Studio Premier Partner Edition.
3. Look in Add or Remove Programs for “Microsoft Visual Studio Premier Partner Edition”. If you don't find any entry for Visual Studio go to the location for SQL Server setup and run \Setup\vs_setup.exe. (Usually in CD #2 under setup)
4. After VSShell is installed repair the BI Studio installation by running the following from the command line from the .\Tools directory: start /wait setup.exe /qb REINSTALL=SQL_WarehouseDevWorkbench REINSTALLMODE=OMUS

Import/Export between SQL 2005 and 2000

Due to architectual differences, you can not restore a database backup from higher version to Lower version [SQL 2005 to SQL 2000]. Once it is attached or restored to a 2005 instance it can not go back intact.

One question which comes into mind immediately is, what if DB is set to SQL 2000 compatibility level? Compatibility level is for client connection behavior. On-disk it is still SQL 2005.
One solution is to create the database manually, create objects and use SSIS/DTS/BCP to transfer the data.

Enable xp_cmdshell

With SQL Server 2005, xp_cmdshell is disabled by default

To enable this feature:

Option 1: Using T-SQL

--Enable the feature
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE

Option 2: SQL Server 2005 Surface Area Configuration tool

From Surface Area Configuration Tools -> Choose “Surface Area Configuration for Features” > Check “Enable xp_cmdshell”