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.

Unable to start SQL Server Service on a cluster instance

When you try to bring online the SQL Server cluster service, it might fail with the error "Login timeout expired".

In my case the SQL Server version was SQL Server 2005 and when I failover to Node1 it was working fine, however when I failback to Node2, it was failing with this error.

This is unusually right?
It works on one node but not on the other node.

SQL Server Agent Could not be started (reason: Error creating new session) - Error 15247

Recently, I was working a server which is newly built and when I tried to start the SQL Server Agent, it failed saying that the Agent service could not be started on the local computer.
When I investigate more in the event viewer i found a message which says "SQL Server Agent Could not be started (reason: Error creating new session)".

Change SQL Server Collation without rebuilding system databases

How many of you want to change the SQL Server Collation without rebuilding the system databases?

In this post, I will explain a method using which the server collation can be easily changed and is through a undocumented start up parameter.
Since this is undocumented, I highly recommend to use this with extra care and with your own risk.