SQL diagnostic manager

Use SQL diagnostic manager to compare SQL Server performance data on two different days

Ever wonder if all of those upgrades, tweaks, or new hardware you’ve installed have really improved the performance of a specific SQL Server? Now you can get proof. SQL diagnostic manager allows you to easily compare SQL Server performance data from two different days – for example, two months ago and today. You can even view this data – anytime, anywhere -- through the new SQL diagnostic manager Web Console.

How it works:
Within SQL diagnostic manager, go to the “Statistics” section. Then select the specific days you wish to compare. Use key to multi-select days. Then change the 'sample' field in the upper left to 'minutes' or 'days'. The information will be displayed in simple graphic form for easy comparison. You can also easily export these graphs or save the gr aphs as bitmaps for use in other documents.
 

Report Example 1

Report Example 2

This handy feature allows users to quickly and easily compare SQL Server data from two different points in time or on two separate SQL Server instances. With the SQL diagnostic manager Web Console, you can see multiple views simultaneously in order to analyze data collected on multiple panes.The Web Console uses views that persist within an individual session. This means that when you open multiple Internet Explorer windows, you will see the same information refreshed on each view.

To compare multiple views in seperate browser windows, follow these steps:

1. Navigate to the view you want to examine.
2. Select a refresh interval of “never” from the drop down list at the top right of the page.
3. Open a new browser window (ctrl-N).
4. Navigate to the view you want to compare to your current view. You may now compare the two views.

From the Advanced Alerting Options screen, you can monitor such occurrences as 'Step Failure' and 'Conflict'. Configure custom alerting actions, such as send an SMTP email, run a job on another server, or run a SQL statement.

Installation tips to achieve optimal performance:

• Choose a Robust Machine: SQLdm should be installed on a client machine which possesses the capability to monitor all enterprise SQL servers. As the tool is primarily designed for use by a Database Administrator (DBA), it is recommended that SQLdm be installed on the DBA’s workstation. As the number of servers monitored increases above 15, we suggest that a separate, dedicated client workstation be set up for SQLdm.
  
• Avoid Using Same Machine: It is not recommended that SQLdm be installed on the same SQL server that it will be monitoring. The reason for this is that the SQLdm service and GUI may increase the server resource load separate from the minimal resource cost incurred by the SQLdm refresh queries.
  
• Configure for Multiple Users: Normally, SQLdm uses less than 1 second of CPU time every refresh interval. However, having multiple SQLdm clients accessing a SQL server can increase the server resource load. If there are multiple clients monitoring a single server, each may use up to a second of server CPU time. This adds up quickly. Therefore, if multiple DBAs need access to SQLdm, one good option is to have your SQL servers monitored by a single SQLdm workstation that users connect via terminal service or another remote connection.

How to view real-time status of servers or server groups, using SQL diagnostic manager v3.5:

1. Go to the “Details” pane view
   
2. Scroll to the far right and you will see Server Group, Application Group, Department and Location columns
   
3. Enter information in these fields to identify each server by group
   
4. To filter server groups, configure the “Custom View” (<Create/Edit View> located at the bottom of the “Detail” pane dropdown)
   
5. You can use wild cards to filter by partial group names
   
6. Save your Custom View with a unique name
   
7. Select your newly created Custom View from the dropdown to see real time status for the filtered group

How to write SQLdm alert messages directly to an ODBC data repository:

1. To set up, go to: Alerts, Configure Destinations, Enable Alerts, Configure Alert Destinations
   
2. Proceed to the ODBC tab
   
3. Set up a SQL server database table as a data source. The easiest way to do this is to create a DSN-less connection using the SQL Server driver, pointed to a database and table where you want to collect the alerts
   
4. Be sure to click Test and check to see if a test alert has been sent to the table
   
5. Click Apply and OK to permanently apply the ODBC destination
   
6. Based on the text of an alert, you can have a trigger or recurring SQL server job send email alerts or execute additional statements or batches in response

SQLsafe

Deploying multiple management services in your SQLsafe environment

One of the primary concerns for a backup and recovery tool is fault tolerance. SQLsafe's architecture ensures that a backup operation will be performed, even if the management service and repository database components cannot be contacted. The backup service can even be installed on a failover cluster to ensure constant availability of the server and its archives. But how do you maintain a current SQLsafe repository database, in case the computer hosting the management service goes offline? Simply, install the management service component of SQLsafe on more than one machine.

Enterprises with sites in several geographically diverse locations may also find that having multiple management services is a good way to organize and manage the result information coming from each of those locations. A management service may communicate with an individual domain and route its servers' restores to a centralized SQLsafe repository database.

You can even overcome port security issues this way. Contact Idera Support if your particular environment requires non-standard TCP/IP port use for communication between the management service and backup service, but it should be possible to configure a management service to work with the available ports when communicating.

First, a few basics about the SQLsafe architecture you may be unaware of that can bring your backup and recovery architecture to all new heights!

• Multiple SQLsafe management services can share the same repository.
  
• SQLsafe backup agents never lose status. So, if the repository is unavailable, SQLsafe will cache status until the repository is up again.
  
• You can put smarts into your backup scripts to check for an available management service and dynamically set.

How to install the management service component of SQLsafe on more than one machine:

1. Copy the installation package for SQLsafe onto the machine where you want to install an additional management service.
   
2. Unzip and begin the install. Select the option for a "Custom" install, and choose only the SQLsafe Management Service from the list.
   
3. The remainder of the installation walkthrough will allow you to configure the management service with the following:
     
   • The SQLsafe repository database the service will send status updates to
   • The SQL logon to access the database with, if you use SQL authentication.
   • The Windows account that the management service will run under. When using Windows authentication, this account must have read/write access to the repository database.

If the server that hosts your primary SQLsafe management service goes offline for any reason, you can specify a different management service to use in the SQLsafe management console.

1. Click File -> Edit Preferences.
   
2. On the General tab, change the machine name for the SQLsafe management service to the machine where the failover service is located.
   
3. Click "Retrieve" to ensure that the service is able to be contacted.
   
4. Click Apply and OK to finalize the change.

(If using a CLI script, you would simply add the parameter "-managementserver [Servername]" to the backup or restore script, and make sure that [Servername] specifies the machine where your failover management service resides.)

For more information, or assistance, please contact Idera Customer Support Team at support@idera.com.

Save time backing up multiple databases

Most backup products immediately launch an executable whenever you request a backup -- making it difficult or impossible for you to select multiple databases for backup without stressing your systems. SQLsafe, on the other hand, will allow you to select multiple databases for backup and will automatically queue those backups to occur in a logical order. This will save you hours of administrative time, help you ensure that every database is backed up without fail, and will never affect the performance of your SQL Server environment.
 

SQLsafe Queued Backups View

SQLsafe provides an easy-to-use enterprise console that allows database administration staff to quickly and easily check the status of all backup and restore commands in real-time. Therefore, regardless of where or how the backup commands were initiated (SQLsafe Management Console, command-line, XSP, or scheduled job) the SQLsafe console displays all in-progress backups as well as historical backup and restore status.

How to install the SQLsafe backup agent on a clustered SQL Server

1. Log on to each physical SQL Server in your cluster and complete a custom install of SQLsafe that includes only the Backup agent. For more information about installing SQLsafe, see the User Guide.
   
2. Log on with an administrator account to the active physical server of the cluster, and start the Cluster Administrator application. By default, a shortcut to this application is located in Administrative Tools. Navigate to and expand the active cluster server.
   
3. Click Active Resources. In the right pane, right-click and select New > Resource.
   
4. On the first wizard page, specify the following values, and then click Next.
   • Name: SQLsafe Backup Service
     
   • Description: SQLsafe Backup Service
     
   • Resource type: Generic Service
     
   • Group: The appropriate cluster group name
5. Specify both the active and the passive servers as possible owners, and then click Next.
   
6. Specify Cluster IP Address and Cluster Name as dependencies, and then click Next.
   
7. Specify the following service paramenters, and then click Next.
   • Name: SQLsafe Backup Service Start parameters: Leave blank Use network name for computer
     
   • name: Unchecked
8. Specify no action needed in Registry replication, and then click Finish.
   
9. Double-click on SQLsafe Backup Service, and select the Parameters tab.
   
10. Check Use network name for computer name, and then click OK.
    
11. Right-click SQLsafe Backup Service, and select Bring Online.

How to increase backup performance using file striping

Striping backups to multiple files - even on the same disk - can significantly increase performance because of the use of multi-threaded processing. You can save all the stripes to a single location or spread across multiple disks to distribute disk I/O.

To stripe backup files from the management console, simply select the backup archive file name and press tab to automatically add a stripe. To stripe using the command-line interface, simply append each additional stripe filename prefixed with the "-additionalbackupfile" switch, as shown:

"c:\program files\idera\sqlsafe\SQLsafeCmd" Backup pubs \\myserver\backups\TESTSERVER_pubs_01_full.safe -backupname "pubs" -backupdescription "pubs" -init -compressionlevel 1 -additionalbackupfile \\myserver\backups\TESTSERVER_pubs_02_full.safe -additionalbackupfile \\myserver\backups\TESTSERVER_pubs_03_full.safe -managementserver TESTSERVER -windowsusername myusername -windowspassword mypassword

The performance increase will differ by environment, so you may want to experiment to see what will work best for your organization.

SQL compliance manager

Create a Schema Change Alert using SQL compliance manager

Let developers know you’re watching when they make schema changes by creating an alert as follows:

1. From Alert Rules, select New Alert Rule...
   
2. Select DDL and click next.
   
3. Select SQL Server Object type (leave everything open is you would like to watch everything globally, then you are truly "big brother"). Click “Next”.
   
4. Select Login Name. Click on specified words, add their login to the list, and then click “Next”.
   
5. Select Email Notification:
• Click on the specified addresses.
  
• Add their email address and you may optionally add yours as well.
  
• Click Next.
6. Give the rule a name like “Big Brother is watching” and a description.
   
7. Click on Alert Message. Customize the message to let the ruler breaker know you’re watching and click ‘OK’.
   
8. Click Finish.

Use SQL compliance manager pre-defined reports or create your own using Microsoft® Visual Studio®

SQL compliance manager gathers a wealth of critical auditing data by tracking DDL, DML, DCL, failed logins, logins, select statements and more on any of the SQL Servers you specify. The data can be sorted, searched and exported as an RDL file – giving you the flexibility to create any customized report you require using a variety of tools. One popular method leverages your existing MS® Visual Studio®.NET IDE. And, it's easy to use regardless of your level of development expertise.

How to quickly create customized reports:

1. From Microsoft® Visual Studio®.NET, click new project, and select Business Intelligence projects, then select Reports Project
   
2. Choose your data source, including server name and security model
   
3. Add your selected datasets (tables, stored procedures, queries, etc) to obtain data from
   
4. Drag and drop text boxes, logos, and other objects onto the report to customize the graphical appearance.

That's all there is to it! Now you can create rich, dynamic reports that will knock your auditor's SOX off!

SQL compliance manager pre-defined reports

SQL compliance manager provides a comprehensive library of pre-defined reports that will help you audit and meet regulatory requirements with ease. Key reports include the following:
 

SQLschedule

Easily rewrite a job's schedule in seconds using SQLschedule

Using either of the following methods, you can instantly rewrite a job's schedule on the SQL Server. The changes to the job will instantly be reflected in the User Interface.

Method 1:

• Use 'Drag and Drop' functionality in the Outlook Calendar style pane, located on the right hand side of the screen.
  
• Left click and hold the job, drag to the desired location, and release.

Method 2:

• Right-click on the job
  
• Select properties – Opens SQL Server’s native Job Scheduler window to enable changes to the job using the standard scheduling method

Configure advanced job alerting

It's easy to customize alerts to notify of failed jobs, perform an action on another server, execute a SQL statement, and more!

To create custom alerts:

• Left click on the job from the Explorer Tree, located on the right side of the User Interface.
  
• Left click the 'General Actions' Vertical Tab to open the Advanced Alerting Options screen, located on the left side of the screen.

From the Advanced Alerting Options screen, you can monitor such occurrences as 'Step Failure' and 'Conflict'. Configure custom alerting actions, such as send an SMTP email, run a job on another server, or run a SQL statement.