Friday, February 20, 2015

Collect Health and Diagnostic detail from SQL Server

There are a lot of new features in SQL Server 2012.  One of my favorite is sp_server_diagnostics. Using sp_server_diagnostics, you can collect details from your servers pertaining to the System, Resources, Query Processing, IO Subsystem and other Events detail. The procedure has one optional parameter of @repeat_interval.  This is the time iteration that the procedure will use to run repeatedly, to collect the server statistics. The parm has a default of 0, which is used if you do not pass in your own value. In this case, it will collect the data only once, and then exit. I typically pass in a @repeat_interval of 5, which tells the procedure to run, collecting the statistics repeatedly, until it is cancelled. This is the output:



Very generally, these are the descriptions for each of the returned components:

System -- Returns data regarding the system, which includes CPU usage, page faults, non-yielding Tasks, severe processing conditions, access violations, and even spinlocks.
Resource -- Much like it sounds, this collects data regarding the system resources, to include physical and virtual memory, page faults, caching, and other memory objects.
Query Processing -- This component returns data pertinent to the query processing, such as wait types, worker threads and very intense query requests.  
IO Subsystems -- Here we return data for the IO subsystem, to include timeouts and data for long running IO requests.
Events -- Returns data for event exceptions. This would include ring buffer and buffer pool events, memory and security exceptions, and even connection failures.  

If you're responsible for any SQL Servers, I'm sure that you have often had to analyze your server performance. This is a new tool that helps you do just that very quickly. 

Take a look at this piece from Microsoft for more detailed information:
    https://msdn.microsoft.com/en-us/library/ff878233(v=sql.110).aspx

Monday, February 9, 2015

SQL Server -- Different RESTORE Methods

The RESTORE command is what we use to restore database backups, previously created using the BACKUP command (see this post).  Much like BACKUP, you may perform the RESTORE in the gui, but in this post I will show you varied RESTORE methods using tSQL. I am going to be using my DBA database in each of the following examples.  

This would be the most basic form of the RESTORE statement:

     RESTORE DATABASE DBA
     FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak'
     GO

In the above example, we are restoring the DBA database from the referenced bak file. Of course, the RESTORE will fail if the bak file is not there.  This is the error you would receive:

    Msg 3201, Level 16, State 2, Line 1
    Cannot open backup device 'C:\MSSQL\Backup\DBA_20150209.bak'. Operating system error 2
    (The system cannot find the file specified.).
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.

Ok. Let's assume that you've got the bak file in place, but you're not exactly sure of the database file names or paths for the DBA data/log files.  You can run this statement to provide the logical and physical name of all database files.

     RESTORE FILELISTONLY 
     FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 


This is a portion of the output from my DBA database:









With these details we could perform the RESTORE as I sampled in the first example, or we will use the MOVE option. This option is used to restore the files to another location on disk. For example, let's say I am restoring the DBA database to another machine, with a completely different directory structure. We would restore the bak file to the D drive on the new box, like this:

RESTORE DATABASE DBA
FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 
WITH 
            MOVE 'DBA_Primary' TO 'D:\MSSQL\Data\DBA_Primary.mdf', 
            MOVE 'DBA_Data' TO 'D:\MSSQL\Data\DBA_Data.mdf'
            MOVE 'DBA_Log' TO 'D:\MSSQL\Log\DBA_Log.ldf'
      GO

Or, maybe I want to create a copy of the DBA database, on the same server, yet with a different name. We accomplish that also with the MOVE option, and a new database name, like this:

RESTORE DATABASE DBANEW
FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 
WITH
            MOVE 'DBA_Primary' TO 'C:\MSSQL\Data\DBANEW_Primary.mdf', 
            MOVE 'DBA_Data' TO 'C:\MSSQL\Data\DBANEW_Data.mdf'
            MOVE 'DBA_Log' TO 'C:\MSSQL\Log\DBANEW_Log.ldf'
      GO

This next example demonstrates how to RESTORE the database AND differential backup for the DBA database. Note we use NORECOVERY for the first part of the RESTORE of the database backup. This will leave the database unusable, or not fully recovered, until the dif is also restored.

      RESTORE DATABASE DBA 
      FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.bak' 
      WITH NORECOVERY
      GO
      RESTORE DATABASE DBA 
      FROM DISK = 'C:\MSSQL\Backup\DBA_20150209.dif'
      WITH RECOVERY 
      GO

Ok.  What about the DBA LOG backup?  In this example we restore the DBA log from a bak file created on another server, with a different directory structure. We do this using the MOVE option.

      RESTORE LOG DBA
     FROM DISK = 'C:\MSSQL\Backup\DBA_LOG_20150209.bak' 
     WITH NORECOVERY, FILE = 1
          MOVE 'DBA_Primary' TO 'D:\MSSQL\Data\DBA_Primary.mdf'
          MOVE 'DBA_Data' TO 'D:\MSSQL\Data\DBA_Data.mdf'
          MOVE 'DBA_Log' TO 'D:\MSSQL\Log\DBA_Log.ldf'
GO

Now we will perform the final RESTORE with RECOVERY, so that the database will again be usable:

     -- Perform final restore
     RESTORE DATABASE DBA
     WITH RECOVERY
     GO

On a slightly different note, how do you know your backup file is even usable?  Maybe it's a copy from somebody else, and you're not aware of how it was created. Here we can use the VERIFYONLY option. This allows us to check the bak file BEFORE running the RESTORE, to ensure that it is valid. This is the statement, and the desired output:



Lastly, one thing that people often overlook is the database ownership.  When you restore a database - in the gui or via tSQL - the database ownership is inherited by whomever is performing the restore. After a restore, right click your database in SSMS, choose Properties;  You can see the Owner is your domain login, rather than 'sa', or any other authorized owner that you may be using.  Easy enough to correct, using this statement:

USE DBA -- change this to whatever database you are targeting
EXEC sp_changedbowner 'sa'

SQL Server -- Different BACKUP Methods

If you know SQL Server, you probably know there are different types of backups that you can perform.  In this post I will briefly reference each backup type, and show you how to perform each backup using tSQL.

These are the different backups that I will cover:
  • Full
  • Differential
  • Transaction log
  • File 
  • Filegroup
  • Partial
  • Copy-Only

Full Backup - I think it's safe to say that this is the most common backup type.  The Full backup creates a complete backup of your database, to include all files and filegroups. This can be used for complete database restoration.   This is the tSQL statement you will use:

       BACKUP DATABASE DBName
       TO DISK = 'C:\MSSQL\Backup\DBName.BAK'
       GO

Differential Backup - This differential, or 'diff' as is commonly said, is a backup of changes that have occured since the last full backup was created.  This is the tSQL statement that you will use:

      BACKUP DATABASE DBName 
      TO DISK = 'C:\MSSQL\Backup\DBName.DIF' WITH DIFFERENTIAL
      GO

Transaction Log Backup - This is only applicable if your database is set to Full or Bulk-Logged Recovery Model. Using the Transaction Log backup with the Full backup will allow you to create a point in time restore. This is the tSQL statement that you will use:
     BACKUP LOG DBName 
     TO DISK = 'C:\MSSQL\Backup\DBName.TRN'
     GO
File Backup - This backup type allows you to backup each database file independently. This only applies if you have multiple data files, which is commonly done to allow for better performance, by spreading your database objects across multiple filegroups, which exist on different disk allocations. In this example, our DBName database has two data files, and this is the tSQL statement that you will use:

    BACKUP DATABASE DBName 
    FILE = 'DataFile1' 
    TO DISK = 'C:\MSSQL\Backup\DBName_DataFile1.FIL'
    GO
    BACKUP DATABASE DBName 
    FILE = 'DataFile2' 
    TO DISK = 'C:\MSSQL\Backup\DBName_DataFile2.FIL'
    GO

FileGroup Backup - Similar to the File level backup, we can also do FileGroup backups. Only applicable, of course, if you have multiple filegroups, but this allows you to target all objects on the given filegroup.  I, personally, have used this before in a horizontally partitioned database. We had a handful of Read-Only filegroups, containing historical data that never changed. These were ignored in the backup routine, which helped preserve resources by targeting only the Read-Write filegroups. This is the tSQL statement that you will use:

    BACKUP DATABASE DBName
    FILEGROUP = 'DBName_HistoricalData' 
    TO DISK = 'C:\MSSQL\Backup\DBName_HistoricalData.FLG'
    GO

Partial Backup - The Partial backups can also be used to ignore Read-Only filegroups.  As the name implies, this will allow you to partially backup the database.  This is the tSQL statement that you will use:

    BACKUP DATABASE DBName READ_WRITE_FILEGROUPS
    TO DISK = 'C:\MSSQL\Backup\DBName_Partial.bak'
    GO

Copy-Only Backup - A Copy-Only backup is very much like creating a 'snapshot' of your database.  It is completely independent of, and will not impact your backup strategy. This is the tSQL statment that you will use:

    BACKUP DATABASE DBName
    TO DISK = 'C:\MSSQL\Backup\DBName_CopyOnly.bak'   
    WITH COPY_ONLY
    GO

That's about it.  Please take a look at my other post for the different RESTORE methods.

SQL Server Reporting Services -- Report Permissions

One of my customers called me this morning with some questions regarding SSRS security. Specifically, he was having problems privileging his end users for reports he had created. He wasn't sure what to check, but this was the error the user was receiving:

  An error has occurred during report processing.
  Cannot create a connection to data source 'DATASOURCENAME'.
  For more information about this error navigate to the report   
  server on the local server machine, or enable remote errors

The user in question was able to launch Report Manager, but any attempt to open a report failed with the above message. After review, we confirmed the user was not privileged in the appropriate database. At this point, I explained the Role-based security model, and the different levels of SQL Server object privileges that are necessary. The problem was resolved when we enabled the correct database permissions for the user.

This post is just a quick query that you can use to see what roles and permissions are assigned to different report server objects.

     USE ReportServer;

     SELECT 
          u.UserName [User]
           r.RoleName [Role]
          r.Description [PrivilegeDescription]
          c.Path [ObjectPath]
          c.Name [ObjectType] 
     FROM
          dbo.PolicyUserRole pur INNER JOIN dbo.Policies p
             ON pur.PolicyID = p.PolicyID INNER JOIN dbo.Users u 
                ON pur.UserID = u.UserID INNER JOIN dbo.Roles r 
                  ON pur.RoleID = r.RoleID INNER JOIN dbo.Catalog c 
                     ON pur.PolicyID = c.PolicyID
     ORDER BY
          u.UserName   


This is an example of the output you will receive:

  User                 Role        PrivilegeDescription                                                  ObjectPath         ObjectType
  DOMAIN\login  Browser   May view folders, reports and subscribe to reports.    /Data Sources     Data Sources
  DOMAIN\login  Report Builder    May view report definitions                               /FolderName/rptname   Report


Please look here for more detail regarding the SSRS object security assignment for reports, report models and resources, and the report data sources.  

Tuesday, February 3, 2015

SQL Server Database Mirroring... Error Msg 1412

I had the craziest problem happen recently.  Not that it shouldn't have happened, but the error return that I received from SQL Server did not indicate the problem very clearly at all.  I spent two days reading and researching... only to find a very obvious problem, that never should have happened.

A little background.... A customer of mine had a v2008 R2 SQL Server where 5 databases were mirrored, using Database Mirroring.  The server was old and over-used, so they bought a new server, dedicated for the mirror, high performance, no witness.  To implement, I needed to break the current mirroring session for each of the 5 databases, and build it back up on the new server.  I have listed my steps here briefly, and then provided further detail for each following the list.
  1. Turn off mirroring on PRINCIPAL
  2. Drop Endpoint on Mirror
  3. Drop Database on Mirror (Won't do this until after the new mirrored instance is defined)
  4. Create FULL backups of databases and logs, copy to MIRROR server
  5. On MIRROR restore database xxx WITH NORECOVERY
  6. On MIRROR restore database log xxx WITH NORECOVERY 
  7. Re-use endpoint on PRINCIPAL  (Not dropping/recreating it, just re-using the same endpoint)
  8. Create new endpoint on MIRROR (mimicking the PRINCIPAL endpoint) 
  9. On MIRROR, create partnership to PRINCIPAL
  10. On PRINCIPAL, create partnership to MIRROR
  11. On PRINCIPAL, set partner safety off (Thus, High Performance mode)

-- #1. Turn off mirroring on PRINCIPAL
ALTER DATABASE xxx SET PARTNER OFF;

-- #2. Drop Endpoint on MIRROR 
DROP ENDPOINT endpointname;

-- #3. Drop Database on Mirror (It's a placeholder. Do not do this until new mirror is good.)
DROP DATABASE xxx

-- #4. Create FULL backups of databases and logs (I backed up to the Mirror Server via UNC)
-- full db backup
BACKUP DATABASE xxx
TO DISK = '\\MirrorServer\E$\TEMP\xxx.bak' 
WITH FORMAT
GO
-- full log backup
BACKUP LOG xxx
TO DISK =  '\\MirrorServer\E$\TEMP\xxx_Log.bak'  
WITH FORMAT
GO                                 

-- #5. On MIRROR restore database xxx WITH NORECOVERY
RESTORE DATABASE xxx
FROM DISK 'E:\temp\xxx.bak'
WITH 
MOVE 'xxx_dat' TO 'E:\MSSQL\DATA\xxx_Data.mdf'
MOVE 'xxx_log' TO 'F:\MSSQL\Log\xxx_Log.ldf',
NORECOVERY;

-- #6. On MIRROR restore database log xxx WITH NORECOVERY
RESTORE LOG xxx
FROM DISK = 'E:\temp\xxx_Log.bak' 
WITH NORECOVERY;  

-- #7. Re-use endpoint on PRINCIPAL
CREATE ENDPOINT endpointname
AUTHORIZATION [DOMAIN\sqlservice]
STATE = STARTED
AS TCP (LISTENER_PORT= 5023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4)        

-- #8. Create new endpoint on MIRROR
CREATE ENDPOINT endpointname
AUTHORIZATION [DOMAIN\sqlservice]
STATE = STARTED
AS TCP (LISTENER_PORT= 5023, LISTENER_IP = ALL)
FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE,
ENCRYPTION = REQUIRED ALGORITHM RC4)

-- #9. On MIRROR, create partnership to PRINCIPAL
ALTER DATABASE xxx SET PARTNER = N'TCP://PrincipalSrvName.DOMAIN.Local:5023' 

-- #10. On PRINCIPAL, create partnership to MIRROR
ALTER DATABASE xxx SET PARTNER = N'TCP://MirrorSrvName.DOMAIN.Local:5023'

-- #11. On PRINCIPAL, set partner safety off 
ALTER DATABASE xxx SET PARTNER SAFETY OFF;


Basically, that's it. I tested the process successfully twice in my own environment, but with two KEY differences.  First, my Principal and Mirror were two named instances on the same physical SQL Server, whereas the Customer had two physically separate SQL Servers.  Secondly, I used the MOVE statement in the RESTORE in step #5.  I used this in my own testing successfully, so I didn't think that I needed to avoid the MOVE when actually doing it in production. *WRONG*WRONG*WRONG* If I could type bells and whistles here, I would.  I used the MOVE for two reasons:  

1. Because I built a new server for the Customer, with a completely different directory path structure.
2. Because step #4 of the example in this reference does not say we 'MUST USE IDENTICAL PATHS'.  Rather, the reference says that 'IF the paths differ....'.  For that statement alone, I thought that the effort could be done with different directory paths on the Principal and Mirror.  At least in my implementation, that was definitely not the case.  

This became clear to me at step #9.  All steps completed successfully to that point, but step #9 failed immediately with this error:

Msg 1412, Level 16, State 0, Line 1
The remote copy of database "xxx" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

This is what I meant by the unclear error return.  Not just the less than intuitive return, but BOL's instructions for preparing the database(s) for Mirroring actually gives an example using the MOVE statement. It was not until several days later, and an assist from one of my SQL buddy gurus, that I realized this failure was being caused by different directory paths on the Principal vs the Mirror.  

I very humbly explained the problem to the Customer;  The Server Team rebuilt the box, and I repeated all of the above efforts WITHOUT the MOVE statement in Step #5.  The directory path structure on the Mirror mimicked the Principal, and I completed the new mirror deployment successfully.

Msg 3241, The media family on device 'XXXX' is incorrectly formed.

I was restoring some databases recently, and ran into an error I wanted to share with my readers. I had backed up both Report Server databases, and copied the bak files to the DR server, where I intended to restore both as a redundancy. The backups worked just fine, but the RESTORE failed with the error in the post title.  This is the statement and the error:



Odd. I had just created the bak file myself. Haven't had any problem writing TO the disk, so how could it be corrupt? I decided to create another backup and attempted the restore again -- same error. Off to Google.... surprising how many posts I find on the same error, but the causes just did not fit mine --

So I backed out of everything and reviewed my logic, and recognized that my backup type was wrong. As you know, we do the RESTORE from different backup file types.  I was attempting to RESTORE from a .bak, but had the 2 in my statement, which is for the trn. 

After I corrected the backup file type to 1, for database backup, I was able to RESTORE from ReportServerTempDBLog.bak without error.  Same statement, just a correct file type.



SQL Server EXISTS vs IN

There are many different opinions on the use of EXISTS vs the IN list.  While they are logically different, many people say that they are identical in terms of performance.  I know they may seem very close to the same, but they are not.  In this post, I am going to demonstrate the performance difference of a very simple SELECT;  first done with EXISTS, then with the IN list.  I have included two quick tables and the population thereof.  Then we've got the retrieval using EXISTS and IN, along with the statistics generated by each of the statements.

-- TableA
IF OBJECT_ID('TableA','U') <> 0
DROP TABLE dbo.TableA
GO
CREATE TABLE dbo.TableA (
 ColumnA INT
) ON dba_data  -- rename as needed
GO

-- TableB
IF OBJECT_ID('TableB','U') <> 0
DROP TABLE dbo.TableB
GO
CREATE TABLE dbo.TableB (
 ColumnB INT
) ON dba_data -- rename as needed
GO

Ok.  Now let's load some data into both test tables.  This is just a very simple method that I use when I'm stress testing, and I need test data sets.  I am auto-incrementing the data while inserting, to generate unique values.

-- Load both tables
DECLARE @a INT
SET @a = 1
WHILE @a <= 200000
BEGIN
  INSERT dbo.TableA VALUES (@a)
  SET @a = @a + 1
END;

DECLARE @b INT
SET @b = 20
WHILE @b <= 120
BEGIN
  INSERT dbo.TableB VALUES (@b)
  SET @b = @b + 1
END;

-- check your rowcounts
SELECT COUNT(*) FROM dbo.TableA
SELECT COUNT(*) FROM dbo.TableB

And now for our retrieval.  We are looking for everything in TableA that does not exist in TableB. In this first SELECT we are using EXISTS.  Please also note that I am using STATISTICS IO ON/OFF to gather the statistics generated by the statement.

/* USING EXISTS */
SET NOCOUNT ON; -- just getting the rowcounts out of the test
SET STATISTICS IO ON;

SELECT * FROM dbo.TableA a 
WHERE NOT EXISTS(
  SELECT 1 FROM dbo.TableB b 
  WHERE a.ColumnA = b.ColumnB)

SET NOCOUNT OFF;
SET STATISTICS IO OFF;

Same same on the SELECT here, but now we are using the IN list.

/* USING THE IN LIST */
SET NOCOUNT ON;
SET STATISTICS IO ON;

SELECT * FROM dbo.TableA a 
WHERE a.ColumnA NOT IN(
  SELECT ColumnB b FROM dbo.TableB)

SET NOCOUNT OFF;
SET STATISTICS IO OFF;

These are our collected statistics -- first for the EXISTS, then for the IN list:

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableA'. Scan count 1, logical reads 338, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TableB'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TableB'.Scan count 9, logical reads 200005,physical reads 0,read-ahead reads 0,lob logical reads 0,lob physical reads 0, lob read-ahead reads 0.
Table 'TableA'. Scan count 5, logical reads 338, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Look at those scan counts, and the logic reads!  I realize this is just a small example, but the difference is very clear, both logically and physically.