TimeMatters Master Audit File Error

If you are running TimeMatters in a replicated environment and you get the error:

Unable to access Master Audit File
Record Audit will not be saved.

ERROR: The insert failed. It conflicted with an identity range check constraint in database 'TimeMatters10', replicated table 'lntmuser.Audit.Current', column 'Ident'. If the identity column is automatically managed by replication, update the range as follows: f

The run the following command on the publisher and it should fix it:

exec sp_adjustpublisheridentityrange @table_name = 'audit current', @table_owner = 'lntmuser'

SSRS 2008 - Adding SSL After Installation

If you can't get it to work, try doing everything in this thread:

http://prologika.com/CS/forums/t/946.aspx

And everything here:

http://technet.microsoft.com/en-us/library/ms345223(SQL.100).aspx


Also, SSRS 2008 does not support wildcard certificates, ie *.yourdomain.com. Obtain a special dedicated SSL cert YourServerName.yourdomain.com, then apply it on the SSRS 2008 server. This is what worked for us at a client, after calling PSS. This is a known issue at Microsoft and they are working on a better solution.

SQL Server 2008 Shrink Log File

When DBAs wanted to shrink a log file in SQL 2000 and 2005, they would often backup the log with TRUNCATE_ONLY. When they attempt to do the same thing in SQL Server 2008, they will be greeted with this message:

'TRUNCATE_ONLY' is not a recognized BACKUP option.

Here is a replacement script for SQL Server 2008:

USE Test
GO
ALTER DATABASE Test
SET RECOVERY SIMPLE
GO

DBCC SHRINKFILE(Test_Log, 10)

GO
ALTER DATABASE Test
SET RECOVERY FULL

This script puts the database in Simple Recovery mode. It then shrinks the log file. Finally, it puts the database back in Full Recovery mode.

New Index grayed out in SQL Server Management Studio

In SQL Server Management Studio, if you right-click on the indexes and you see new index grayed out, it is because the table is schema-locked in another window. Close the design view of the table and new index should now be black and usable.

SSRS 2008 List All Reports, Users, Roles, and Security

Someone on the newsgroups today asked the following question:

Is there any report that can be run in Reporting Services that lists
all reports and the users that have access?


I assumed he meant that he wanted a list of all the reports he has, and a list or roles and the users that are in them. I quickly wrote the following query for him. I hope some of you find it useful:

SELECT u.UserName, c.[Path], c.[Name], r.RoleName, *
FROM Users u
JOIN PolicyUserRole pur
ON u.UserID = pur.UserID
JOIN Policies p
ON pur.policyID = p.PolicyID
JOIN Catalog c
ON p.policyID = c.policyID
JOIN Roles r
ON pur.RoleID = r.RoleID
ORDER BY u.UserName, c.[Name], r.RoleName

SQL Server Support with VMWare

I have a client who is running SQL Server 2005 Standard Edition on a VMWare ESX operating system. I wondered if Microsoft supported this environment and did some digging. I had some difficulty finding current information, so once I found it, I thought I'd share it with you.

As of November 2007, Microsoft says they do not support third party software virtualization. They require that you reproduce the issue in a non-virtualized environment before they will help you. Here's the link confirming that:

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

Tracing Events Appropriately

When using SQL Server Profiler, it is generally a good idea to only trace COMPLETED events, instead of starting events.

Completed events have all necessary performance information. The starting events do not have important performance statistics because they haven't been collected yet. This will reduce the load of tracing and give you less data to filter out later.

Performance Tuning and the Heisenberg Uncertainty Principle

Often when I visit a new client, they say, "We need you to start SQL Server Profiler and tell us why our server is so slow."

I'm reluctant to immediately resort to the SQL Server tracing utilities because of the performance load that they cause. Sometimes, I explain to the Heisenberg Uncertainty Principle:

http://en.wikipedia.org/wiki/Uncertainty_principle

and similarly the Observer Effect:

http://en.wikipedia.org/wiki/Observer_effect

Developed by Werner Heisenberg in 1927, this principal states that when you measure something, there is uncertainty caused by the measuring process. For instance, if we write our trace logs to a file and we're looking for disk contention, the tracing itself might be contributing to it. The uncertainty might also be caused by the measuring logic. We might be looking at excessive disk contention and determine that we need faster disks. But what if memory paging is causing the pages to be written to disk? In that case, our measuring logic was flawed, and we would need to add more RAM to resolve the performance bottlenecks.

So before I start SQL tracing, I like to use other tools first. Task Manager, System Monitor, Activity Monitor, and DMVs will tell us an awful lot about a server's performance before tracing is even needed. This allows me to observe the server's performance with an open mind, before using invasive tracing utilities.

Do Your Foreign Key Columns Have Indices?

When I do a database audit, I always check for the existence of foreign keys that enforce referential integrity. That is often a great sign on whether or not a data-centric developer has been involved in the project. Once foreign keys have been created, indices must manually be configured, for SQL Server does not create them automatically.

I wrote this script to tell you if you have indices on all of your foreign key columns:

SELECT o.[Name] AS TableName, c.[Name] AS ColumnName
FROM sysForeignKeys f
JOIN sysObjects o
ON f.fkeyID = o.[id]
JOIN sysColumns c
ON c.[ID] = f.fkeyID AND c.[colid] = f.fkey
LEFT OUTER JOIN sysIndexKeys i
ON f.[fkeyid] = i.[id] AND f.[fkey] = i.[colid] AND f.[keyno] = i.[keyno]
WHERE i.[id] IS NULL

Even if referential integrity is being enforced at the application layer (GASP!), you should still have indices created on those virtual foreign key columns. Unfortunately, that will have to be audited manually by the application developer, which pretty much means it will never happen.

Hard Disk Configuration

When asked where hardware dollars are best spent, I always recommend a great disk subsystem.

Hard drives are often the source of performance bottlenecks. Any dime spent on a high-performing hard drives will not go to waste.

Disks are also the component that will most likely fail. Matt, the Microsoft blogger, claims that disks have a failure rate of 2% per year per disk. If that's the case, then an investment in RAID arrays would be very wise.

As far as specific drive components go, it's difficult to make a recommendation without knowing the purpose of the SQL Server.

Here's Matt's post:

http://blogs.msdn.com/matt_hollingsworth/archive/2007/04/02/mtbf-is-useless-how-to-decide-when-to-use-raid-for-database-high-availability.aspx

No More DBCC SHOWCONTIG

In SQL Server 2005, DBCC SHOWCONTIG has been replaced with the function sys.dm_db_index_physical_stats. Use this function if you need to diagnose fragmentation and page usage issues.

Pay attention to the fragmentation levels in the output. This is reporting more accurately than in SQL Server 2000.

Here's a Books Online link explaining the new function:
http://msdn2.microsoft.com/en-us/library/ms188917.aspx

And a link on index fragmentation:
http://msdn2.microsoft.com/en-us/library/ms189858.aspx

Dedicated Administrator Connection

In previous version of SQL, it was possible for the server to tie up so many resources, that one more connection was no longer possible. For instance, you could have one process that was blocking all the others, and have no way to open the SQL Server tools to kill it.

That all changed with the DAC. Here's the Microsoft article on how to use it:

http://msdn2.microsoft.com/en-us/library/ms189595.aspx

Kalen Delaney (Inside SQL Server 2005) has this great query for determining the SPID of the DAC:

SELECT t2.session_id
FROM sys.tcp_endpoints as t1
JOIN sys.dm_exec_sessions as t2
ON t1.endpoint_id = t2.endpoint_id
WHERE t1.name='Dedicated Admin Connection'; GO

32-bit vs 64-bit SQL

When it comes to hardware, most SQL Servers I have seen are over-powered. Memory and CPU are most often underuntilized. So when I'm asked whether a customer needs the 64-bit version of SQL server, I think it comes down to one question. Do you need more than 3GB of memory addressed?

It's important to note that the 32-bit version of Windows can only address 4GB of memory. By default, it reserves 2GB for itself, however this can be reduced to 1GB by using an operating system switch. Memory beyond the 4GB addressed by the OS can be accessed by SQL Server indirectly using the PAE switch.

http://msdn2.microsoft.com/en-us/library/ms190673.aspx

The 64-bit version of SQL Server can address an incredible amount of memory directly, with no need of these work-arounds.

Free SQL Server 2005

Just a friendly reminder that you can evaluate SQL Server 2005 for free for 120 days. This is an excellent way to learn the features of the product before purchasing it. It is the full Enterprise edition. You can download it here:

http://www.microsoft.com/technet/prodtechnol/eval/sqlsrv05/default.mspx