How To Access A User Work Database Or Datamart From Your Enclave Project Workspace VDI

This document outlines the requirements and instructions for connecting to a User Work Database or User Work Datamart from your Enclave project workspace virtual desktop infrastructure (VDI). 

Introduction

A User Work Database is an empty database for research. A User Work Datamart contains a patient cohort pulled from the Patient Data Science Repository (PDSR). Enclave project staff members are granted full permissions to either the database or datamart and both are accessed from your Enclave project workspace VDI.

Requirements

Instructions

1. Accessing using Microsoft SQL Server Management Studio (SSMS)

1. From your Enclave project workspace VDI, click on the Microsoft SSMS SQL icon. Please be patient, SSMS can take up to 20-30 seconds to launch.
2. When the SSMS application opens, you will be prompted to connect to the SQL Server. The following entries should be used:
   
   • For Server name, type or copy & paste the following: mgb-risc-userdbs-prod-e2-sqlmi.2653b5361395.database.windows.net
   • From the Authentication dropdown, select: Azure Active Directory – Universal with MFA
   • For the User name field, type your full primary Mass General Brigham email address

! Make sure you type in your primary MGB email address in full

3. Once all the entries have been filled, click on the Connect button. In the next window, expand the Databases folder.

4. All User Work Databases and Datamarts are listed using the same naming convention. The unique Project ID followed by _Userwork. (Example: PROJ1234_Userwork) Click on your User Work Database or Datamart.

2. Upload data from a file using Bulk Copy Program (bcp)

The bcp utility can be used to import large numbers of new rows into a User Work Datamart and/or Database table. To import data into a table, you must use a format file created for that table. Below provides basic instructions on how to import:

1. Create a .csv file with column headers and data matching the table columns you've created in your User Work Datamart or Database.
2. Within your Enclave project workspace, open a cmd prompt: Open the "Start" menu and type "cmd". Click "Command Prompt".
3. Type  cd C:\Program Files\Microsoft SQL Server\160\DAC\bin\
4. Syntax for bcp upload command. You may exclude -F 2 if your file does not include headers.

bcp [database_name.] schema.{table_name} in {csv file location} -c -G -U -t -S tcp:mgb-risc-userdbs-prod-e2-sqlmi.2653b5361395.database.windows.net -F 2

Example: bcp proj1234.dbo.bulk_insert_test in C:\temp\bulk_insert_test.csv -c -G -U -t -S tcp:mgb-risc-userdbs-prod-e2-sqlmi.2653b5361395.database.windows.net -F 2

For more information, see Microsoft bcp utility documentation.

3. Accessing using other Enclave tools

The same as the Complete PDSR Curated Data Set, all User Work Databases and Datamarts reside on an Azure SQL Server. Multiple Enclave knowledge articles exists detailing using various tools to access the Complete PDSR Curated Data Set. These same articles can be used to connect to your User Work Database or Datamart. Remember to replace all Server article references to mgb-risc-userdbs-prod-e2-sqlmi.2653b5361395.database.windows.net when connecting.

• KB0040282 How to Test Connections to the Complete PDSR Curated Data Set Using Python
• KB0040356 How to Test Connections to the Complete PDSR Curated Data Set Using R - Enclave Linux VDI
• KB0040279 Connect to the Complete PDSR Curated Data Set Using Azure Data Studio in the Enclave Linux VDI

Additional Resources

Introduction to SQL | Codefinity

Microsoft Educational SQL Resources | Microsoft Doc 

Microsoft bcp utility documentation | Microsoft Doc