Database Backup/Recovery

There are 3 types of backup and recovery method that can be setup on the database.
1- FULL used for PRODUCTION databases only (see troubleshooting). For up to the moment recovery.
2- SIMPLE
3- TRANSACTION Block (good to use when creating lots of transactions in a batch mode). Can be set and reset via a script.

Performance

Tips & Tricks

NOTE: Dues to differing system configurations, Tips and Tricks will have varying results.

• Blobs have a big impact on SQL Server performance. Consider using Remote Blob Services.
• Consider Datatabase Table Groups (you can setup a database with groups of file located on other disks (higher performance). Large files or lots of transaction tables should be considered, for instance Comparable Sales tables, AR Detail table, CENTRAL notes,,,
• Changing Transaction Logs for special batch processing (large transaction)

Troubleshooting

• Backup/Recovery Transaction log type set to FULL on a database that is not backed up can be problematic as it will grow until a deferential backup is made.

Monitoring

See this document for the counters to add in 399-SQLServer-Perfmonance-Poster.pdf.

Event Manager

Very useful.

Task Manager

You can show the activity of all users.
Right-Click to open location to see where the service is run and the database is installed.
Note: You can get the ConfigurationFile.ini from this location. Note: you can use it to modify parameters or create a new db instance.

Perfmon

Counters that can be predefined
note: right-click to update échelle
You can save the html file once setup. Note: works only with Internet Explorer.

Snare and Splunk

Tools to collect and analyze; performance monitoring

Windows Management Instrumentation

So you can monitor other users and connections …

Profiler

You can select the variables to be profiled and then save the file and have it executed at the customer site.
Variables to add:

• T-SQL
• Network

Use the Template = Tuning
You can use Database Tuning Advisor

Journals

Load files … save and execute elsewhere

Tips & Tricks

Upgrading to 6.0/6.1
Database Verification will perform an extensive review of the database tables and columns. Errors include invalid columns types. In order to change the column type in MS SQL SERVER, you need to remove the option ‘Prevent Saving Changes That Require Table Re-Creation‘ in the Tools and Options menu.

French Installation

When installing in French need to change the settings to France instead of Canada otherwise will cause problems.
Charaters:
CI = Case Insensitive
AS = Accent Sensitive

SQL Query windows

When you have an error, click on the error to see where it is in the query.

Task Manager

Right-Click on service to see where it is installed.
You can sort by ID or Service …. See other Monitoring tools

GNA Verify Database Troubleshooting

When running Verify Database in GNA, it checks table and attributes format. In order to easily make that change, uncheck this SQL Server parameter Prevent Saving Changes that Require Table Re-creation. Click here to view the Options Screen.

SQL Server Parameters in Govern

[6.0.11] A new parameter was added in the MSGDatabaseStructureMods.xml for INDEX PageLocks to it can be set to False when needed.
This option is to specify if we want page locks on the index. The default set by MS SQL Server = True

• pagelocks=”False”

Please review with your System Administrator before changing the value of this option as it can have an impact on performance and record locking.
For more information, see Microsoft TechNet

See Also

MS SQL Server and Visio