Azure for LemonEdge - Database Backup

Here's how to quickly Copy, Prepare, Export, and Share your LemonEdge Database. Optional Data Obfuscation is available for instances where sensitive data is involved.

Prerequisites:

• Azure Portal Admin with access to your associated LemonEdge
  • Resource Group & core Resources contained within
    • SQL Database – Credentials, query, & config
    • Storage Account - any backup/shared Containers within
    • App Services –Web/Task Services
    • SQL Server – necessary for Obfuscation
• LemonEdge WPF/Desktop Client - necessary for Obfuscation
  
  • LemonEdge Keys – License and SaS Key(s) which have been provided separately via (.txt) document
  • Current and corresponding WPF/Desktop Client available to download at download.lemonedge.com with your SaS Key(s) mentioned above
• (Optional) Updated versions of the following Microsoft tools:
  • SQL Server Management Studio
  • SQL Server - Developer version preferred
  • Azure Storage Explorer

Quick steps from the Azure Portal (No Obfuscation):

1. Copy the DB
2. Clear unneeded item from the DB
3. Export the DB
4. Share via SAS token
5. Delete or store copy once transferred

**Should Data Obfuscation be needed, please perform that task between steps 1 and 2 above. It is essential the Obfuscation take place on the copy and not the original DB as it is an irreversible data-masking process. Instruction on Obfuscation here.

Copy the DB

1. Login to https://portal.azure.com, navigate to your LemonEdge Resource group, and ensure you have visibility to the Resources>Types as shown below:

     2. Select the SQL database to be copied and click Copy on the subsequent screen.

3. On the Copy database screen, you may select all defaults with the exception of the Database name field. Here we ask that you include a clear indicator within the name that this is the copy as all subsequent steps in this process will be performed on this copy. Adding a date in the name is helpful as well. Click the blue Review+create button once named. Click the blue Create button on the next screen to begin process.

4. You will see a Deployment is in Progress screen followed by a confirmation of deployment. Click the blue Go to resource button to access the DB copy and proceed to the next steps. **No further actions are to be taken on the original database after this deployment is complete.

Clear unneeded items from DB

Prepare the DB for Export - perform the following steps on the DB to make it more manageable and easier to restore:

• Sometimes legacy SQL Wrappers have orphaned or incorrect references. The system wrappers get recreated on the first call by the system and can therefore be removed. Clear the wrappers with the query below.

• When creating a backup for support purposes, often history tables and system status tables aren't relevant. These table can dramatically increase the size of your .bacpac file and can be removed. Strip the history tables with the query below.

1. Ensure you are working from the Copy, select Query editor (preview) from the menu, and Login to the DB with the appropriate admin credentials.

Note that if you do not have these credentials stored or readily available, you can locate the values in the WebService config back at your Resources screen:

Within the WebService, select Settings>Environment variables from the menu. Under the App settings, scroll to the LemonEdge_Connection_Settings_ConnectionString value and click Show Value to view (credentials) values.

2. Once logged in, run two new Queries as described below:

Query 1: SQL Wrapper removal query:

Declare @sql NVARCHAR(MAX) = N'';

    SELECT @sql = @sql + N' DROP FUNCTION ' 

                    + QUOTENAME(SCHEMA_NAME(schema_id)) 

                    + N'.' + QUOTENAME(name)

    FROM sys.objects

    WHERE type in ('IF','TF') and name like 'sw1_%'

 

    Exec sp_executesql @sql

    --SELECT @sql

    GO

Query 2: Clear History tables:

BEGIN TRY

    TRUNCATE TABLE LT_AddIns_History

    TRUNCATE TABLE LT_CapitalAccountDataItems_History

    TRUNCATE TABLE LT_CompiledPaths_History

    TRUNCATE TABLE LT_GLPostingAllocations_History

    TRUNCATE TABLE LT_GLPostings_History

    TRUNCATE TABLE LT_ServerTasks_History

    TRUNCATE TABLE LT_ServerTaskData

    TRUNCATE TABLE LT_ServerTaskData_History

    TRUNCATE TABLE LT_ServerTaskStatuses

    TRUNCATE TABLE LT_ServerTaskStatuses_History

    TRUNCATE TABLE LT_SQLTypeWrappers_History

    TRUNCATE TABLE LT_SQLWrappers_History

    TRUNCATE TABLE LT_Transactions_History

END TRY

BEGIN CATCH

    PRINT 'An error occurred: ' + ERROR_MESSAGE();

END CATCH

You may receive a message that 0 rows were affected but this is normal, close out of both queries and click Overview from the Menu, clicking OK to any "unsaved edits" message.

Export the DB

From the DB Overview screen, click Export:

Select storage and please note the container for the next step(s). Enter the DB credentials and click OK.

You will receive confirmation of the export commencing but not necessarily confirmation of completion. You will typically have to wait 10-15 minutes and then confirm (.bacpac) file in the Storage container selected above.

Share via SAS token

To view and share the exported .bacpac file, navigate back to your Resources screen and select your Storage account:

From the Storage Account, select Data storage>Containers and any individual container you may have exported to in the previous step:

Select the export from the list and click Generate SAS from the pop-up screen:

Define your variable or keep the defaults, then click the Generate SAS token and URL button. Forward the Blob SAS Token and Blob SAS URL to your LemonEdge representative or attach to Support ticket if applicable.

Delete or store DB copy

Once your LemonEdge team confirms receipt of DB copy, you may Delete it or keep it archived as needed.