Best Known Method for Avalanche 6.x Database Maintenance

Version 5

    Verified Product Versions

    Avalanche 6.1Avalanche 6.0Avalanche 6.2


    Scenario:

     

    This article is designed to walk users through identifying DB fragmentation, how to reindex and also how to do a DB backup. Performing DB backups and maintenance such as reindexing is important to ensure your avalanche 6.x installation is running as efficient as possible.

    Environment:

     

    Based on a Server 2012 R2 with SQL 2008 Express

     

    Reindexing:

     

    Index fragmentation can happen when the logical sequence of pages is disrupted or when an index page contains less than its maximum amount of data, creating a gap in the data page or index. As indexes become fragmented, data reads become inefficient when accessing tables and slow database performance. please note that Re indexing may improve performance of load times varyingly from one customer to the next.

     

    You will need to set the database to SINGLE USER MODE before reindexing the DB. This is best done during a maintenance window.

     

    Script for sql fragmentation was obtained from:

    https://gallery.technet.microsoft.com/scriptcenter/Check-SQL-Server-a-a5758043

     

    Script for reindex was obtained from:

    https://community.landesk.com/support/docs/DOC-4362

     

    Verify Database needs Reindexing

     

    1. Download IndexFragmentation.zip which is attached to this file
    2. Open your SQL management Studio and log in
    3. Drag the IndexFragmentation.sql file from the Indexfragmentation.zip file into the instance
    4. Run Query

     

    To set a database to single-user mode in SQL 2008

     

    1. Disable all services that would touch SQL. This would include Tomcat, Enterprise, SDS and MDS
    2. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
    3. Right-click the database to change, and then click Properties.
    4. In the Database Properties dialog box, click the Options page.
    5. From the Restrict Access option, select Single_User.
    6. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

     

    Starting SQL Server in single user mode:

     

    1. 1. Start a command session (start ; Run ; "cmd" and press the enter key).
    2. 2. To ensure that SQL Server is not running type: "net stop MSSQLServer" This may say that this will also stop another service (typically SQLSERVERAGENT ), in which case allow it to continue. It will also indicate if the service is already stopped.
    3. 3. To start SQL Server in single user mode type: "sqlservr -m" If this is not recognised then cd to " C:\Program Files\Microsoft SQL Server\MSSQL\BINN" and try again.

     

    Reindex instructions for Microsoft SQL Server database

     

    1. Download SQLReindex.zip attached to this article
    2. Open your SQL Management Studio and log in
    3. Drag the .sql file within the SQLReindex.zip file into the instance
    4. Run The query

     

    Restoring DB to working state

    1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.
    2. Right-click the database to change, and then click Properties.
    3. In the Database Properties dialog box, click the Options page.
    4. From the Restrict Access option, select Multi_User.
    5. If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

     

    SQL Backup

     

    It is strongly recommended that our customers keep backups of their Avalanche servers. These can be Daily, Weekly, or Monthly whatever suits your Corporate Policy.  Back ups can be VM snapshots or sql and avapackage backups.

     

    Backup an SQL Database

     

    1. Open your SQL management Studio and log in
    2. Locate your Databse (Avalanche or AvaStat) and right click to pull up menu
    3. Select Tasks then select backup
    4. Ensure the Backup Type is set to Full
    5. Leave default location or Remove the default destination and add a location of your choice.

    Steps for Automating backs can be found here: https://support.microsoft.com/en-us/kb/2019698

    Restore an SQL Database

     

    1. Open your SQL management Studio and log in
    2. Locate your Databse (Avalanche or AvaStat) and right click to pull up menu
    3. Select Tasks then select Restore > Database
    4. Select the proper Database to Restore to and which DB to restore from