Skip to main content

Working with MSSQL

Hands-On Lab

 

Photo of Michael McClaren

Michael McClaren

Linux Training Architect I in Content

Length

01:00:00

Difficulty

Intermediate

In this hands-on lab, we have been tasked with installing and configuring an MSSQL Express server on CentOS 7. Once this is done, we will set up the server instance for use by the development teams.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Working with MSSQL

Introduction

In this hands-on lab, we have been tasked with installing and configuring an MSSQL Express server on CentOS 7. Once this is done, we will set up the server instance for use by the development teams.

Solution

  1. Begin by logging in to the lab server using the credentials provided on the hands-on lab page:

    ssh cloud_user@PUBLIC_IP_ADDRESS

Install MSSQL and Necessary Tools

  1. Add the repo for the mssql-server and install:

    sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
    sudo yum install -y mssql-server
  2. Run the configuration and select the Express edition:

    sudo /opt/mssql/bin/mssql-conf setup
    • Enter your edition(1-8): 3
    • Do you accept the license terms?: Yes
    • Enter the SQL Server system administrator password: Omgpassword!
    • Confirm the SQL Server system administrator password: Omgpassword!
  3. Add the repo for the mssql-tools and install:

    sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
    sudo yum install -y mssql-tools unixODBC-devel
    • Enter YES when prompted to accept the license terms.
  4. Export the tools path:

    export PATH=$PATH:/opt/mssql-tools/bin

Restore and Validate

  1. Download the backup:

    wget https://github.com/Microsoft/sql-server-samples/releases/download/adventureworks/AdventureWorksDW2017.bak
  2. Copy it to /var/opt/mssql:

    sudo cp AdventureWorksDW2017.bak /var/opt/mssql
  3. Restore the database:

    Create a restore.sql file:

    vim restore.sql

    Paste in the following contents:

    restore database AdventureWorksDW2017
    from disk = '/var/opt/mssql/AdventureWorksDW2017.bak'
    with move 'AdventureworksDW2017' to '/var/opt/mssql/data/AdventureWorksDW2017.mdf',
    move 'AdventureworksDW2017_log' to '/var/opt/mssql/data/AdventureWorksDW2017.ldf'
    GO

    Save and close the file.

    Execute the restore:

    sqlcmd -S 127.0.0.1 -U sa -P 'Omgpassword!' -i restore.sql
  4. Validate the restore was successful:

    Connect to the database:

    sqlcmd -S 127.0.0.1 -U sa -P 'Omgpassword!'

    Show databases:

    select name from sys.databases
    go

    Verify that "AdventureWorksDW2017" is listed.

  5. Validate that "Kevin Perry" is in Contacts:

    use AdventureWorksDW2017
    go
    select FirstName,LastName from DimCustomer where FirstName='Kevin' AND LastName='Perry'
    go

Conclusion

Congratulations — you've completed this hands-on lab!