Database deployment instructions - PuzzleServer/mainpuzzleserver GitHub Wiki

Note: These instructions ONLY update the database. After completing these steps, you need to deploy the full site. Instructions for deploying the site can be found here.

Note: It's a good idea to deploy the database first since it's less likely to break the site than the other way around (due to the fact that we've almost entirely avoided breaking DB changes).

1. Make sure you are on the correct commit

This is a dangerous way to change things. Please double check that you have things set up correctly.

2. Copy the SQL connection string for the database

This can be found in the Azure Portal by going to the puzzlehunt App Service -> Configuration, scrolling down to the 'Connection strings' section, finding the row for the PuzzleServerSQLConectionString, and clicking the edit (pencil) icon at the end of the row. Copy the 'Value' field from the blade that opens. image image

3. Paste the SQL connection string into the appsettings.Development.json file

In the ServerCore project, open the appsettings.Development.json file and replace the value of the 'PuzzleServerContextLocal' setting with the connection string from the Azure App Service. image

4. Hit F5

WARNING - THIS IS THE STEP WHERE THE DATABASE GETS UPDATED

The site should run normally, but it will be using the production database and will update the production database with the local migrations. Be extremely careful about updating any data on the site - if at all possible, don't.

5. REVERT YOUR CHANGE

We super duper need to not check this change in and you definitely don't want it to stick around locally. As soon as the local site starts running & things aren't broken revert the change.

Congratulations - you've updated the database! :D Don't forget to deploy the website too!

Bonus: Troubleshooting

You may run into an error message similar to this one:

System.Data.SqlClient.SqlException: 'Cannot open server 'puzzlehunt' requested by the login. Client with IP address 'XXX.XXX.XXX.XXX' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.'

If that happens, you'll need to add your IP address to the firewall for the database. To do that, go to the Azure Portal and go to the puzzleserver SQL database. On the Overview click the 'Set server firewall' button. On the Firewall settings page that opens, fill in the top row of the table with a rule name and your IP address (which is shown in the error message - redacted in the one above), then click the 'Save' button at the top of the page. Wait for a while (the error message states that it may take up to five minutes for the change to take effect) and then try F5 again.

image image

temp raw notes - ignore these

PH App service, under config, grab PuzzleServerSQLConectionString, hit edit, copy out entire value Go to appsetting.Development.json, paste over local change, hit F5, then revert the local file DO NOT COMMIT THIS Note: DB first is less likely to break the site (since it's usually adding columns)

SQL - set server firewall command - firewall settings blade - fill in a rule & save - can be a range for microsoft IPs for 0-255 (for house use a specific one) - error message has specific IP address

System.Data.SqlClient.SqlException: 'Cannot open server 'puzzlehunt' requested by the login. Client with IP address '131.107.159.178' is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.'