Show top 10 Wait State Issues

Hello Everyone,

The following code will show the top 10 issues reported by SQL Server's wait states. Full descriptions are provided.

Hope you find it helpful,

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DWmsxcVhXVnNoVTQ/edit?usp=sharing

SQL Server Document Generator for Disaster Recovery

Hello Everyone,

Today I'm putting up my new script that will document your SQL server so that you can recover from a disaster which in this case is a bare metal build. To rebuild everything, you need to have as much documentation about your SQL server as possible. This script will help.

There are 3 stored procedure use to generate the output which are
usp_DR_DBRestoreScript, usp_DR_LinkSrvRebuilt and sp_help_revlogin which are included at the end of the code.

The 3rd stored procedure is actually from Microsoft which will script out the logins

Some code has been placed together with snippets I have found on the internet and would the credit the authors but haven't found them. So if you see a snippets of your code here then I pass on credit to you too.

***Note*** Please test this script before using and before using it for DR proposes. I do not warrently and do not take any responsiblity for it, its misuse or output it creates. Use at your own risk.

The script produces and output from several areas which are listed here:

1)    Physical Server Information - Number of CPUs, memory, IP Address, etc.
2)    Hard drive space available - in Megabytes
3)    SQL Server Information - Server name, version of SQL server, Patch level, etc.
4)    SQL Server Port Number Used
5)    SQL Server Settings - SP_CONFIGURE
6)    Database and Log file Physical Locations - All the information regarding the database(s) location
7)    Database Details - All database information
8)    Last Backup Dates -  What and when the last backups was completed
9)    List of SQL Jobs - What jobs execute
10)  Failed SQL Jobs - Jobs that have failed before. This is important as the new installation may have the same failures
11)  Disabled Jobs - Jobs there but not set to execute
12)  SQL Server Services Status - What services were installed and running
13)  Link Server Details - What other servers is SQL Server linked to
14)  Database Mail Details - See if it is installed and running
15)  Database Mirroring Details - Databases mirrored status
16)  Database Log Shipping Details
17)  Cluster Details - Information on cluster configuration
18)  Always On Replication Details. SQL 2012 and newer

I have tested it on SQL Server 2008, 2008R2, 2012. Haven't tested on SQL 2000

Please let me know if you think other information should be added to the script.

Thanks!

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DMnRRUDJOeXIyRWs/edit?usp=sharing

Managing many SQL Servers

 

How many times have you wished you could just get some information from all your SQL servers? Things like job failures, versions, list of users, etc?  When you have a few SQL servers it's not as bad. Since I was working in over 70 servers/instances,  getting this information could take all day or longer.  Here I will show you how you can remotely execute code on all your SQL servers regardless of version or platform. Currently I'm using this process against SQL 7.0, SQL 2000, SQL 2005, SQL 2008 both x32 and x64 servers.  It works with default and named instances.

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DOERVNXdyeW9NNlE/edit?usp=sharing

What  is Remote Execution?

Here is my definition of remote execution. Executing a stored procedure or a T-SQL statement on many servers from one location and viewing the results on one monitor.
So for example, let's say you want to execute the "sp_who"  command on all your production servers. Normally you would manually connect to each server/instance and execute the stored procedure. Then you would want to copy/paste the output for final review. With a remote execution setup, you would just pick the servers/instances and have the code execute the "sp_who" command on all the production servers/instances with all the outputs delivered to one screen. 
The above example talks about production servers, but it could have been any of the following , or more.

• All production servers/instances that are only SQL 2000 or only SQL 2005
• Only test servers/instance that have SQL 2005 and owned by Finance
• All servers/instances regardless of version, type, owner, etc

As you can see this would make the life of a DBA a little easier. With all the results displayed in one location you could easily review and/or save to a file.

How to Get Started

Getting started is easy and doesn't take a veteran DBA to setup. Here are the basics you will need.
Note that all the code is provided so you just have to copy, paste and execute.

First is where you want to store and execute the code. I recommend a server/instance that you have control over. Here you would create the databases and tables needed and execute the remote code from. This can be just about anywhere but I would recommend a server/instance that is not development, test or UAT environment as someone could modify or delete your code and or table(s). 

The version of SQL server to use is SQL 2005 with SP2 and the Hotfix for SP2.  You can search the Microsoft web page( http://support.microsoft.com/) to get the service packs and patches you need, if you don't already have them.

You will need to set this SQL server installation to be "Trusted" by all you SQL servers. To do this you will need access to Active Directory or have someone in the Network Department with "Domain Admin" rights to make the changes.

What needs to be done is make your server "trusted" by all the other SQL servers in your network. Once the other servers trust yours you can easily use "link connections" to execute your code remotely. To get more information on link connections, please refer to Books On Line (BOL) or Microsoft's web page ( http://support.microsoft.com/).

To make your server trusted (as you not make your instance trusted) perform the following

1. Open "Active Directory Users and Computers" from your PC (if domain user) or log into a servers with a user that has domain admin rights.
2. Click on "Actions" and select "Find"
3. Change the "Find" pull down and select "Computers". Now in the "Computer Name" box type in the name of your server and click " Find Now"
4. Your server should now be displayed in the "Search results" window. Right click on your server's name and select "Properties"
5. A new window will open. Click on the "Delegation" tab and finally click the radial button that says "Trust this computer for delegation to any service (Kerberos only).
6. Click the "Ok" button to close the windows and give the network some time to propagate this information to all the other servers.

Security Note! By allowing your server to be trusted by all the other servers you need to ensure that you guard this server as if someone hacks into it they could gain access to all the other servers. Please discuss with your Security Analyst before setting this feature.

If using trusted connections is allowed you can modify as follows.

• Add to additional columns to theAllSQLInstancesTabletable. One would be user id and the other would be the password.
• Modify main code to look up these two columns so that it would populate the link connection login information and thus connect using the id provided.

Collected data to your main table. That is all your SQL server names, versions, department or system owners and type of environment (production, development, test, UAT). If you have a list, great! If not, here is what you can do to start.

Open a command window (CMD) and execute :  osql -L or isql -L  which will display a list of SQL servers on your network.  Now you will have a list of most of the servers/instances. For some reason not all servers/instances are displayed.

You could use the pipe command to create a file and import the data to your table. Here is an example.

osql -L >srvlist.txt

Using notepad or any editor, open the file and remove anything you don't what  so that all you have is a list of only SQL servers/instances. Next you can use the Import Data wizard to load the data into your table.

The next step is to create the databases and tables that will hold all you server/instance information and more.

Creating the Remote Execution Environment

Now it's time to create the databases and tables needed to make this all work. The databases you will create are called
Database 1 - SQLSrvOps -  SQL Server Operations is there the main work is done
Table 1 - AllSQLInstancesTable - Stores the server/instance names

Database 2 - SQLSrvReports - SQL Server Reports is where data is saved for reporting
Table 2 - GeneralError  - Stores errors that may occur during execution.

Creating SQLSrvOps Database

Here is the code used to create the database called SQLSrvOps. If you want to change the name just do a search and replace throughout all the code provided.

Before executing change the location of where you want the data (.MDF) and log (.LDF) files to be located. Here I'm installing on to the D:\MSSQL2005\MSSQL\DATA  location.

USE [master]
GO
 CREATE DATABASE [SQLSrvOps] ON PRIMARY
 ( NAME = N'SQLSrvOps',FILENAME =
N'D:\MSSQL2005\MSSQL\DATA\SQLSrvOps.mdf', SIZE = 6144KB , MAXSIZE =UNLIMITED, FILEGROWTH= 1024KB )
  LOG ON
 ( NAME = N'SQLSrvOps_log',FILENAME = N'D:\MSSQL2005\MSSQL.8\MSSQL\DATA\SQLSrvOps_log.ldf', SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
 EXEC dbo.sp_dbcmptlevel @dbname=N'SQLSrvOps', @new_cmptlevel=90
GO
 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
 begin
 EXEC [SQLSrvOps].[dbo].[sp_fulltext_database] @action = 'disable'
 end
 GO

Now you should see the database called SQLSrvOps.

Creating  Table for SQLSrvOps Database

Next, you will need to create the table called "AllSQLInstanceTable" to hold all your server/instance information. Below is the code to execute.

USE [SQLSrvOps]
GO
 SET ANSI_NULLS ON
GO
 SET QUOTED_IDENTIFIER ON
GO
 SET ANSI_PADDING ON
GO
 CREATE TABLE [dbo].[AllSQLinstancesTable](
       [SQLInstanceName] [varchar](75)NOT NULL,
       [SQLlvl] [varchar](1)NOT NULL,
       [Owner] [varchar](50)NOT NULL,
       [SQLVer] [nchar](10)NULL
 ) ON [PRIMARY]
GO
 SET ANSI_PADDING OFF

If you view the table created you should see the following columns.

SQLInstanceName - this is where all your SQL server/instance names are located

SQLlvl - type of system. I use P for Production and D for development, test and UAT
Owner - Person or department that owner the server/instance
SQLVer - Version of SQL . Currently I use the following

• 70 = SQL 7.0
• 2000 = SQL 2000
• 2005 = SQL 2005
• 2008 = SQL 2008

From the list your created earlier (remember the "osql -L >srvlist.txt" command?) you can now use the Import Data Wizard to import the data into this new table. To do this just right click on the "SQLSrvOps" database, highlight "tasks" and "Import Data". I am not going to get in to details of this tool as  I'm sure you can take it from here. Just read and follow the prompts. Once the data is loaded, open the table and add the additional information. Continue to populate the table until you have a few servers to test on.

Creating SQLSrvReports Database

Within this database we will record errors that have occurred during execution of the remote code and hold data from the results (if you like) which can be used with SQL Server Reporting Services (SSRS) to display information. Let's make the reporting database.

Here is the code to create the "SQLSrvReports database"
Before executing change the location of where you want the data (.MDF) and log (.LDF) files to be located. Here I'm installing on to the D:\MSSQL2005\MSSQL\DATA  location.

USE [master]
GO
 CREATE DATABASE [SQLSrvReports] ON PRIMARY
 ( NAME = N'SQLSrvReports',FILENAME =
N' D:\MSSQL2005\MSSQL\DATA \SQLSrvReports.mdf' , SIZE= 595968KB , MAXSIZE= UNLIMITED,FILEGROWTH = 1024KB )
  LOG ON
 ( NAME = N'SQLSrvReports_log', FILENAME=
N' D:\MSSQL2005\MSSQL\DATA \SQLSrvReports_log.ldf' , SIZE= 833024KB , MAXSIZE= 2048GB , FILEGROWTH= 10%)
GO
 EXEC dbo.sp_dbcmptlevel @dbname=N'SQLSrvReports', @new_cmptlevel=90
GO
 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
 begin
 EXEC [SQLSrvReports].[dbo].[sp_fulltext_database] @action = 'disable'
 end

The reporting database is now created.

Creating  Table for SQLSrvReports Database

To create the table called "GeneralError", execute the following.

USE [SQLSrvReports]
GO
 SET ANSI_NULLS ON
GO
 SET QUOTED_IDENTIFIER ON
GO
 SET ANSI_PADDING ON
GO
 CREATE TABLE [dbo].[GeneralError](
       [ErrMsg] [nvarchar](2048)NULL,
       [ErrInstance] [varchar](255)NULL,
       [ErrDate] [datetime] NULL,
       [ExecFrom] [varchar](255)NULL
 ) ON [PRIMARY]
GO
 SET ANSI_PADDING OFF

Here is the definition of the columns used

• ErrMsg - Holds the error message itself
• ErrInstance - Shows which remote server produced  the error
• ErrDate - Date and time of the error
• ExecFrom - The function or job that when executed caused the error

You can see that the remote execution did not get information on the FINSQL01  and the INVENT01 server. This does not mean that the jobs failed but the remote execution on the status has failed.
With all the databases created and tables created/populated you are now ready to execute your code remotely.

Remote Execution Code

Let's go through the code and I will show you an example of how to use it.
First the code starts off by declaring variables that will be used throughout the execution

USE master
GO
 PRINT '***************** Start of Remote Query Excution *******************'
 PRINT ' '
 SET NOCOUNT ON
 -- Create variables used by script
 DECLARE@InstanceName VARCHAR (75),     -- Server Instance Name
             @lvl VARCHAR (1),          -- Production or Test server
             @ver VARCHAR (4),          -- SQL version number
             @Err int,                  -- Errors detected. For future use
             @PreLinked VARCHAR (75)    -- Used for removing any old linked                                                                                                       
 -- Create cursor to cycle thru servers in the SQLinstanceTable table
 DECLARE myInstances CURSOR FOR SELECT SQLInstanceName, SQLlvl, SQLVer FROM SQLSrvOps.dbo.AllSQLinstancesTable

The variable descriptions is show in the remarks in green. Next we create a cursor where the code reads in the contents of the "AllSQLInstanceTable". Remember the table you had to populate?
The next section is remarked out. If you want to remotely execute your code on all servers/instances regardless of type, version, owner, etc . you would leave this section as is.

-----------------------------------------------------------------------------
 -- Below are some conditions that could be applied
 -- Unremark the following to only execute code on Production servers
 -- WHERE SQLlvl = 'P'
 -- Unremark the following to only execute code on Test / Development servers
 -- WHERE SQLlvl = 'D'
 -- Unremark the following to only execute code on SQL 2000 servers only
 -- WHERE SQLVer = '2000'
 -- Unremark the following to only execute code on SQL 2005 servers only
 -- WHERE SQLVer = '2005'
 -- Here you can add your own conditions for example
 -- WHERE Owner = 'Finance'
 -----------------------------------------------------------------------------

If you only want to execute the remote code on the production servers/instances, you would un remark the line as follows
Original line -- WHERE SQLlvl = 'P'
Un remarked line  WHERESQLlvl = 'P'

With this change only the production servers/instances would have the remote code executed against.
Let's say you what a more refined condition, you could use something like this

SELECT * FROM dbo.AllSQLinstancesTable
  WHERE SQLlvl = 'P' 
  AND SQLVer = '2000'
 AND SQLInstanceName LIKE '%fin%'

The code would only execute on servers/instances that are in the production environment, have SQL 2000 installed and the name is like 'fin'
So you see that you have many possibilities on how to refine the execution. You could even add additional columns to the "AllSQLInstanceTable" table to even more complex conditions.
The code now creates another cursor that will detect and remove any existing link connections.

-- Create cursor to cycle thru servers at are still have a link connection
 -- Remove any existing Linked Server connection before continuing with code
 DECLARE LinkedSrv CURSOR FOR (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0)
       OPEN LinkedSrv
       FETCH NEXT FROM LinkedSrv INTO @PreLinked
             WHILE @@FETCH_STATUS = 0
             BEGIN
                   PRINT @PreLinked
                   EXEC sp_dropserver @PreLinked, 'droplogins'
                   FETCH NEXT FROM LinkedSrv INTO @PreLinked
             END
 -- Close cursor
 CLOSE LinkedSrv
 DEALLOCATE LinkedSrv

Why is this done?  As the code tries to create the link connection if one exists and that server/instance not longer exists the code will report back an error. This clearing of existing link connection allows the code to only use the data in the "AllSQLInstanceTable" as valid server/instance names.
If a new server/instance is added to you network, or one is decommissioned, you would only have to make the change once in the table and thus the code will continue to work without errors.
If you have linked connections that are always needed, you could modify the code so that the link connection is never touched.

Now the code starts to collect the first server/instance name and creates a linked connection to that server.

-- Collect first server name to remotely execute script on
 OPEN myInstances
       FETCH NEXT FROM myInstances INTO @InstanceName, @lvl, @ver
       WHILE @@FETCH_STATUS = 0
             BEGIN
             PRINT 'Create/Connect/Execute/Drop Linked Server for Instance: [' + @InstanceName + ']'
 -- Create Linked Server connection
 BEGIN TRY
       EXEC sp_addlinkedserver @InstanceName , N'SQL Server'
 END TRY
 BEGIN CATCH
       PRINT 'ERROR! CAN NOT ADD LINK CONNECTION ON REMOTE INSTANCE: [' + @InstanceName + '], please review'
       INSERT INTO [SQLSrvReports].[dbo].[GeneralError](ErrMsg, ErrInstance,  ErrDate, ExecFrom)
             values (error_message(), @InstanceName, getdate(),'SQL Job');
 END CATCH

This is why you need to ensure that the server/instance you are executing from is "TRUSTED" by the remote server/instance as a link connection is made.
A successful link connection is made the code will show you the server/instance name as below.
Create/Connect/Execute/Drop Linked Server for Instance: FINSQL01

If link connection cannot be made, you will see an error message and an entry is made in the GeneralError table.
ERROR! CAN NOT ADD LINK CONNECTION ON REMOTE INSTANCE: FINSQL01, please review'

Once the link connection is available the code creates a login.

-- Create Linked Server login
 BEGIN TRY
       EXEC sp_addlinkedsrvlogin @InstanceName, 'TRUE'
 END TRY
 BEGIN CATCH
       -- Provide warnings if script could not be executed on the remote server
       PRINT 'ERROR! CANNOT ADD LINK SERVER LOGIN ON REMOTE CODE ON INSTANCE: [' + @InstanceName + '], PLEASE REVIEW'
       INSERT INTO [SQLSrvReports].[dbo].[GeneralError](ErrMsg, ErrInstance, ErrDate, ExecFrom)
             values (error_message(), @InstanceName, getdate(),'SQL Job');
 END CATCH

You will be notified  if a link connection login can or cannot be created.  The error message will be copied to the " GeneralError" table. Note about the " GeneralError"  table. You can execute the remote code within a job and review the "GeneralError" table for possible issues.

Finally we get to the section that contains the actual code to be executed remotely.

-- Code to be executed remotely --
 -----------------------------------------------------------------------------
 BEGIN TRY
 -- Actual code to execute on remote server/instance
 EXEC ('SELECT [name] FROM ['+ @InstanceName + '].MASTER.dbo.SYSDATABASES')
 END TRY
 BEGIN CATCH
       -- Provide warnings if script could not be executed on the remote server
       PRINT 'ERROR! CAN NOT EXECUTE REMOTE CODE ON INSTANCE: [' + @InstanceName + '], PLEASE REVIEW'
       INSERT INTO [SQLSrvReports].[dbo].[GeneralError](ErrMsg, ErrInstance, ErrDate, ExecFrom)
             values (error_message(), @InstanceName, getdate(),'SQL Job');
 END CATCH
 -----------------------------------------------------------------------------

Once again, if the successful or not a message and possible table insert is performed.
In the code above you can see that the code to be executed it between the BEGIN TRY and END TRY
EXEC ('SELECT [name] FROM ['+ @InstanceName + '].MASTER.dbo.SYSDATABASES')

Here SQL is instructed to get the names of the databases (in sysdatabases) that are located on the remote server/instance which is stored in the [' + @InstanceName + ']variable.  With the results shown on your screen.

Now here is some code you should execute remotely on all your production servers.

SELECT ' **** Failed MS SQL Jobs Report **** ' as ' ' 
exec ('SELECT name FROM [' + @InstanceName + '].msdb.dbo.sysjobs A, [' + @InstanceName + '].msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0')

The results displayed are ONLY the jobs that have failed. Knowing if a job was successful is important, knowing when a job has fail can be even more important! In my next article I will show you how I took the results, put them into a table and display them in a report using SSRS.
Example of output returned for failed jobs report

Create/Connect/Execute/Drop Linked Server for Instance: [MARKETSQL01]
-------------------------------------
  **** Failed MS SQL Jobs Report ****
name
-------------------------------------------------------------------------------------------------
Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan1'
DB Backup Job for DB Maintenance Plan 'Full One Time Backup'
Optimizations Job for DB Maintenance Plan 'DB Maintenance Plan1'

The output is showing which jobs failed on MARKETSQL01 server and would continue to display any other failed jobs on the other servers/instances.
You can even create stored procedures on your remote servers/instances and execute them remotely. There are many different possibilities for you to use.
Once the remote execution is completed the linked connection is removed and the collection of  the next server/instance name is inserted in to the InstanceName variable.

-- Remove created link connection
 BEGIN TRY
       EXEC sp_dropserver @InstanceName, 'droplogins'
 END TRY
 BEGIN CATCH
       PRINT 'ERROR! CAN NOT DROP CONNECTION FOR INSTANCE: [' + @InstanceName + '] PLEASE REVIEW'
       INSERT INTO [SQLSrvReports].[dbo].[GeneralError](ErrMsg, ErrInstance, ErrDate, ExecFrom)
             values (error_message(), @InstanceName, getdate(),'SQL Job');
 END CATCH
 -- Collect next server name and start again
 FETCH NEXT FROM myInstances INTO @InstanceName, @lvl, @ver
 END
 -- Close cursor
 CLOSE myInstances
 DEALLOCATE myInstances
 PRINT ' '
 PRINT ' '
 PRINT '*** End of Remote Query Excution- Completed ***'

This loop continues until all the servers/instances are processed and the "End of Remote ..." message is displayed.

What's Next

Now that you have this working, try to see what else you could do with remote execution. I'll be creating additional articles that continues with this idea. Here are just some of reports I created with remote execution.

• Global job status reports for all production systems
• Global Job status reports for Test/Development/UAT systems
• Global job schedule reports which show job name, scheduled times, frequency and time to run
• SQL 2000 and SQL 2005 error log analysis
• Link connection failures that are record from the remote execute code
• SQL Server information report showing server/instance version, patch levels, editions, etc.
• SQL Server up time for all the production servers

As you can see there is a lot of automation you can add to your environment making it easier to be a DBA with many servers/instance to manage. I hope you find this article interesting and useful in your day to day DBA role and would be interested to hear how you use remote execution.

Auto Fix User's SID after Database Restore

Hello Again,

Have you restored a database and then find that user's cannot connect to it? But their ID is in the database whos what's up? Once you perform a restore the SID for the user in the system and the user in the database no longer match. The script below auto corrects this as it scan through each database.

Enjoy,

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DRU1DdkpnZlhKOW8/edit?usp=sharing

SQL Splitz - Found out what's in your SQL Server

If you have been given a SQL server and have no idea as to how it is configured, the script below (by Brent Ozar) will help.

This is a great way to find out alot of information in once quick step. Brent even provides information on why and how to correct issues that were found.

Be sure you visit his site and search for an updated version. Great job Brent and Friends!!

The version I have here is version 17 of the sp_Blitz v17.sql

Thanks,

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DYUFZdDlDTXlnV0E/edit?usp=sharing

See all DMV's in SQL Server

Hi everyone,

Ever needed to find all the system dynamic views or just a specific one? Below are two scripts that do just that.

Thanks,

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DVlkwYWZiZ1h1SHc/edit?usp=sharing

SQL Server System Monitor as a Script

Here is a cool script that shows SQL server system information just like in System Monitor.
Copy and paste the script below, it's very small, or download script here: https://docs.google.com/file/d/0B07PMQYOhF2DZDJQQ1BiTmIyaTA/edit?usp=sharing


-- Process Moniitor Script
SELECT spid
,blocked
,DB_NAME(sp.dbid) as DBName
,program_name
,waitresource
,lastwaittype
,sp.loginame
,sp.hostname
,a.[Text] as [TextData]
,SUBSTRING(A.text, sp.stmt_start / 2,
 (CASE
WHEN sp.stmt_end = -1
 THEN DATALENGTH(A.text)
 ELSE sp.stmt_end
 END - sp.stmt_start
 )/2) AS [current_cmd]
FROM sys.sysprocesses sp
OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) as A
WHERE
spid > 50
ORDER BY
blocked DESC
,DB_NAME(sp.dbid) ASC
,a.[text]

Display OS Wait Stats Information

Hello,

Just finished a script that will show the top 10 OS wait states. This is helpful in determining the issue with SQL server.

For more information, check out Brent Ozar's site http://www.brentozar.com/sql/wait-stats/ where he give more information on this topic.

I hope you find it helpful.

Rudy

Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DNnBkZmlUeTNFSTA/edit?usp=sharing

Convert an SSRS Report to .RDL for Editing

Hello Again,

How to copy / export an existing SSRS report for editing is a common question DBAs get asked. Here is a link that explains this easy process.

http://lukehayler.com/2010/02/download-reports-rdl-files-for-editing-4/

If the link is not available, below are the steps to take.

1. Open SQL Server Management Studio,
2. Log on to the Reporting Services Instance that contains the reports you are looking for
3. Browser through the directory structure to the report that needs to be updated
4. Right-click the report and choose Edit…
5. Now follow the prompts to save the file to a destination of your choice
6. Open BIDS & create a new project/solution
7. Import the saved .rdl file into your SSRS project.
8. Edit your content

 This process is great if you don't have the project or .rdl file but can access the report.

Thanks,

Rudy

SQL 2012 Free eBook

Hello everyone,

Since SQL 2012 is on its way, I thought a link to a free ebook (288 pages) would be a great starter.

Here is the link: http://blog.infoadvisors.com/index.php/2012/03/19/grab-this-free-ebook-on-whats-new-in-sql-server-2012-288-pages/

With all the new features it's good idea to start reading up on them and getting ahead start.

Enjoy,

Rudy

Several Interesting Blog Post and Sites

SQL Server Can Run Databases from Network Shares & NAS
http://www.brentozar.com/archive/2012/01/sql-server-databases-on-network-shares-nas/

Microsoft SQL Server 2012: Evolutionary New Features for the Enterprise Data Platform
http://www.sqlmag.com/article/sql-server/sql-server-2012-features-enterprise-platform-141637

Once More With Feeling: Stop Using Active/Passive and Active/Active Clustering
http://www.sqlha.com/2012/01/09/once-more-with-feeling-stop-using-activepassive-and-activeactive/

How to Improve Database Performance Without Changing Code
http://thomaslarock.com/2012/01/how-to-improve-database-performance-without-changing-code/