|
|
|
|
|
|
the.faq
This section is being moved to our online forums. Please feel free to register and contribute your feedback to the virtualDBA project.
Installation
Configuration
Maintenance
Audit
Reporting
System
the.installation
Question:
I receive the error "virtualDBA installation failed. View the setup log [vw_adm_setup_log] for more details." when I run the setup procedure. How do I get the installation to work?
Answer:
In SQL Server Management Studio, run the following query and match the output to the errors listed in the table below:
select * from [virtualDBA].[dbo].[vw_adm_setup_log];
| error |
resolution |
| SQL Agent service must be running |
The virtualDBA system installs a number of SQL Agent jobs during the setup/installation. Start the SQL Agent service and re-run the setup procedure. |
| specified '@server' is invalid |
The name of your SQL instance does not match with its network name. Run a comparison between @@servername and ServerProperty('ServerName') - if the two values are not the same, match your @@servername name by using sp_dropserver/sp_addserver (see Books Online for more details.) Caution - changing the SQL name of your server could result in issues with your database/applications. Please consult your development team(s) before making any changes. |
| SET options have incorrect settings |
Before running the setup procedure, make sure that the session settings QUOTED_IDENTIFIER and ANSI_PADDING are set to 'ON'. |
| Other |
Please contact eleven27 support (support@virtualdba.ca) for assistance. |
the.configuration
Question:
How do I get the virtualDBA alerting features working?
Answer:
To enable the virtualDBA alerting features, you must first setup an SQL Server database mail profile (see Books Online for more details or contact eleven27 support.) Once the profile has been setup, you can enable the system alerts using the following:
update [virtualDBA].[dbo].[vw_config_system_alerts]
set enabled = 1,
alert_by_eventlog_enabled = 0,
alert_by_mail_enabled = 1,
mail_profile = '<your_dbmail profile>',
mail_recipient_email_address = '<dba@yourcompany.com>',
html_enabled = 1;
In order to enable the virtualDBA reporting feature:
update [virtualDBA].[dbo].[vw_config_reporting]
set enabled = 1,
mail_profile = '<your_dbmail profile>';
update [virtualDBA].[dbo].[vw_config_reporting_templates]
set agent_enabled = 1,
email = '<dba@yourcompany.com>';
Question:
I have installed virtualDBA but I need to disable some of the maintenance features on a few databases. How do I do this?
Answer:
You can enable/disable and configure all maintenance specific tasks (consistency checks, index rebuilds, backups, etc.) through the following view:
[virtualDBA].[dbo].[vw_config_maintenance_db]
Question:
I accidently updated a number of maintenance/monitor settings for all of my databases instead of just the one I wanted. Is there a way to get my previous settings back?
Answer:
Definitely. When you make changes to the maintenance and monitoring configuration views, a snapshot of the pre-changed rows is saved in a number of history views:
| view |
description |
| dbo.vw_history_config_maintenance_db |
View historical snapshot data for any database maintenance configuration changes. |
| dbo.vw_history_config_maintenance_log |
View historical snapshot data for any transaction log maintenance configuration changes. |
| dbo.vw_config_history_monitor_db |
View historical snapshot data for any database monitoring configuration changes. |
| dbo.vw_config_history_monitor_dbm |
View historical snapshot data for any database mirroring monitor configuration changes. |
Question:
I have installed virtualDBA and configured the proper maintenance settings for my databases. I plan to add a number of databases in the future but I want them to use a different backup path when virtualDBA detects and configures them. Is this possible?
Answer:
You can pre-configure the default maintenance and monitoring settings using the following views:
| view |
description |
| dbo.vw_config_default_maintenance_db |
Set the default database maintenance settings for all new databases. |
| dbo.vw_config_default_maintenance_log |
Set the default transaction log maintenance settings for all new databases. |
| dbo.vw_config_default_monitor_db |
Set the default database monitoring settings for all new databases. |
These views allow you to set the defaults for any new database that virtualDBA detects - it does not change the settings for your existing databases.
Question:
I have a custom stored procedure or third-party application (ie LiteSpeed) that I use to backup one or more of my databases. Can I still use this? Can I have virtualDBA notify me if it fails?
Answer:
Absolutely. You can setup a maintenance override task and enable it in place of the virtualDBA backup process. The override can be setup in the dbo.vw_config_maintenance_overrides view as follows:
| column |
description |
| override_id |
The unique ID of the override record.
|
| alert_enabled |
Send an alert if the override fails. This is dependent on the error code returned by the override TSQL statement(s). |
| tsql |
The TSQL code. This can be any valid TSQL statement including stored procedure execution, DML, third-party extended stored procedures, etc. |
| description |
A summary of the override record. This is used for descriptive purposes only. |
Once the override has been setup, simply add the ID to the maintenance configuration for the database(s) you wish to override:
update [virtualDBA].[dbo].[vw_config_maintenance_db] set bu_db_override_id = <override_id> where database_name = '<database_name>';
The next time the maintenance process runs the backup task, it will automatically run the override syntax. All core maintenance tasks have the option to include an override on both a daily or weekly basis.
If you wish to disable an override, simply set the ID to 0 in the maintenance configuration:
update [virtualDBA].[dbo].[vw_config_maintenance_db] set bu_db_override_id = 0 where database_name = '<database_name>';
Question:
How do I use a third-party command line tool to compress my backups?
Answer:
Third-party command line tools can be integrated into the virtualDBA backup routines by updating the maintenance configuration views to include the 'core' compression syntax - virtualDBA will handle the file naming convention.
For example, if you wanted to include compression using 7-Zip:
update [virtualDBA].[dbo].[vw_config_maintenance_db] set
bu_db_external_compression_enabled=1,
bu_db_external_compression_command='"c:\program files\7-zip\7z.exe" a -t7z -m0=lzma -mx=1 -mfb=64 -md=64m',
bu_db_external_compression_extension='7z',
where database_name = '<database_name>';
Any command line based compression utility is supported however you may need to test variations of the 'core' settings to get it to work. If you have any problems with a specific compression utility, contact eleven27 support for assistance.
Question:
How do I enable the backup-and-copy feature?
Answer:
You can enable this feature by executing the following command for each database that you want copied:
update [virtualDBA].[dbo].[vw_config_maintenance_db] set
bu_db_file_copy_enabled = 1,
bu_db_file_copy_unc_path = '<unc_path>'
where database_name = '<database_name>';
If you have enabled third-party backup compression, you can copy the compressed file instead of the original backup file:
update [virtualDBA].[dbo].[vw_config_maintenance_db] set
bu_db_file_copy_compressed_file_enabled = 1
where database_name = '<database_name>';
Note: The SQL Server Agent service account must have access to the <unc_path> path.
Question:
Why is the native SQL 2008 compression unavailable in virtualDBA?
Answer:
Support for native SQL 2008 compression will be available in the next major release of virtualDBA (v4.0.0)
Question:
I have installed virtualDBA but all of the database maintenance jobs are scheduled/running at the same time? Can I change the schedule for a few databases?
Answer:
Absolutely. virtualDBA uses a default schedule for all databases but you can reschedule this for any database agent according to your environment.
The next major release of virtualDBA (v4.x) includes a "smart scheduling" technology that will "offset" each job agent based on the resources and duration required to maintain each database.
Question:
How do I configure the database monitoring features?
Answer:
You can configure the database monitoring features using the dbo.vw_config_monitor_db view.
| column |
description |
| database_name | The database name. |
| fxs_enabled | Enable/disable the file expansion/shrink monitor. This requires the SQL Server default trace to be running (see BOL or contact support@virtualdba.ca for asistance.) |
| fxs_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| ios_enabled | Enable/disable the database file IO stall monitor. |
| ios_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| ndx_miss_enabled | Enable/disable the missing index monitor. |
| ndx_miss_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| ndx_ops_enabled | Enable/disable the index operational statistics monitor. |
| ndx_ops_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| ndx_usg_enabled | Enable/disable the index usage monitor. |
| ndx_usg_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| qry_blk_enabled | Enable/disable the blocked query monitor. |
| qry_blk_min_wait_sec | The minimum amount of time (in seconds) a query sits in a blocked state before the monitor detects it. |
| qry_blk_alert_enabled | Enable/disable an email alert when a blocked query is detected. |
| qry_blk_alert_email_cc | An email CC address for blocked query alerts. Multiple addresses should be semi-colon separated. |
| qry_blk_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| qry_run_enabled | Enable/disable the long running query monitor. |
| qry_run_min_exec_sec | The minimum amount of time (in seconds) a query is running before the monitor detects it. |
| qry_run_alert_enabled | Enable/disable an email alert when a long running is detected. |
| qry_run_alert_email_cc | An email CC address for long running query alerts. Multiple addresses should be semi-colon separated. |
| qry_run_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| qry_sts_enabled | Enable/disable the query statistics monitor. This monitor will track query CPU/IO/Plan re-use/duration/etc. statistics. |
| qry_sts_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| usg_db_enabled | Enable/disable the database usage monitor. |
| usg_db_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| usg_obj_enabled | Enable/disable the database per-object/per-index usage monitor. |
| usg_obj_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
| ft_crawl_enabled | Enable/disable the FullText crawl monitor. |
| ft_crawl_duration_sec | The minimum amount of time (in seconds) a crawl is running before the monitor detects it. |
| ft_crawl_alert_enabled | Enable/disable an email alert when a long running crawl is detected. |
| ft_crawl_log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
Question:
Is there a way to monitor the overall disk space usage on my systems?
Answer:
Definitely. You can enable a disk subsystem monitor using the [dbo].[vw_config_monitor_srv_disks] view.
| column |
description |
| enabled | Enable/disable the disk monitor. |
| alert_enabled | Enable/disable an email alert when the estimated days remaining threshold has been met. |
| alert_estimated_days_remaining | The minimum amount of days remaining before the disk space runs out and the monitor detects it. Note: this is an estimated value and may appear negative if a large number of files are deleted from the disk. |
| log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
If you want manually track the disk space changes on a daily basis, you can do so via:
select * from [virtualDBA].[dbo].[vw_monitor_srv_disks];
This will provide you with a daily breakdown of disk usage metrics.
Question:
How do I monitor TempDB usage?
Answer:
You can enable the TempDB usage monitor by using the [dbo].[vw_config_monitor_srv_tempDB] view.
| column |
description |
| enabled | Enable/disable the TempDB usage monitor. |
| min_per_session_page_count | The minimum amount of pages (8K) a connected session must consume before the monitor detects it. |
| alert_enabled | Enable/disable an email alert when the pages threshold has been met. |
| log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
You can manually view the TempDB monitor usage via:
select * from [virtualDBA].[dbo].[vw_monitor_srv_TempDB];
Question:
Is there a way to monitor the SQL Server logs?
Answer:
Absolutely. You can monitor the SQL logs and send alerts when specific log entries are detected. The log monitor is configurable using the [dbo].[vw_config_monitor_srv_logs] view.
| column |
description |
| enabled | Enable/disable the SQL log monitor. |
| backlog_hrs | The maximum amount of time (in hours) that the monitor will use when detecting log entries based on the log date. |
| log_retention_hrs | The amount of log history (in hours) to retain for this monitor. |
Question:
I have enabled the SQL Server log monitor but I want to be alerted when certain log entries occur. How do I do this?
Answer:
You can enable/disable email alerts for the SQL log monitor using the [dbo].[vw_config_monitor_srv_logs_alerts] view.
| column |
description |
| enabled | Enable/disable the SQL log monitor alerts. |
| log_keyphrase | An SQL Log keyphrase that is picked up by the monitor. For example 'Backup Failed'. |
| email_subject_tag | A tag that will be appended to the start of the email subject field (ie Server Restarted - ). This is very useful if you have email filters in place for a specific email address and only want to see certain messages. |
Question:
I have enabled the SQL Server log monitor and alerting features but I need to filter out some of the alerts I am receiving. Is this possible?
Answer:
Definitely. You may have added ERROR as an alert keyphrase but you don't want to receive an alert when the error log has been reintialized. You can setup a filter for this using the [dbo].[vw_config_monitor_srv_logs_filter] view.
| column |
description |
| enabled | Enable/disable the SQL log scanner filter. |
| log_keyphrase | A keyphrase used to filter the log entries. |
For example, to filter out the SQL log reinitialization mentioned above, simply add the filter keyphrase: -e%errorlog
The filter keyphrase supports wildcard (%) characters!
the.maintenance
Question:
If virtualDBA is maintaining all my database backups, how will I know which files to restore in the event that I need to recover a database?
Answer:
virtualDBA dynamically builds recovery scripts as each database or log is backed up. You can access the recovery script for a particular database by executing the following:
select restore_script from [virtualDBA].[dbo].[vw_maintenance_current_backups] where database_name = <database_name> order by backup_start_date;
restore database [mydb] from disk = 'd:\sqlbkup\11NOV2010T022426D_mydb_full_bkup.bak' with file = 1, replace, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T022426L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T023000L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T030000L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T033001L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T040000L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T043001L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T050001L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T053001L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T060000L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T063000L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T070000L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T073000L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T080000L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T083001L_mydb_log_bkup.bak' with file = 1, norecovery;
restore log [mydb] from disk = 'd:\sqlbkup\11NOV2010T090001L_mydb_log_bkup.bak' with file = 1, recovery;
the.audit
Question:
How do I enable DDL Audting?
Answer:
To enable DLL auditing, execute the following command:
update [virtualDBA].[dbo].[vw_config_audit] set
enabled = 1,
where audit_type = 'ddl';
The DLL audit will be enabled once the next scheduled DDL Audit Agent runs.
There are 4 separate audit types that can be enabled - DDL, Security, Locks and Mirroring.
To check the status of an audit type or to see if any events have not been logged:
select * from [virtualDBA].[dbo].[vw_queue_audit];
To view auditied events:
| view |
description |
| dbo.vw_audit_ddl |
View all DDL audit events. |
| dbo.vw_audit_locks |
View all deadlock occurence events. |
| dbo.vw_audit_mirroring |
View all Database Mirroring state change events. |
| dbo.vw_audit_security |
View all security related events. |
Question:
Is there a way to receive an email alert when a DDL audit event occurs?
Answer:
Absolutely. By default, the audit events are pre-configured with alerting set to disabled. You can change this default setup by updating or adding records to the dbo.vw_config_audit_alerts_ddl view.
| column |
description |
| alert_enabled |
Enable the alerts for this event. |
| alert_event |
The audit event name. This must be a valid DDL Event Notification. |
| alert_database_name |
The name of the database for this event. A value of NULL represents all databases. |
| alert_email_cc |
By default, all alerts will be sent to the account setup in the dbo.vw_config_systems_alerts view. If you wish to cc an email address on a specific event, you can do so by adding the account(s) to this column. Multiple accounts can be added using a semi-colon separator. |
If you want to enable alerts for all events/databases, simply run the following:
update [virtualDBA].[dbo].[vw_config_audit_alerts_ddl] set
enabled = 1;
If you want to enable all alerts for a specific database, you can add the new records as follows:
insert [virtualDBA].[dbo].[vw_config_audit_alerts_ddl] select 1, alert_event, <database_name>, null from [virtualDBA].[dbo].[vw_config_audit_alerts_ddl] where alert_database_name is null;
the.reporting
Question:
I have enabled the reporting features but I don't receive any of the default reports. How do I enable these?
Answer:
You can enable the default reports by executing the following command:
update [virtualDBA].[dbo].[vw_config_reporting_templates] set agent_enabled = 1;
Question:
How do I create a custom report?
Answer:
You can create a custom report (or modify an existing one) using the dbo.vw_config_reporting_templates view.
| column |
description |
| agent_enabled |
Enables the generation of a report through the scheduled reporting agent. If you wish to schedule a report outside of the reporting agent, you can set this column to 0 and setup a custom job. |
| report_id |
A unique ID for the report. |
| title |
The report title. |
| description |
A summary description of the report. |
| tsql |
The TSQL statement. This can be any valid TSQL code and can span any database or linked server configuration that the SQl Server Agent service account has access to. There is no support for stored procedures in version v3.x.x. |
| email |
A semi-colon separated list of email accounts to send the report to. |
| include_xml |
Include a secondary email containing an XML based attachemnt of the report results. |
| include_xml_only |
Send a single report containing an XML based attachemnt of the report results only (no HTML.) |
| log_retention_hrs |
The length of time (hours) to maintain reporting history. |
| alert_threshold_enabled |
Enable an email alert if the time taken to generate the report exceeds a threshold value. |
| alert_threshold_report_duration_sec |
An alert threshold value representing the length of time (seconds) taken to generate a report. |
| html_font_face |
The HTML font face. |
| html_title_cell_background_colour |
The HTML title background colour. |
| html_title_cell_font_colour |
The HTML title font colour. |
| html_data_cell_background_colour |
The HTML data background colour. |
| html_data_cell_font_colour |
The HTML data font colour. |
| html_table_border_width |
The HTML table border width. |
| html_table_border_width |
The HTML table border width. |
| html_cell_padding |
The HTML table cell padding. |
| html_logo_url |
An HTML logo file/image. Can be a UNC path or a URL. |
If you want to schedule a report outside of the reporting agent, you can setup a job step using the following code:
exec [virtualDBA].[dbo].[up_rep] <report_id>;
Question:
Does virtualDBA keep a log of the reports?
Answer:
Yes. virtualDBA has a central logging view that tracks the outcome of all reports.
select * from [virtualDBA].[dbo].[vw_reporting_log];
Question:
Aside from the default reporting templates that come with virtualDBA, do you have any other templates that I can use to monitor my system?
Answer:
Absolutely. We have provided a number of sample reports below and will continue to add this list. You will need to modify the <report_id> and email address for each template to suit your environment.
Visit us often to check out the latest templates or follow us on Twitter to receive updates. Also, if you have come up with some creative templates and want to share them with other virtualDBA users, please feel free to get in touch in with us and we can add them to the list!
[Template: Index Usage]
List index usage information for indexes that have high scan-to-seek to user-to-maintenance ratios.
insert [virtualDBA].[dbo].[vw_config_reporting_templates]
(
agent_enabled,
report_id,
title,
description,
tsql,
email,
log_retention_hrs,
alert_enabled,
alert_threshold_report_duration_sec
)
values
(
1,
<report_id>,
'Database Profile - Index usage statistics',
'Database Profile - Index usage statistics where scans vs seeks > 75% or maintenance vs user > 75%.',
'select log_date, database_name, object_name, index_name, user_seeks, user_scans, user_lookups, last_user_update, maintenance_updates maintenance_updates, 100*cast((case when user_seeks = 0 then 1 else user_scans*1.0/(user_seeks+user_scans) end) as decimal(10,3)) percent_scans_vs_seeks, pct_maintenance_vs_user percent_maintenance_vs_user from [virtualDBA].[dbo].[vw_monitor_db_index_stats_usage] where (log_date between cast(convert(varchar(30), getdate(), 12) as datetime)-1 and cast(convert(varchar(30), getdate(), 12) as datetime)) and user_seeks is not null and (pct_maintenance_vs_user > 75 or (100*cast((case when user_seeks = 0 then 1 else user_scans*1.0/(user_seeks+user_scans) end) as decimal(10,3)))>75) order by pct_maintenance_vs_user desc;',
'support@mycompany.com',
168,
0,
5
);
[Template: Buffer Pool]
Provides average buffer pool usage (in MB) on a per database basis. This template assumes that you have enabled the database memory monitors.
insert [virtualDBA].[dbo].[vw_config_reporting_templates]
(
agent_enabled,
report_id,
title,
description,
tsql,
email,
log_retention_hrs,
alert_enabled,
alert_threshold_report_duration_sec
)
values
(
1,
<report_id>,
'Database Profile - Buffer Pool Memory Usage',
'Database Profile - Buffer Pool Memory Usage',
'select database_name, avg(buffer_pool_size_current_mb) avg_sample_buffer_pool_size_mb from [virtualDBA].[dbo].vw_monitor_db_memory (nolock) group by database_name;',
'support@mycompany.com',
168,
0,
5
);
the.system
Question:
Does virtualDBA have it's own logs?
Answer:
Yes. virtualDBA has a central logging view that tracks all errors, events, notifications, etc.
select * from [virtualDBA].[dbo].[vw_system_log];
This section is being moved to our online Forums! Please feel free to register and contribute your feedback to the virtualDBA project.
|
|
|
|
|
|
|
|
