The Service Broker in database "<Database Name>" cannot be enabled because there is already an enabled Service Broker with the same ID.

You receive the below error when you try to enable service broker on a database.

Msg 9772, Level 16, State 1, Line 1 The Service Broker in database "<Database Name>" cannot be enabled because there is already an enabled Service Broker with the same ID. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

This usually happens

1. When the service broker is enabled on a database and it is overwritten and then you try to enable the service broker on it again
2. When the service broker is enabled on a database and it is renamed and another database with the same name is created and then you try to enable the service broker on it again.

To fix this error, run the below command which will generate the new ID for the service broker.

ALTER DATABASE <Database Name> SET NEW_BROKER

Different ways to make a table read-only

There may be many cases where a tables needs to be made as read-only in order to maintain the existing data. This can be achieved using many ways and out of which the common methods are

• Deny Permission
• Put the table on read only file group
• Create trigger instead of insert, update, delete
• Make the database read-only
• Create a Column Store Index

When was a Database Deleted and by whom

Here is a T-SQL query which gives information on who and when was a database deleted.

SELECT name AS EventName, DatabaseName, StartTime, LoginName
    FROM sys.traces T CROSS Apply 
 fn_trace_gettable(CASE WHEN CHARINDEX('_', [path]) <> 0
                           THEN SUBSTRING(PATH, 1, CHARINDEX('_', T.[path]) - 1) + '.trc'
                           ELSE [path]
                         END, max_files) TT
    JOIN sys.trace_events TE ON TT.EventClass = TE.trace_event_id
WHERE name like '%Deleted%' 
 AND ObjectName IS NULL 
 AND EventSubClass = 1
ORDER BY StartTime DESC 

Size of a Backup is more than the Database

Recently one of my Junior DBA came up with a question to me relating to the size of the Backups.
The question was, How will the size of the backup be more than the database size?

We all know that the size of the backup will be less than or equal to the size of the database and no chance of increasing.
So what could be wrong in this case?

Find list of Reserved Keywords used as column names or table names

It is always good not to use any reserve keywords as any of the user objects.
If you suspect that your database has reserved keywords as column names or table names then the below query will help you find out.

Use <DatabaseName> 
GO 
DECLARE @ReservedKeyWords Table (KeyWords VARCHAR(100))
INSERT INTO @ReservedKeyWords
SELECT 'ADD' UNION
SELECT 'EXTERNAL' UNION
SELECT 'PROCEDURE' UNION
SELECT 'ALL' UNION
SELECT 'FETCH' UNION
SELECT 'PUBLIC' UNION
SELECT 'ALTER' UNION
SELECT 'FILE' UNION
SELECT 'RAISERROR' UNION
SELECT 'AND' UNION
SELECT 'FILLFACTOR' UNION
SELECT 'READ' UNION
SELECT 'ANY' UNION
SELECT 'FOR' UNION
SELECT 'READTEXT' UNION
SELECT 'AS' UNION
SELECT 'FOREIGN' UNION
SELECT 'RECONFIGURE' UNION
SELECT 'ASC' UNION
SELECT 'FREETEXT' UNION
SELECT 'REFERENCES' UNION
SELECT 'AUTHORIZATION' UNION
SELECT 'FREETEXTTABLE' UNION
SELECT 'REPLICATION' UNION
SELECT 'BACKUP' UNION
SELECT 'FROM' UNION
SELECT 'RESTORE' UNION
SELECT 'BEGIN' UNION
SELECT 'FULL' UNION
SELECT 'RESTRICT' UNION
SELECT 'BETWEEN' UNION
SELECT 'FUNCTION' UNION
SELECT 'RETURN' UNION
SELECT 'BREAK' UNION
SELECT 'GOTO' UNION
SELECT 'REVERT' UNION
SELECT 'BROWSE' UNION
SELECT 'GRANT' UNION
SELECT 'REVOKE' UNION
SELECT 'BULK' UNION
SELECT 'GROUP' UNION
SELECT 'RIGHT' UNION
SELECT 'BY' UNION
SELECT 'HAVING' UNION
SELECT 'ROLLBACK' UNION
SELECT 'CASCADE' UNION
SELECT 'HOLDLOCK' UNION
SELECT 'ROWCOUNT' UNION
SELECT 'CASE' UNION
SELECT 'IDENTITY' UNION
SELECT 'ROWGUIDCOL' UNION
SELECT 'CHECK' UNION
SELECT 'IDENTITY_INSERT' UNION
SELECT 'RULE' UNION
SELECT 'CHECKPOINT' UNION
SELECT 'IDENTITYCOL' UNION
SELECT 'SAVE' UNION
SELECT 'CLOSE' UNION
SELECT 'IF' UNION
SELECT 'SCHEMA' UNION
SELECT 'CLUSTERED' UNION
SELECT 'IN' UNION
SELECT 'SECURITYAUDIT' UNION
SELECT 'COALESCE' UNION
SELECT 'INDEX' UNION
SELECT 'SELECT' UNION
SELECT 'COLLATE' UNION
SELECT 'INNER' UNION
SELECT 'SEMANTICKEYPHRASETABLE' UNION
SELECT 'COLUMN' UNION
SELECT 'INSERT' UNION
SELECT 'SEMANTICSIMILARITYDETAILSTABLE' UNION
SELECT 'COMMIT' UNION
SELECT 'INTERSECT' UNION
SELECT 'SEMANTICSIMILARITYTABLE' UNION
SELECT 'COMPUTE' UNION
SELECT 'INTO' UNION
SELECT 'SESSION_USER' UNION
SELECT 'CONSTRAINT' UNION
SELECT 'IS' UNION
SELECT 'SET' UNION
SELECT 'CONTAINS' UNION
SELECT 'JOIN' UNION
SELECT 'SETUSER' UNION
SELECT 'CONTAINSTABLE' UNION
SELECT 'KEY' UNION
SELECT 'SHUTDOWN' UNION
SELECT 'CONTINUE' UNION
SELECT 'KILL' UNION
SELECT 'SOME' UNION
SELECT 'CONVERT' UNION
SELECT 'LEFT' UNION
SELECT 'STATISTICS' UNION
SELECT 'CREATE' UNION
SELECT 'LIKE' UNION
SELECT 'SYSTEM_USER' UNION
SELECT 'CROSS' UNION
SELECT 'LINENO' UNION
SELECT 'TABLE' UNION
SELECT 'CURRENT' UNION
SELECT 'LOAD' UNION
SELECT 'TABLESAMPLE' UNION
SELECT 'CURRENT_DATE' UNION
SELECT 'MERGE' UNION
SELECT 'TEXTSIZE' UNION
SELECT 'CURRENT_TIME' UNION
SELECT 'NATIONAL' UNION
SELECT 'THEN' UNION
SELECT 'CURRENT_TIMESTAMP' UNION
SELECT 'NOCHECK' UNION
SELECT 'TO' UNION
SELECT 'CURRENT_USER' UNION
SELECT 'NONCLUSTERED' UNION
SELECT 'TOP' UNION
SELECT 'CURSOR' UNION
SELECT 'NOT' UNION
SELECT 'TRAN' UNION
SELECT 'DATABASE' UNION
SELECT 'NULL' UNION
SELECT 'TRANSACTION' UNION
SELECT 'DBCC' UNION
SELECT 'NULLIF' UNION
SELECT 'TRIGGER' UNION
SELECT 'DEALLOCATE' UNION
SELECT 'OF' UNION
SELECT 'TRUNCATE' UNION
SELECT 'DECLARE' UNION
SELECT 'OFF' UNION
SELECT 'TRY_CONVERT' UNION
SELECT 'DEFAULT' UNION
SELECT 'OFFSETS' UNION
SELECT 'TSEQUAL' UNION
SELECT 'DELETE' UNION
SELECT 'ON' UNION
SELECT 'UNION' UNION
SELECT 'DENY' UNION
SELECT 'OPEN' UNION
SELECT 'UNIQUE' UNION
SELECT 'DESC' UNION
SELECT 'OPENDATASOURCE' UNION
SELECT 'UNPIVOT' UNION
SELECT 'DISK' UNION
SELECT 'OPENQUERY' UNION
SELECT 'UPDATE' UNION
SELECT 'DISTINCT' UNION
SELECT 'OPENROWSET' UNION
SELECT 'UPDATETEXT' UNION
SELECT 'DISTRIBUTED' UNION
SELECT 'OPENXML' UNION
SELECT 'USE' UNION
SELECT 'DOUBLE' UNION
SELECT 'OPTION' UNION
SELECT 'USER' UNION
SELECT 'DROP' UNION
SELECT 'OR' UNION
SELECT 'VALUES' UNION
SELECT 'DUMP' UNION
SELECT 'ORDER' UNION
SELECT 'VARYING' UNION
SELECT 'ELSE' UNION
SELECT 'OUTER' UNION
SELECT 'VIEW' UNION
SELECT 'END' UNION
SELECT 'OVER' UNION
SELECT 'WAITFOR' UNION
SELECT 'ERRLVL' UNION
SELECT 'PERCENT' UNION
SELECT 'WHEN' UNION
SELECT 'ESCAPE' UNION
SELECT 'PIVOT' UNION
SELECT 'WHERE' UNION
SELECT 'EXCEPT' UNION
SELECT 'PLAN' UNION
SELECT 'WHILE' UNION
SELECT 'EXEC' UNION
SELECT 'PRECISION' UNION
SELECT 'WITH' UNION
SELECT 'EXECUTE' UNION
SELECT 'PRIMARY' UNION
SELECT 'WITHIN GROUP' UNION
SELECT 'EXISTS' UNION
SELECT 'PRINT' UNION
SELECT 'WRITETEXT' UNION
SELECT 'EXIT' UNION
SELECT 'PROC'

SELECT OBJECT_NAME(object_id) AS TableName
         ,name AS ColumnName
         ,column_id AS ColumnID 
FROM sys.columns WHERE object_id in (SELECT object_id FROM sys.objects WHERE type_desc='USER_TABLE')
AND name IN (select KeyWords from @ReservedKeyWords) 

SELECT name as TableName from sys.objects  WHERE type_desc='USER_TABLE' 
AND name IN (select KeyWords from @ReservedKeyWords) 

Mockup data - 70% off this week for SansSQL Readers

What is MockupData

MockupData is a a software designed for Windows that generates a large quantity of realistic data for testing and demonstration purposes. Names will look like names, addresses will look like addresses, and phone numbers will look like phone numbers.

Why MockupData?

First off, large quantities of realistic data make test runs more realistic than just a handful of lines filled with random gibberish. Functional testing catches more bugs, saving you and your company both time and money. Plus filling your databases with a large volume of data allows for stress and performance testing to take place.

Secondly, real looking data makes more sense to anyone inspecting the application. Testing teams and company representatives will love seeing endless lines of real data, rather than a couple lines of random keystrokes. It simply makes everyone’s job easier.

MockupData will always be able to populate your databases when you have no previous information available (such as for a system in development) or privacy restrictions prevent you from using current information.

Finally, if you ever find yourself presenting your software to potential clients, you won’t want to pass up this software. How many clients have passed on your product simply because it had a “raw”, “unfinished” look to it due to fake data?

MockupData will help you make the best impression when selling your software to businesses. They want to see a finished product. They want to see what the software will look like with thousands of their clients inputted into the system. You simply can’t show them realistic results with fake data.

Click here to read more about MockupData
To get the Offer code write to sandeshsegu@sanssql.com. This exclusive Offer is valid till December 8th, 2013.

Upgrade Error - Valid Database compatibility level and successful connection rule

Recently when I was trying to upgrade an SQL Server 2008 R2 instance to SQL Server 2012, I was presented with an error during the upgrade rules validation.
The error message was, "The report server database is not a supported compatibility level or a connection cannot be established."

When I checked the reporting server databases, the databases had the right compatibility level.

Then I found that the SQL Server Name defined in the Reporting services configuration manager was not existing. This caused the validation rule to fail.

After changing the SQL Server Name to right server name, all the validations passed and it let me upgrade the SQL Server Instance. 

Change SSAS Deployment mode from multidimensional to tabular mode without reinstalling Analysis Services

It so happens that sometimes minds change easily and things needs to be done without reinstalling.
One such case is changing the Deployment mode of SSAS from multidimensional to tabular mode in SQL Server 2012.
To change the deployment mode

• Backup the multidimensional Analysis services databases on the instance (if any)
• Detach the multidimensional Analysis services databases from the instance (if any). These databases will not be usable in tabular mode
• Navigate to the path "<Install Location>:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config" and backup the file "msmdsrv.ini"
• Open the file "msmdsrv.ini" and change the value of DeploymentMode to 2.
  0 - Multidimensional
  1 - SharePoint
  2 - Tabular
  
• Re-Start the SQL Server Analysis services 

SSAS multidimensional mode - Before Change

SSAS tabular mode - After Change

Error while installing SQL Server 2012 - Access to the path 'C:\Program Files\Microsoft SQL Server\110\License Terms\SQLServerDenaliCTP_License_EVAL_1033.rtf' is denied.

When installing SQL Server 2012 you might run into an error which says "Access to the path 'C:\Program Files\Microsoft SQL Server\110\License Terms\SQLServerDenaliCTP_License_EVAL_1033.rtf' is denied."

To workaround this error, browse to the path "C:\Program Files\Microsoft SQL Server\110\License Terms\" and change the property of file "SQLServerDenaliCTP_License_EVAL_1033.rtf" to remove "Read-Only" and then click "Retry" and the installation will continue.

When was my database last taken Offline or Online

Here is a T-SQL script which tells when and who took the database offline or online recently.
This script utilizes the default trace and if the trace is reset after the database went offline or online then you have change the trace file path and name in the script.

DECLARE  @DBNAME nvarchar(100)
  ,@FileName nvarchar(max)
  ,@spid int
  ,@LogDate Datetime
  ,@Status nvarchar(10)
  
SET @DBNAME = 'AdventureWorks2008R2' -- Change DB Name
SET @Status = 'OFFLINE' --[OFFLINE or ONLINE]
SELECT @FileName=[path] FROM sys.traces WHERE is_default=1

DECLARE @ErrorLogTable table (Logdate datetime, ProcessInfo nvarchar(10), [Text] nvarchar(max))

INSERT INTO @ErrorLogTable
EXEC xp_readerrorlog 0,1, @Status, @DBNAME, NULL, NULL, 'desc'

SELECT TOP 1 @spid=cast(SUBSTRING(ProcessInfo,5,5) AS int)
   ,@LogDate=cast(Logdate AS nvarchar) 
FROM @ErrorLogTable

SELECT DatabaseID, DatabaseName, HostName, ApplicationName, LoginName, StartTime
FROM sys.fn_trace_gettable( @FileName, DEFAULT )
WHERE spid=@spid and DatabaseName=@DBNAME and CAST(StartTime AS nvarchar)=@LogDate

SQL Server Replication - Configuring Peer-to-Peer Replication

The first step in configuring the Peer-to-Peer replication is to configure the distribution on all the involved SQL Server instances.
To configure the distributor, follow the steps posted here.
Once the distributor is configured on all the involved instances, we need to configure the transactional publication.
To configure the publisher, follow the steps posted here.
Once the publisher is configured, we need to enable the peer-to-peer replication for this publisher.

Data source name not found and no default driver specified - SSIS Error

Sometimes you might get the below error when you are trying to run a SSIS package which is trying to connect to a 32 bit system from a 64 bit system/driver.

[DataReader Source [320]] Error: System.Data.Odbc.OdbcException: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConnection(Object pTransaction) at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)

To fix this error:

1. Go to "Solution Explorer"
2. Right-Click on the Project and choose "Properties"
3. Expand "Configuration Properties" and choose "Debugging"
4. Set the "Run64BitRuntime" option to "False"
5. Click "ok" and save the project

T-SQL to get the list of objects modified in x number of days

USE SansSQL; -- Change the Database Name
GO
DECLARE @Days int
SET @Days=300 -- Specify the number of days
SELECT name AS ObjectName
  ,SCHEMA_NAME(schema_id) AS SchemaName
  ,type_desc AS ObjectType
  ,create_date AS ObjectCreatedOn
  ,modify_date As ObjectModifiedOn
FROM sys.objects
WHERE modify_date > GETDATE() - @Days
ORDER BY modify_date;
GO

T-SQL query to get the Full text catalogs associated with a database

Here is a T-SQL query to get the Full text catalogs associated with a particular database

Use <Database Name>
SELECT DB_Name() AS DatabaseName, name 
FROM sys.fulltext_catalogs;
GO

To get the Full text catalogs associated with all databases in a instance, use the above query with sp_msforeachdb

Exec sp_msforeachdb '
USE [?];
SELECT DB_Name() AS DatabaseName, name 
FROM sys.fulltext_catalogs;'
GO

Connect to SSIS service on machine failed: Error loading type library/DLL

When connecting to an SSIS instance on a newly installed system you might be sometimes presented with the error "Connect to SSIS service on machine <Machine Name> failed: Error loading type library/DLL"

Install MongoDB on windows

Installing MongoDB on windows is very easy and happens in 3 simple steps.
MongoDB is self-contained and does not have any other system dependencies. You can run MongoDB from any folder you choose.

One: Download the MongoDB binaries from its website.

There are 3 builds available for windows

1. MongoDB for Windows 2008 R2 - This runs on only Windows Server 2008 R2, Windows 7 64-bit, and newer versions of Windows and takes advantage of recent enhancements to the Windows Platform and cannot operate on older versions of Windows.
2. MongoDB for Windows 64-bit - This runs on 64-bit version of windows operating system
3. MongoDB for Windows 32-bit - This runs on 32-bit version of windows operating system

Small data in a Big Data is a useful data

Every bit of data has its own space – be it BIG or SMALL!

Considering the universe which is HUGE, Earth is one small bit of it and the space required for individual like us is one tiny tot. That doesn't stop us from dreaming BIG or living BIG in all.

In the database technology world today the most buzzing word around us is BIG Data – so this is like Universe with loads of information which is formed of million-billion bit of tiny tots.  So, in that way every bit of data is informative talking about something or the other.

One Such practical example wherein we see such usage of data, be it BIG or SMALL is Facebook. Facebook introduced a concept known as Graph Search (concept of breaking huge chunk of data into smaller bits making it more consumable). The level Graph search has adopted is getting to each individual information. This data is being consumed to give back the ‘personal belongingness’ for each individual – just because Graph search is helping us to get there. This ultimately means Big Data has showcased what every bit of data can be – BIG or SMALL!

Many companies have already adopted this technology, with more and more companies are moving towards this technology making use of the abundant data around us.

We could see such adoption at NASA for analyzing and forecasting of various extra celestial activities. So, data is important in every possible way! The useful data or the end result we see might be small but without the big data it is impossible to derive this tiny useful data which makes a drastic changeover.

BIG REVOLUTION is here! Embrace it!!

What is MongoDB?

On this auspicious day of Diwali, Let's learn something new.
At SansSQL, we are always committed to Learn and Share new things and technologies.

What is MongoDB?

MongoDB is a cross-platform document-oriented database system.
It is an Open source database and classified as a "NoSQL" database. This provides high performance, high availability, and easy scalability.

Importance of DAILY CHECK LIST!

Here I am, back from long holidays to my blogs! I was thinking of writing in which happens to be part of any DBA on production support (it can different levels as such).  So, have chosen to blog the importance of Daily Check list.

Daily check list:

The following is purely what we have adopted as part of the routine daily check. The check can be done at one time everyday and at a convenient time so that we don't miss anything.

First thing, we have selected list of servers which are very critical for the business and have to be part of check list.

Replication features in various editions of SQL Server 2005/2008

Reference Link:

http://blogs.msdn.com/b/repltalk/archive/2011/01/03/replication-features-in-various-editions-of-sql-server-2005-2008.aspx

Crisp information on SQL Server Replication Features availability and limitations when using SQL Server 2005 and SQL Server 2008. I am sure this information is still useful in environments using older versions like SQL 2005 and 2008.

Snapshot from the above link shows the following... happy reading!

Feature	Express	Express Advanced	Workgroup	Standard	Enterprise	Web	Evaluation	Developer
Merge Replication	Subscriber only	Subscriber only	<= 25 subscribers	Y	Y	Subscriber only	Y	Y
Transactional / Snapshot  Replication	Subscriber only	Subscriber only	<= 5 subscribers	Y	Y	Subscriber only	Y	Y
P-P Transactional Replication	N	N	N	N	Y	N	Y	Y
Oracle Publishing	N	N	N	N	Y	N	Y	Y

SQL Server Replication - Configuring Subscriber

In my previous post "SQL Server Replication - Configuring Publisher", we have seen how to configure the Publisher with articles and the next step in configuring replication is to configure Subscriber.

• Expand the SQL Server Instance and then "Replication"
• Right Click on "Local Subscriptions" and choose "New Subscription"
• Click "Next"
  

SQL Server Replication - Configuring Publisher - Transactional Replication

In one my previous post "SQL Server Replication - Configuring Distributor", we have seen how to configure the distributor database. In this post, we will see how to configure the publisher for transactional replication.

• Expand the SQL Server Instance and then "Replication"
• Right Click on "Local Publications" and choose "New Publication"
• Click Next in the welcome screen
  

Login Changes History

This query will gives the changes done to the logins in a SQL Server Instance.
This is based on the currently available Default Trace for that particular instance.

DECLARE @Tracepath nvarchar(max)
SELECT @Tracepath= [path] FROM sys.traces

SELECT TraceEvents.name AS [What Happened]
   ,SubClass.subclass_name AS [What Action]
   ,TraceTable.TargetLoginName AS [Who Was Affected]
   ,TraceTable.ApplicationName AS [From Which Application]
   ,TraceTable.LoginName AS [Who Did it]
   ,TraceTable.StartTime AS [At What Time]
FROM sys.fn_trace_gettable(@Tracepath, DEFAULT) TraceTable
JOIN sys.trace_Events TraceEvents ON TraceTable.EventClass = TraceEvents.trace_event_id
JOIN sys.trace_subclass_values SubClass ON SubClass.trace_event_id = TraceEvents.trace_event_id
AND SubClass.subclass_value = TraceTable.EventsubClass
WHERE TraceEvents.name like '%login%'
GO

T-SQL Query to find the list of databases mirrored

Here is an T-SQL Query to find the list of all databases mirrored in a particular SQL Server instance.

SELECT [Database Name] = DB.name
   ,[Recovery Model] = DB.recovery_model_desc 
   ,[Mirroring Status] = CASE WHEN DM.mirroring_state is NULL THEN 'Not Mirrored' ELSE 'Mirrored' END
FROM sys.databases DB, sys.database_mirroring DM
WHERE DB.database_id=DM.database_id
ORDER by name

T-SQL Query to display all currently executing user commands

Here is a T-SQL Query to display all currently executing user commands on an SQL Server Instance

SELECT r.session_id
            ,status
            ,SUBSTRING(qt.text,r.statement_start_offset/2, 
                  (CASE WHEN r.statement_end_offset = -1 
                  THEN len(convert(nvarchar(max), qt.text)) * 2 
                  ELSE r.statement_end_offset end - r.statement_start_offset)/2) 
            as query_text
            ,qt.dbid
            ,qt.objectid
            ,r.cpu_time
            ,r.total_elapsed_time
            ,r.reads
            ,r.writes
            ,r.logical_reads
            ,r.scheduler_id
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE r.session_id > 50
ORDER BY r.scheduler_id, r.status, r.session_id

T-SQL Query to find all members in server role

Here is T-SQL script to find all the members present in the SQL Server roles.

SELECT SUSER_NAME(members.role_principal_id) AS [ServerRole]
   ,logins.name AS 'RoleMember'
   ,'EXEC sp_addsrvrolemember ''' +logins.name+''', '''+
   SUSER_NAME(members.role_principal_id)+'''' AS [Command to add role members]
FROM sys.server_role_members members, sys.server_principals logins
WHERE members.role_principal_id >=3 AND members.role_principal_id <=10 AND
members.member_principal_id = logins.principal_id
and logins.name <>'sa'

Generate ASCII table

Here is T-SQL Query to generate ASCII table

DECLARE @ASCIITable TABLE 
       (AsciiChar nvarchar(2), CharNum int)
DECLARE @CNT INT
SET @CNT = 1

WHILE @CNT < 125
BEGIN
       INSERT INTO @ASCIITable
       SELECT CHAR(@CNT), @CNT
       SET @CNT = @CNT + 1
END

SELECT * FROM @ASCIITable

Configuring Database Mirroring and Testing Mirroring Failover

Slno Steps Stage Remarks   1 Set the Database (Those Involved in Mirroring)  to Readonly Pre Configuration While Configuring on production environment, set the involved database to Read-Only to avoiding data modification during configuration   2 Backup Databases in Source Pre Configuration   3 Move Backups to local Drives on Destination Server Pre Configuration   4 Restore Backups on Mirror server with No recovery Pre Configuration Execute AS USER='dbo' (To Make the Database Owner as 'sa') Recommended to Use Scripts while restoring. Make Use of the above Query in order to restore the database with 'sa' as owner when logged in as a different User. Ex: EXECUTE AS USER='dbo' GO Restore Database XYZ from Disk='C:\....' ....   5 Manually Change the DB Owner to "sa" Pre Configuration If not Using Scripts in Step 4 Ex: Use DBName GO Exec sp_changedbowner 'sa'   6 Make sure that the Databases are in FULL Recovery Mode Pre Configuration Full recovery mode against the principal instance databases   7 Script out required logins from Principal server using sp_help_revlogin (More Information @ http://support.microsoft.com/kb/918992) and Create on VM's (Principal and Mirror Pre Configuration This is done to match the SID's for SQL Users on both Principal And Mirror   8 Make sure that the Default Database for the Logins involved in mirroring should be 'Master' Pre Configuration   9 Make Sure SQL Services are running under the same service account Pre Configuration   10 Right-Click on the Principal Database and Go to properties Mirror Configuration     10.A Go to "Mirroring" Tab Mirror Configuration     10.B Click on "Configure Security" Mirror Configuration     10.C Choose "Yes" In the Include Witness Server Page Mirror Configuration     10.D In the Next Page, Check the "Witness Server Instance" option Mirror Configuration     10.E Choose the Port Number and Chage the Endpoint Name For the Principal Server. Mirror Configuration     10.F In the Next Page, Connect to the Mirror Server and Choose the appropriate Port Number and Endpoint Name for the Mirror Server. Mirror Configuration     10.G In the Next Page, Connect to the Witness Server and Choose the appropriate Port Number and Endpoint Name for the Witness Server. Mirror Configuration     10.H In the Next Page, provide the Service Account Names under which mirroring should be configured. Mirror Configuration Preferably a Service Account     10.I Once step 10.H is completed, click Finish to start the configuration of Mirroring Mirror Configuration     10.J Once the Mirroring is setup, it will prompt for starting the Mirroring. Choose "Start Mirroring" if you are sure that the all the Information that is shown in the Popup box is correct. If you find any discrepancies choose "Do not Start Mirroring" and then make the necessary changes and then Start the mirroring. Mirror Configuration     10.K In the Database Properties Mirroring Page, the status should show "Synchronized: the databases are fully synchronized" if there are no issues encountered. Mirror Configuration     10.L Once Step 10.K is confirmed, Click "OK" on the Database Properties Mirroring Page Mirror Configuration   12 Make Sure That the Mirror Database status is "Mirror, Synchronized / Restoring ..." Post Configuration   13 Give appropriate access to the DB Users in Principal Post Configuration   14 Give appropriate access to the DB Users in Mirror Post Configuration FailOver must be done for giving access to the Mirror Database   15 To Check the Mirroring Status, Right-Click on the Principal Database and go to "Tasks" and choose "Launch Database Mirroring Monitor …" Post Configuration   16 Register The Mirrored Databases by connecting to the appropriate Instance of SQL Server Post Configuration   18 Go to "Mirroring" Tab Testing / Test Case 1   19 And Click "FailOver" Testing / Test Case 1   20 In the PopUp Window, Choose "Yes" Testing / Test Case 1   21 Once the failover is complete, Login to the Mirror (Now Principal) using a SQL User and run few queries against the database Testing / Test Case 1   22 Change the Default DB of a SQL Login to the Mirrored Database. Testing / Test Case 2   23 Initiate Failover Testing / Test Case 2   24 Once the failover is complete, Login to the Mirror (Now Principal) using the SQL User and run few queries against the database Testing / Test Case 2   25 Shut Down SQL Services on the Principal Server (Right Click on the Server and Choose Stop Services) Testing / Test Case 3   26 Once the Services is stopped, Login to the Mirror  (Now Principal) and run few queries against the database Testing / Test Case 3   27 Hard Shut Down SQL Services on the Principal Server (Execute SHUTDOWN WITH NO WAIT) Testing / Test Case 4 Execute The Command Execute SHUTDOWN WITH NO WAIT   28 Once the Services is stopped, Login to the Mirror  (Now Principal) using a SQL User and run few queries against the database Testing / Test Case 4   29 Stop SQL Services on Witness Server. Testing / Test Case 5 There should be No Effect on Mirroring   30 Pause the Mirroring Testing / Test Case 6   31 Stop the Services on Principal Testing / Test Case 6   32 Rename the Data file of the Principal database Testing / Test Case 6   33 Start the Services. Testing / Test Case 6 Does not Fail over to the Mirror.   34 Stop the Services on Principal Testing / Test Case 6 To Bring back to Original State   35 Rename the Data file of the Principal database to original Name Testing / Test Case 6   36 Start the Services. Testing / Test Case 6 Principal and Mirror databases are still on the Same Servers.