Changes Using a Migration-Based Methodology
In this lab we will make changes to a database using a Migration-Based Methodology
DevOps MPP Course Source
- This lab is used in course DevOps200.6x: DevOps for Databses - Module 2.
Prerequisites:
- Completion of Module 1 labs
Lab Tasks:
In this lab, we will begin making a variety of changes to our database and committing them to version control.
- Add a new stored procedure.
- Alter a table
- Seed data into a table
- Alter a stored procedure
Estimated Lab Time:
- approx. 30 minutes
Task 1: Add a new stored procedure
We will examine the process for adding a new stored procedure through code.
-
Open your ReadyRoll project WideWorldImporters-RR in Visual Studio. There should be one migration script from the previous lab. (The Recent Projects and Solutions option from the File menu might help with opening this project).
-
Using SQL Server Object Explorer, expand your SQL Server instance (likely to be (localdb)\ProjectsV13), then expand the WideWorldImporters-RR database. Expand the Programmability folder, right-click the Stored Procedures folder, then click Add New Stored Procedure.
-
This will open the new Stored Procedure window. A template stored procedure is shown.
-
Replace all that code with this code, then click Update in the upper left.
CREATE PROCEDURE dbo.GetOpenPurchaseOrderCount AS BEGIN SELECT COUNT(*) AS [Open Orders] FROM Purchasing.PurchaseOrders WHERE IsOrderFinalized = 0; END;
-
This will open the Preview Database Updates dialog, with a few options at the bottom. The Generate Script option would create a new migration script without deploying it to the database. The Update Database button will update the database, but not generate a script. For this change, click Update Database.
-
Close the dbo.Procedure window.
-
If the ReadyRoll pane isn’t pinned and visible, hover over it, and click the Pin to make it stay visible.
-
In the ReadyRoll window, click Refresh. You should see change that shows a pending import for the new stored procedure.
-
Click Import and generate script. This will generate a new script, named 002_xxx.sql. This will be added in the Migrations folder of your project and also opened in the main editing space.
-
Click to open the Team Explorer toolbar, then click Changes. You should see the new migration script and a project change listed. Enter a commit message, and click Commit All and Push.
This sends all our changes to the VSTS repository that we created in a previous lab. You can check this on your own to verify that the changes were sent.
Task 2: Alter a table
We will alter an existing table with the designer.
Let us change one of the existing tables in this database. We want to capture the original price of an order line. We want to link this change to a work item.
-
In a browser, open your VSTS project WideWorldImporters-RR and select the Work tab. Click New Item. Enter this text in the User Story edit box: Capture original PurchaseOrder line item price.
After this is complete, you will have a new work item. We will use this when we make a code change.
Double-click your new work item and note the number in the upper left shown as a USER STORY number. This is the work item ID that we will use in our commit.
-
Return to your Visual Studio project, navigate to your SQL Server instance and WideWorldImporters-RR database. Expand the Tables folder, right-click the Purchasing.PurchaseOrderLines table and click View Designer.
-
When the designer opens, add an OriginalPrice column to the end of the list with a decimal (18,2) data type. Allow nulls. Click Update.
-
In the Preview Database Updates window, click Generate Script. This will create a new migration script that contains the ALTER TABLE code.
-
We want to add one additional set of code to this script. We want a trigger to populate our data. At the bottom of the script, add this code:
PRINT N'Creating Trigger for Purchasing.PurchaseOrderLines'; GO CREATE TRIGGER PurchasingOrderLine_CaptureOriginalPrice ON Purchasing.PurchaseOrderLines AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE pol SET pol.OriginalPrice = d.ExpectedUnitPricePerOuter FROM Purchasing.PurchaseOrderLines AS pol INNER JOIN inserted AS i ON i.PurchaseOrderLineID = pol.PurchaseOrderLineID INNER JOIN deleted AS d ON d.PurchaseOrderLineID = pol.PurchaseOrderLineID; END;
Our script should look like this:
-
Our script is pending deployment as the code has not been executed against our development database. A ReadyRoll toolbar will have appeared at the top of the script. Click Deploy Project.
The build-and-deploy should take place with all logging available in the Output window. Refreshing the ReadyRoll window should show all objects identical.
-
In the Team Explorer pane, in the Changes section, we should have the third migration script listed. Enter this comment and press Commit All and Push.
“Added new column and trigger to capture the original purchaseOrderLineitem price #x” and replace X with the number of your work item.
-
In a browser, open your VSTS project. Go to the Work tab and double-click your work item. You should see your commit and comment linked in the right side under the Development section.
Task 3: Seed data into a table
We will seed data into the People table
In this lab, we will learn how to add data to a table during deployment. Our development database already has data in it, but if we were to deploy these three migration scripts to a new database, there would be no data. To do that, we will add a script that inserts data.
-
In Solution Explorer, right-click the Migrations folder, click Add, then click Script.
-
A new, blank script is created. Under the batch separator GO, add this code:
IF ( SELECT COUNT(*) FROM Application.People ) = 0 BEGIN INSERT Application.People ( PersonID , FullName , PreferredName , IsPermittedToLogon , LogonName , IsExternalLogonProvider , HashedPassword , IsSystemUser , IsEmployee , IsSalesperson , UserPreferences , PhoneNumber , FaxNumber , EmailAddress , Photo , CustomFields , LastEditedBy ) VALUES ( 1, N'Data Conversion Only' , N'Data Conversion Only' , 0 , N'NO LOGON' , 0 , NULL , 0 , 0 , 0 , N'{"theme":"blitzer","dateFormat":"yy-mm-dd","timeZone": "PST","table":{"pagingType":"full_numbers","pageLength": 25},"favoritesOnDashboard":true}' , N'' , N'' , N'' , NULL , NULL , 1 ); END;
-
Click the Mark as Deployed button. You do not need to deploy this script to your local development database.
-
Save and commit the script to your VCS the same way you did earlier in the lab.
Task 4: Alter a stored procedure
In this last lab, we will make a change in Management Studio and sync that change to our project. We will alter the procedure we created in the first part of this lab.
-
Open SQL Server Management Studio and connect to your WideWorldImporters-RR development database.
-
Enter this code in the window:
ALTER SCHEMA Website TRANSFER dbo.GetOpenPurchaseOrderCount;
-
Execute this code. This will move the stored procedure to a new schema.
-
In Visual Studio, click the Refresh button in the ReadyRoll pane. You should see a drop and add as new changes.
-
Click Import and Generate Script. You should get a new migration script such as this.
-
Replace the code in the migration script with
-
Save and commit your change.
We could have left the generated code, which would work in this case, but dropping and added objects can reset permissions, so using code that preserves objects is recommended when possible.
Summary
In this lab you completed the following tasks:
- Added a new stored procedure.
- Altered a table
- Seeded data into a table
- Altered a stored procedure