Firewall configuration for a SQL Server 2008 Analysis Services Named Instance

1. Open TCP port 2382. This port is used by SQL Browser to handle instance name resolution for OLAP.

2. If Analysis Services is configured for dynamic port (default configuration), then add Analysis Services to firewall exception list.Find the path name of msmdsrv.exe, usually it will be below -

C:\Program Files\Microsoft SQL Server\MSAS10.{Instance Name}\OLAP\bin\msmdsrv.exe

Add above to the firewall exception list. This will enable AS to communicate with clients on any port it’s running on through SQL Browser.

If Analysis Services is configured to listen on a fixed port, then instead of adding msmdsrv.exe to exclusion list, open the inbound communication for the TCP port.

Firewall configuration for SQL 2008/2005 Named Instance

1. Open TCP and UDP port 1434. UDP port 1434 is used by SQL Browser to handle instance name resolution for SQL Server. TCP Port 1434 is used for SQL server dedicated Admin connections.

2. If SQL Server has been configured for dynamic port (Default configuration) then add SQL server service to firewall exception list. Find the path name of sqlserver.exe, usually it will be below (Standard install)-

C:\Program Files\Microsoft SQL Server\MSSQL10.{Instance Name}\MSSQL\Binn\sqlservr.exe

Add above to the firewall exception list. This will enable SQL server to communicate with clients on any port it’s running on through SQL Browser.

If SQL Server is configured to listen on a fixed port, then instead of adding sqlservr.exe to the exception list, you can open the Inbound communication for the TCP port.

Dedicated Admin Connection feature for SQL 2005/2008

DBAs would run into situations where SQL server becomes unresponsive to the point that it doesn't allow new connections. Since we cannot login , we won't be able to figure out the rouge query (or queries :-)) that caused the problem.

SQL 2008 & 2005 allow you to connect to the sql server even under these circumstances using a Dedicated Admin Connection (or DAC).

To use it, you would need to first enbale it using below statements -

sp_configure 'REMOTE ADMIN CONNECTIONS', 1;
GO
RECONFIGURE;
GO

Then connect to the server using SSMS new query Or SqlCmd. You will have to use 'ADMIN:' before the server name. For example if your server name is xyz,
then for DAC connection the server name would be ADMIN:xyz

DAC connection doesn't work with SSMS object explorer and only 1 DAC connection is allowed at a time.

DMV Query For Current Executing SQL Statement

Often times for troublshooting a running big batch of query (for example a migration script) you would need to know the current sql statement executing. You can use below query after identifying the session id (spid) for the batch :

SELECT SUBSTRING(t.[text],
1+(r.statement_start_offset/2),
1+(
(
CASE r.statement_end_offset
WHEN -1 THEN DATALENGTH(t.[text])
ELSE r.statement_end_offset
END - r.statement_start_offset
)/2
)
)
FROM
sys.dm_exec_sessions s LEFT JOIN
sys.dm_exec_requests r ON (r.session_id = s.session_id) CROSS APPLY
sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id = ? /* Session id that you need to investigate */

Agents Runnig fine but red X in replication monitor

Recently came accross a problem in a non prod environment where replication replication monitor was marked with red X. It was a SQL 2000 server and replication agents were running fine without error. Server had undergone restores from prod backups and replication was rebuilt after the restores. One of my collegue found the solution in http://www.replicationanswers.com.

Running sp_MSload_replication_status did the trick.

The link has solution for SQL 2005 as well.

SQL 2008 Replication - debugging distributor agent error message

Check details of the replication error using replication monitor. For example, in case of a general no row found error at subscriber, we would find error description of something like below -

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x000019A0000032DF000800000000, Command ID: 1)

Error messages:
• The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)
Get help: http://help/20598
Error doesn’t give the name of the table or the command text that resulted in this failure. Below are two ways to get this
information –

1. sp_browsereplcmds

In order to use this, we will need to know values for @xact_seqno_start ,@xact_seqno_end ,@publisher_database_id ,@article_id ,@command_id
@xact_seqno_start and @xact_seqno_end would be the same as we would be using @command_id.

Error message doesn’t give us the @publisher_database_id or @article_id, so we need to find it out.

Below querries should give us @article_id and @publisher_database_id

use distribution

go

select * from MSrepl_commands
where command_id = 1 /* from replication monitor*/
go
select * from MSpublisher_databases

once we get all variables we can execute the proc

sp_browsereplcmds @xact_seqno_start = '0x000019a0000032e50008'
,@xact_seqno_end = '0x000019a0000032e50008'
,@publisher_database_id = 1
,@article_id = 1
,@command_id= 1

In the above example, I get below value for command column -

{CALL [dbo].[sp_MSupd_dboPerson] (,'test',,,,,,,,,72328,0x0200)}

Person is the table name (part of the replication Proc name), 'test' is the changed value of second column for Primary Key Value of 72328

2.Using ‘OutputVerboseLevel’ and ‘Output’ options of Distribution agent

We can set distribution agent to log to a file with ‘OutputVerboseLevel’ value of ‘2’. This will log both error messages as progress report. I would suggest using this for debugging purpose only. ‘0’ doesn’t
seem to give detailed error message in SQL 2008 (RTM).
For the Distribution Job ‘Run Agent’ step add below two options
-Output C:\ReplDistbOutput.txt -OutputVerboseLevel 2

Here’s the portion of the log file -

2009-02-12 00:49:58.668 Last transaction timestamp: 0x000019a0000032df000800000000
Transaction seqno: 0x000019a0000032e50008
Command Id: 1
Partial: 0
Type: 30
Command: <>
2009-02-12 00:49:58.684 sp_MSget_repl_commands timestamp returned: 0x0x000019a0000032e500085a87a1e7, 2, local rowcount: 2
2009-02-12 00:49:58.699
42000 The row was not found at the Subscriber when applying the replicated command. 20598
2009-02-12 00:49:58.699 sp_MSget_repl_commands timestamp value is: 0x0x000019a0000032e5000800000000
2009-02-12 00:49:58.715
42000 The row was not found at the Subscriber when applying the replicated command. 20598
2009-02-12 00:49:58.762
Failed command = {CALL [dbo].[sp_MSupd_dboPerson] (,?,,,,,,,,,?,0x0200)} {CALL [dbo].[sp_MSupd_dboPerson] (,?,,,,,,,,,?,0x0200)}
2009-02-12 00:49:58.777 Parameterized values for above command(s): {{'dl1fh1l', 72328}, {'dl1fh111l', 72328}}
2009-02-12 00:49:58.809 Disconnecting from Subscriber 'SHAMSH\ECM1'
2009-02-12 00:49:58.824 Disconnecting from OLE DB Subscriber 'SHAMSH\ECM1'

Using SQL 2008 Change Data Capture (CDC)

SQL 2008 has a new feature called Change Data Capture (CDC). When enabled for a table it stores before and after values of the tracked columns in a change tracking table.

1. Enabling CDC for a database

To capture data changes for a table we need to first enable change tracking at database level. Execute the following script to enable CDC for the database.

use 'YourDBName'

go

exec sys.sp_cdc_enable_db

2. Setting CDC for the table

Use sys.sp_cdc_enable_table for a table to set CDC. By default, all
columns are tracked. We can strict CDC to specific columns by specifying column names for parameter ‘@captured_column_list’

exec sys.sp_cdc_enable_table
@source_schema = 'dbo'
,@source_name = 'Person'
,@role_name = 'rptrole'
,@capture_instance = 'Person'
/* This will create a change tracking table cdc.dbo_Person_CT */
,@supports_net_changes = 1
/* Indicates if support for quering net changes are allowed or not
allow = 1 */
,@index_name = 'PK_Person_PersonID'
/* change tracking requires a unique index to track changes against*/
,@captured_column_list
= 'Address1,Address2,City,State,Zip,PhoneNo,PersonID'

/* Changes will be tracked for columns mentioned in this parameter.
Default is all*/
,@filegroup_name = 'cdc'
/*file group where cdc objects will be created It’s a good practice to
create separate filegroup for CDC.Place the filegroup in a different
Drive to minimize impact on I/O
*/
-- ,@partition_switch = 'partition_switch'

/* Indicates if switch partition command is allowed or not.
applicable to patitioned tables only*/

3. Retrieving Change

Change data for the above example would be stored in cdc.dbo_Person_CT.It can be used in conjunction with cdc.lsn_time_mapping table to retrieve data for a time period. CDC provides functions for change retrievel as well.

Get Start LSN –

sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time)

Get End LSN -

sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time)

Get net changes for the LSN range (shows only final content of a row for the range)

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Person(Start LSN, End LSN,'all');

Get all changes for the LSN range (shows all changes for a row in the LSN range)

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Person(Start LSN, End LSN,'all');

4. CDC Jobs

CDC creates a capture and cleanup job. Use the below SQL to see job configuration.

exec sys.sp_cdc_help_jobs

By default cleanup job is configured to retain up to 72 hrs changes and capture job is set to run continuously.

We can use sys.sp_cdc_change_job to modify the configuration of cdc jobs.