SQL 2008 - Linked Server: Msg 7391, Level 16, State 2, Line 5

Trying to leverage a linked Server and received the following error:

OLE DB provider "SQLNCLI10" for linked server "PANEL_REMOTE" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 5
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "PANEL_REMOTE" was unable to begin a distributed transaction.

-------------------
Verify RPC and RPC out is set to True in the Linked Server properties.

Code Snippet was:

I had declared the table variable @temp2 (yada)

declare @dynamic_sql NVARCHAR(MAX)
declare @prefix NVARCHAR(MAX)
SET @prefix = N'LinkedServerName.'
SET @dynamic_sql = N'' + CHAR(13) +
' INSERT INTO @temp2 EXEC ' + @prefix + 'DBName.dbo.StoredProcName '
exec (@dynamic_sql)

Still received the error message Msg 7391, Level 16, State 2, Line 5

-------------
Changed the call to use OpenQuery...
declare @dynamic_sql NVARCHAR(MAX)
SET @dynamic_sql = N'' + CHAR(13) +
' INSERT INTO @temp ' + CHAR(13) +
' SELECT * FROM OPENQUERY(LinkedServerName, ' + '''' + 'DBName.dbo.StoredProcName ' + '''' + ')'

And it worked successfully...

SQL 2008 R2 Mirroring End Points - Different NICs

Leveraging dedicated NIC's for SQL Mirror NIC traffic (other than SQL Server system traffic).

Example:

SQL Services run on 10.X.X.Y on Principal and 10.X.X.Z on Mirror)
SQL Mirror traffic:

Mirror Node will run on 192.168.0.2
Principal Node will run on 192.168.000.1


Mirror Node:

CREATE ENDPOINT [DB_Mirror]
AUTHORIZATION [SURVEYSAMPLING\matt_fitzgerald]
STATE=STARTED
AS TCP (LISTENER_PORT = 5030, LISTENER_IP = (192.168.000.2))
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

GRANT CONNECT ON ENDPOINT::DB_Mirror TO [DOMAINNAME\sqlserverServiceAccount]

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

Principal Node

CREATE ENDPOINT [DB_Mirror]
AUTHORIZATION [SURVEYSAMPLING\matt_fitzgerald]
STATE=STARTED
AS TCP (LISTENER_PORT = 5030, LISTENER_IP = (192.168.000.1))
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

GRANT CONNECT ON ENDPOINT::DB_Mirror TO [DOMAINNAME\sqlserverServiceAccount]

-----------

Restore the database on mirror node along with one transaction log...


restore database dbname
from disk = '' WITH NORECOVERY, stats = 10

Restore log dbname from disk='' with norecovery, stats=10

--On mirror node
alter database DBNAME
set partner = 'TCP://192.168.000.1:5030'

--On Principal Node
alter database entrycheck
set partner = 'TCP://192.168.000.2:5030'

By default this will create a Sync mirror.

Map SQL Logins after restore

Finally got around to sorting out orphaned users. Create two procedures... Here it is.

Put these procedures in the master database..

The second procedure loops thru each database (using the first procedure) and populates a temp table.The select statement at the end of GetOrphanedUsers_AllDatabases simply builds the string to execute.

--Here is the first proc.
CREATE Proc [dbo].[GetOrphanedUsers]
@databasename sysname
as
set nocount on

Create table #Fitz
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

declare @sql varchar(1000)
set @sql = '
use '+@databasename+'
insert into #Fitz
(DB, OrphanedUserName, UserType)
SELECT db_name() as ''DB'', a.name AS OrphanUserName, a.type_desc AS UserType
FROM sys.database_principals a
LEFT OUTER JOIN sys.server_principals b
ON a.sid = b.sid
WHERE b.sid IS NULL
AND a.type In (''S'', ''U'', ''G'')
AND a.name NOT in (''sys'', ''INFORMATION_SCHEMA'', ''guest'')

select * from #Fitz
'

exec (@sql)
GO

----Second Proc
CREATE PROC [dbo].[GetOrphanedUsers_AllDatabases]
AS
Create table #OUsers
(DB varchar(50),
OrphanedUserName varchar(100),
UserType varchar (50))

DECLARE @databasename sysname
DECLARE DatabaseLoop CURSOR FOR
(select name from sys.databases where state=0 and database_id >4 )
OPEN DatabaseLoop
FETCH NEXT FROM DatabaseLoop into @databasename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #OUsers
exec GetOrphanedUsers @databasename
FETCH NEXT FROM DatabaseLoop into @databasename
END
CLOSE DatabaseLoop
DEALLOCATE DatabaseLoop

Select 'exec ' + DB + '..sp_change_users_login ' + '''' + 'auto_fix' + '''' + ',' + '''' + OrphanedUserName + ''''--*
from #OUsers
where orphanedUserName <> 'dbo'
order by db
GO

Trace Flag 834

We purchased new hardware (HP 980) with 32 cores, 50 GB RAM with multiple FusionIO cards (to house database files) to run SQL 2008 R2 Enterprise. We’re leveraging SQL mirroring. This physical server is the mirror node.

Long story short, after many rebuilds from the OS up, SQL performance remained degraded. Systematically, we began to rule out what was causing the issue.

At the end of the day, we added trace flag –T834 (causes SQL Server to use Microsoft Windows large-page allocations for the memory that is allocated for the buffer pool). Lock pages in memory had already been configured.

http://support.microsoft.com/kb/920093

Well, long story short, the performance of the SQL server immediately began to improve.

Interesting fact, we have dozens of other high performance SQL instances (clustered, stand-alone, etc.), (SAN storage, other FusionIO cards) and have NOT had to set the –T834 trace flag on any additional SQL instances.

If you’ve had to set the T834 flag, please let me know.

FusionIO SQL Server - Severe performance Degradation

Long story short - I run FusionIO cards in production (4 X 1.2TB DUO Cards for one logical drive and 2 X 640 DUO cards for 2 logical drives).

Well, come to find out the FusionIO cards need to placed specifically on the PCIE bus in a specific order. Our production servers (with the physical different drive layout) work without issues.

I builtout a new physical server (HP980) with FusionIO cards and performance was horrible.
So - after multiple server rebuilds - we mimicked the exact FusionIO physical card location from our primary principal node. Now the server behaves as expected.

Now, all the 1.2 TB Duo cards are sitting on Gen8 slots and the smaller 640 cards reside on the Gen4 slots.

So - if running FusionIO cards, make sure the cards in the correct Gen slot.

SQL 2008 R2 and Hyperthreading

Turn it off...

Master of the Obvious

Master of the Obvious.

On a heavy OLTP environment, I'd recommend having one tempdb database file for each physical core on the server.

So, if there are eight cores, create an additional seven tempdb database files.

If possible, split out the files across muliple disk groups.

DBCC OpenTran - No More For Me

We experienced a transaction log growing out of control. We performed multiple transaction log backup w/out much success. Clearly there was an open transaction.

Normally, I would execute dbcc opentran(). Unfortunately, this didn't return the correct SPID.

I did some digging and came across the following..

I will not be using dbcc opentran() anymore.

SELECT dd.transaction_id,
ds.session_id,
database_transaction_begin_time,
CASE database_transaction_type
WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
END database_transaction_type,
CASE database_transaction_state
WHEN 1 THEN 'The transaction has not been initialized.'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log records.'
WHEN 4 THEN 'The transaction has generated log records.'
WHEN 5 THEN 'The transaction has been prepared.'
WHEN 10 THEN 'The transaction has been committed.'
WHEN 11 THEN 'The transaction has been rolled back.'
WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted'
END database_transaction_state,
database_transaction_log_bytes_used,
database_transaction_log_bytes_reserved,
database_transaction_begin_lsn,
database_transaction_last_lsn
FROM sys.dm_tran_database_transactions dd
INNER JOIN sys.dm_tran_session_transactions ds
ON ds.transaction_id = dd.transaction_id
WHERE dd.database_id =7 --this is your database ID