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

SQL Mirroring errors on Principal and Mirror Server

Ever had an issue you truly could not figure out?

Then a collegue takes one look, points to your monitor and says 'Here is your problem'!!!! Happened again today.

I'm running SQL 20008 SP1 with multiple Sync mirrors. I tried to mirror another DB today and received the following:

Principal Error Log:
Error: 1438, Severity: 16, State: 1.
The server instance Partner rejected configure request; read its error log file for more information.
The reason 1460, and state 1, can be of use for diagnostics by Microsoft.
This is a transient error hence retrying the request is likely to succeed.
Correct the cause if any and retry.

Error: 1499, Severity: 16, State: 1.
Database mirroring error: status 1460, severity 16, state 1, string DBNAME
Error: 1443, Severity: 16, State: 2.
Database mirroring has been terminated for database DBNAME

Mirror Error Log:
Error: 1474, Severity: 16, State: 1.
Database mirroring connection error 2 'DNS lookup failed with error: '11004
(The requested name is valid, but no data of the requested type was found.)'.
for 'TCP://NAME.Domain.com:5022'.
Error: 1443, Severity: 16, State: 2.

Solution
When altering the database - set the partner with the correct machine name.
I had a typo in the name and the mirror kept failing....