In this lab we will make changes to a database using a Migration-Based Methodology

DevOps MPP Course Source

Prerequisites:

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.

  1. 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).

    In Solution Explorer, WideWorldImporters-RR is expanded, Migrations is expanded, and 0001_20170607-1055_Steve.sql is selected.

  2. 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.

    In Object Explorer, the following path is expanded: SQL Server(localdb)ProjectsV13 (SQL Server 13.0.44…)\Databases\WideWorldImporters-RR\Programmability. In the Programmability folder, the Stored Procedures folder is selected, and on its right-click menu, Add New Stored Procedure is selected.

  3. This will open the new Stored Procedure window. A template stored procedure is shown.

    On the dbo.procedure.sql tab, template code displays.

  4. 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;
    
  5. 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.

    On the Preview Database Updates page, three buttons display: Generate Script, Update Database, and Cancel. In the center, Highlights are none, User actions Create, and Supporting actions are None.

  6. Close the dbo.Procedure window.

  7. If the ReadyRoll pane isn’t pinned and visible, hover over it, and click the Pin to make it stay visible.

    An Auto Hide callout points to the pin icon.

  8. In the ReadyRoll window, click Refresh. You should see change that shows a pending import for the new stored procedure.

    In the ReadyRoll Core Edition pane, a message displays saying that changes are pending import.

  9. 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.

    In Solution Explorer, under WideWorldImporters-RR\Migrations, a new .SQL file displays.

  10. 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.

    In Team Explorer – Changes, the commit message reads “New stored procedure to track the open orders for the website status.” The Commit All drop-down menu is expanded, and Commit All and Push is selected.

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.

  1. 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.

    Under Stories, Board is selected. Under New, a New item icon displays.

    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.

  2. 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.

    In the Tables folder, Purchasing.PurchaseOrderLines is selected. From its right-click menu, View Designer is selected.

  3. 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.

    On the Purchasing.PurchaseOrderLines Design tab, the script file Purchasing.PurchaseOrderLines.sql is selected. In the designer table below, OriginalPrice is selected.

  4. In the Preview Database Updates window, click Generate Script. This will create a new migration script that contains the ALTER TABLE code.

    A newly generated migration script with ALTER TABLE displays.

  5. 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:

    A migration script displays. At this time, we are unable to capture all of the information in the window. Future versions of this course should address this.

  6. 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.

    A callout points to the Deploy Project button.

    The build-and-deploy should take place with all logging available in the Output window. Refreshing the ReadyRoll window should show all objects identical.

  7. 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.

  8. 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.

    The Development section displays with the commit and comment displaying.

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.

  1. In Solution Explorer, right-click the Migrations folder, click Add, then click Script.

    In Solution Explorer, the Migrations folder is selected. From its right-click menu, Add / Script is selected.

  2. 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;
    
  3. Click the Mark as Deployed button. You do not need to deploy this script to your local development database.

  4. 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.

  1. Open SQL Server Management Studio and connect to your WideWorldImporters-RR development database.

  2. Enter this code in the window:

     ALTER SCHEMA Website
     TRANSFER dbo.GetOpenPurchaseOrderCount;
    
  3. Execute this code. This will move the stored procedure to a new schema.

  4. In Visual Studio, click the Refresh button in the ReadyRoll pane. You should see a drop and add as new changes.

    In the ReadyRoll pane, two StoredProcedure object types display. Under Action, the first one is Add to Project, and the second one is Delete from Project.

  5. Click Import and Generate Script. You should get a new migration script such as this.

    The Migration script displays a drop and add.

  6. Replace the code in the migration script with

    Migration script code displays. The PRINT line displays with red code, N’Moving (dbo).(GetOpenPurchaseOrderCount) to the website schema.

  7. 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