1. Use the WITH CHECKSUM option on your backup scripts.  Read Paul Randall’s The Importance of Validating Backups article on SQL Server Central for more information.
2. Use the RESTORE VERIFYONLY command (although we use Ola Hallengren’s Backup Solution which is the verify option which runs the command).
3. Use the WITH CHECKSUM on the RESTORE command to recheck the page checksums in the backup as well as the backup file.
4. Automate or manually restore your databases from scratch on a regular basis.  (This is where we are lacking and where I want to take my servers).
5. Once you have restored, then run DBCC CHECKDB against the restored database.

If you are not doing all five then you cannot say confidently that your backups are verified.  However, if you are doing all five keep in mind that there is no fool proof guarantee against corruption, this merely minimizes the destruction by having viable backups.  I hope this helps….Enjoy!

SQL Nexus Report Options

Yesterday, I wrote about the “Realtime – Server Status” report quirkiness where essentially the report does not work when you import SQL Server 2008 data and essentially it does not contain any information as to why it does not work, only a blank screen.  Craig Purnell (Blog | Twitter) shortly thereafter sent me an updated report library file that works with 2008.  That is exciting!  I ran a diff on the two files and found that Craig updated two lines with the same change in order for this to work.  The filename  is “Realtime – Server Status.rdl” located in the Reports subfolder of your SQL Nexus installation folder.  Below is the line that needs to be changed (remember to do it in both places):

Original line:  SELECT @ts_now = cpu_ticks / CONVERT (float, cpu_ticks_in_ms) FROM sys.dm_os_sys_info

Updated Line:  SELECT @ts_now = ms_ticks FROM sys.dm_os_sys_info

Thanks Craig, now I have results that look like the following:

Realtime Server Status

SQL Nexus Report Options

As we continue our SQL Nexus journey, we continue to point out some quirkiness in the amazingly useful SQL Nexus tool.  One of the items in the Reports section that I find quirky is the “Realtime – Server Status” report which on the surface sounds like an essential tool that I would rely heavily upon.  However, when I select that report, the child screen goes blank and nothing happens.

Some of the other reports, such as the “SQL Server 2000 Perf Stats” report at least prompt you with a “The database doesn’t have necessary data to run this report” dialog box.  That makes sense in this case because I am running from a SQL 2008 R2 source import.  As we discovered early on in this blog series, the “ReadTrace_Main” report only works if your trace file import is successful.  But the “Realtime – Server Status” report gives us nothing.  No error, no suggestion, no data, nothing whatsoever.

After some careful research, actually I just loaded a SQL Server 2005 import, I discovered that this only works for 2005.  Sadly, it appears to be a deprecated feature.  This is a neat report that shows a dashboard reflecting the health of the server.  This is quirky and there should at least be a message telling you that it will not work with 2008.  Enjoy!

• EventType “Performance”, Event name “Show Plan Statistics”
• EventType “Stored Procedures”,  Event name “SP:StmtCompleted”
• EventType “Stored Procedures”,  Event name “SP:StmtStarting”
• EventType “TSQL”,  Event name “SQL:StmtCompleted”
• EventType “TSQL”,  Event name “SQL:StmtStarting”

In order to enable these simply edit the XML file, SQLDiagPerfStats_Trace2008.xml, changing the above items from false to true.  After you collect these new events by running the CMD file, then import the collection into SQL Nexus and check out your new results.  Enjoy!

The first thing I noticed, after our debacle with the trace files not loading, was that several of the reports do not work as they should.  After those were resolved, I noticed that some of the reports are not exactly what they state they are.  For example, I connected to a SQL 2008 R2 box and imported the results from a detailed 2008 performance script (see previous posts) and yet when I click on the “SQL Server 2008 Perf Stats” report I get the error below.  That is strange, what is stranger is that when I select the “SQL Server 2005 Perf Stats” report then I get results.  I have talked with other DBAs who have experienced similar problems.  Enjoy your holiday weekend!

Weird SQL Nexus Errors

Not so fast there, accidental DBA.  You are going to need the help of a nifty stored procedure called sp_help_revlogin.  This stored procedure, provided by Microsoft in the link below, will generate a T-SQL script that will copy the logins, their passwords and their corresponding SIDs (or security identification number, which is usually the culprit here).  This is also a great utility when the database has numerous users with various security levels and passwords.  Really now, who wants to write all of that down and recreate it?  Even with screenshots, it can turn into a large, fat-fingering and time consuming event.  Mind-numbing, I would say.  Why do something manually when you can do it automagically?   Just think of all those happy logins that will not be orphaned by the dark side?

How to transfer the logins and the passwords between instances of SQL Server 2005 and SQL Server 2008.

Here is also a code snippet to check for orphaned logins, for your enjoyment.  Enjoy!

USE <database_name>;
GO
sp_change_users_login @Action='Report';
GO