Learnings Detail

Creating a Sandbox

If using an Azure IIS VM & Azure SQL:

• Best practice is to make a copy of the Azure production DB, then export from that copy (as otherwise there will be errors)
• If your public website runs on Rock, the export is probably the only realistic option to get a copying
• If using Azure BLOB storage as the location, IO performance will be slower vs copying directly to a location

Process to follow

1. Open SQL Server Management Studio
2. Connect to Azure SQL production instance
3. Export Data Tier Application (BACPAC), saving to Azure BLOB storage (or local disk). Take note of the location
4. RDP into Production IIS VM
5. Compress/zip c:\inetpub\wwwroot and copy to a temp location of Azure BLOB storage
6. Log into Sandbox server with AAD Server Admin (or a local admin account)
7. Copy bacpac file to where you will be running SSMS (or leave in an Azure BLOB storage location)
8. Open SSMS and log into the Sandbox server with SQL Server Authentication and the 'sa' account
9. Copy zip file to sandbox server
10. Compress/zip existing c:\inetpub\wwwroot and save in a known location (make a backup of existing wwwroot)
11. While using SSMS connected to Sandbox server w/ 'sa' account, restore bacpac file (import data-tier application), making note of the restored database name (like "RockDB_Sandbox_06-08-19_2352")
12. Run Calvary-Tool_PostDeploySandboxTestDevDatabase.sql script in SSMS with the Sandbox server and the new Sandbox DB as the connected DB
13. Using IIS Manager, stop IIS Server (alternatively stop the site, but stopping the server is more authoritative)
14. Remove existing c:\inetpub\wwwroot files and folders except web.ConnectionStrings.config file
15. Expand new c:\inetpub\wwwroot
16. Edit DB connection string c:\inetpub\wwwroot\web.ConnectionStrings to use the proper Catalog value (the new database name)
17. Make sure that URL rewrite or any other add-ons are installed on the sandbox server just as the production server had, INCLUDING SNI for SSL
18. Restart SQL Server
19. Restart IIS
20. Log in to the sandbox/test/dev Rock instance. You may need to go to ~/admin?logout=true depending on cookies and cache state. This can be done from an external system or from the sandbox/test/dev server (even using http://localhost)
21. Enable the appropriate Jobs
• SQL that can be used for 'low risk' jobs:
• UPDATE [ServiceJob] SET [IsActive] = 'True' WHERE [Name] IN ('Job Pulse', 'Calculate Metrics', 'Group Sync', 'Spark Link', 'Calculate Person Duplicates', 'Rock Cleanup', 'Calculate Group Requirements', 'Check for New Plugins', 'Family Analytics')
• The above SQL will set active for Jobs: Job Pulse, Calculate Metrics, Group Sync, Spark Link, Calculate Person Duplicates, Rock Cleanup, Calculate Group Requirements, Check for New Plugins, Family Analytics
• Examine and enable as appropriate:
• Process Workflows
• Send Communications
22. Edit the appropriate Global Attributes:
    • Public Application Root: http://my.<sandboxname>.calvary.church/
    • Internal Application Root: http://sandbox.calvary.church/
23. In System Settings, set the Financial Gateway 'Network Merchants' to inactive; if needed, enable the test gateway
24. In System Settings, deactivate Protect My Ministry
25. Update Google Authentication Service with correct keys for sandbox (overwriting the restored keys from production)
26. Update Google API Key with correct key for sandbox (overwriting the restored key from production)
27. In CMS Configuration, edit the Sites to contain the URL sandbox.calvary.church
28. Use the ACME Certificates plugin to install an SSL certificate solely for sandbox.calvary.church
29. Enable and force SSL for internal and calvary church 2019 sites
30. Clear cache
31. Restart Rock
32. Disable appropriate auxiliary plugins/services:
    • Wistia
    • Planning Center Online
33. Enable appropriate Jobs
    • Update Persisted Dataviews
    • Database Maintenance
    • Renew Certificates
    • Data Automation
34. Test Google Auth with an incognito window
35. If needed, enable SMTP:
• Set SMTP component active
• Set 'Server' address to 'localhost'
• Leave 'Port' at '25', with username, password blank and SSL set to 'no'

Calvary Tool PostDeploy

-- IMPORTANT --
-- USE THE BELOW SQL TO TAKE A PREVIOUSLY RESTORED SQL DATABASE AND MAKE IT USABLE FOR SANDBOX, DEV, OR TEST USAGE --
-- IMPORTANT --

-- CREATE FUNCTION FOR REMOVING NON-ALPHA CHARACTERS
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ufnUtility_RemoveNonAlphaCharacters]') AND type = 'FN')
DROP FUNCTION [dbo].[ufnUtility_RemoveNonAlphaCharacters]
GO

CREATE FUNCTION [dbo].[ufnUtility_RemoveNonAlphaCharacters](@Temp VarChar(1000))
RETURNS VARCHAR(1000)

AS

BEGIN

    DECLARE @KeepValues as VARCHAR(50)
    SET @KeepValues = '%[^a-z]%'
    WHILE PatIndex(@KeepValues, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    RETURN @Temp
END
GO

-- Create 'RockDBA' so it points to the current server's Logins; we use RockDBA on the Sandbox server at Calvary --
CREATE USER [RockDBA] FOR LOGIN [RockDBA]
GO
ALTER ROLE [db_owner] ADD MEMBER [RockDBA]
GO

-- ADD 'TEST' BANNER --
UPDATE B SET 
        [PreHtml] = '<h4 style="position:absolute;left:80px;top:6px; color: black;">You are connected to the <strong>Test</strong> database updated 1-2-2018</h4><script>$(".navbar-static-top").css("background-color","hotpink");</script>', [ModifiedDateTime] = GETDATE()
FROM [Block] B
INNER JOIN [BlockType] T ON T.[Id] = B.[BlockTypeId]
WHERE T.[Path] = '~/Blocks/Core/SmartSearch.ascx'
AND B.[Zone] = 'Header'

-- TURN OFF SSL FOR ALL PAGES --
UPDATE [Page] SET [RequiresEncryption] = 0

-- TURN OFF SSL FOR ALL SITES --
UPDATE [Site] SET [RequiresEncryption] = 0

-- INACTIVATE JOBS --
UPDATE [ServiceJob] SET [IsActive] = 0

-- BLANK OUT AND CHANGE TO @SAFETY.NETZ ADDRESSES ALL NON-SYSTEM, NON-CALVARY, NOT BLANK, NOT NULL EMAIL ADDRESSES --
BEGIN
UPDATE [Person]
set [Email] = LOWER(dbo.[ufnUtility_RemoveNonAlphaCharacters]([NickName])) + LOWER(dbo.[ufnUtility_RemoveNonAlphaCharacters]([LastName])) + '@safety.netz'
WHERE [Email] IS NOT NULL AND [Email] NOT LIKE '%@calvaryonline.cc' and [Email] != '' AND IsSystem != 1
END

-- DEACTIVATE ALL MAIL TRANSPORTS --
BEGIN
UPDATE [AttributeValue] SET [Value] = 'False' 
WHERE AttributeId IN 
    (SELECT a.id 
    FROM [EntityType] et 
    INNER JOIN [Attribute] a 
    ON a.EntityTypeId = et.Id AND a.[Key] = 'Active' 
    WHERE et.name LIKE '%Communication.Transport%')
END

-- UPDATE THE MAIL MEDIUM/TRANSPORT SETTINGS TO USE SMTP WITH LOCALHOST/25 --
DECLARE @SMTPEntityTypeId int = ( SELECT TOP 1 [Id] FROM [EntityType] WHERE [Name] = 'Rock.Communication.Transport.SMTP' )
DECLARE @MailEntityTypeId int = ( SELECT TOP 1 [Id] FROM [EntityType] WHERE [Name] = 'Rock.Communication.Medium.Email' )
DECLARE @MailAttributeId int

-- SMTP SERVER --
SET @MailAttributeId = ( SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'Server' )
UPDATE [AttributeValue] SET [Value] = 'localhost' WHERE [AttributeId] = @MailAttributeId

-- SMTP Port --
SET @MailAttributeId = ( SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'Port' )
UPDATE [AttributeValue] SET [Value] = '25' WHERE [AttributeId] = @MailAttributeId

-- SMTP USERNAME --
SET @MailAttributeId = ( SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'UserName' )
UPDATE [AttributeValue] SET [Value] = '' WHERE [AttributeId] = @MailAttributeId

-- SMTP PASSWORD --
SET @MailAttributeId = ( SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'Password' )
UPDATE [AttributeValue] SET [Value] = '' WHERE [AttributeId] = @MailAttributeId

-- SMTP USESSL --
SET @MailAttributeId = ( SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @SMTPEntityTypeId AND [Key] = 'UseSSL' )
UPDATE [AttributeValue] SET [Value] = 'False' WHERE [AttributeId] = @MailAttributeId

-- MAIL TRANSPORT --
DECLARE @SMTPEntityTypeGuid varchar(50) = ( SELECT LOWER(CAST([Guid] as varchar(50))) FROM [EntityType] WHERE [Id] = @SMTPEntityTypeId )
SET @MailAttributeId = ( SELECT TOP 1 [Id] FROM [Attribute] WHERE [EntityTypeId] = @MailEntityTypeId AND [Key] = 'TransportContainer' )
UPDATE [AttributeValue] SET [Value] = @SMTPEntityTypeGuid WHERE [AttributeId] = @MailAttributeId