Accessing Your TeamDynamix Private Cloud Database

Overview

Please note that this article only pertains to private cloud customers! 

After requesting customer read-only credentials, there may be some ambiguity on next steps. When does TDX rotate passwords? What programs are supported? How do we connect? Once we’ve confirmed access, what do we do next? Are there any database diagrams?  This article is designed to help with those next steps.

An Important Note

Before we begin, this account is not meant for data loading and is not supported if used that way. It can cause serious performance degradation to the point of making it hard to work with the application or even making parts of it unusable. We frequently see performance complaints when customers try to do this. If that is something your organization is interested in, we do offer nightly backups that can be restored to a Microsoft SQL Server managed by your organization. That service can be requested here.

Supported Programs

TeamDynamix will aid in the initial connectivity to the database. These are programs which have been known to work connecting to our backend databases, but functionality varies based on language and we will not necessarily be able to assist in all troubleshooting efforts.

  • Microsoft SQL Server Management Studio (SSMS) – One of the standards for working directly with SQL Server. This option gives the user a lot of flexibility and control over query design and data access but does require some T-SQL coding knowledge.
  • Azure Data Studio – A newer SQL GUI made by Microsoft which may be a little easier for beginners to work with. Although similar to SSMS, it has some advantages like support for non-Windows systems and a dark mode theme.
  • Microsoft Power BI Desktop – A powerful data reporting tool which can be a bit easier to work with than SSMS. However, the tradeoff for being more user-friendly is that you may have less access to describe to the SQL engine how you want it to do something. It’s important to review your settings as Power BI can sometimes pull all data locally before doing any filtering. It is also important to ensure you add proper filtering to your reports.  
  • Microsoft Powershell – A command-line tool pre-installed on any Windows system which has an installable SQLServer Powershell Module which can connect to and query external SQL Servers.
  • Quest Toad Data Point – This is a third-party data reporting tool similar to Power BI managed by an industry leader. Although we have little experience working with or troubleshooting this product, we have seen customers able to connect with it successfully.
  • Tableau – As with Toad, this is third-party business intelligence software for data analysis and visualization.
  • Python – A dynamically-typed, high-level programming language commonly used with modern applications and integrated into Microsoft SQL products. As this is most commonly used in apps or data science, we suggest caution when using Python to connect to your server as unexpected additional high call volume may lead to degraded performance.

Programs Not Supported

At this time, the following list of programs are not supported for use with TeamDynamix software.

  • Microsoft Power BI on Azure –  Unfortunately, Azure uses a large IP range for products like the Cloud version of Power BI (e.g. publishing reports to share via app.powerbi.com). Opening the range necessary to allow use of this software would constitute an unacceptable security risk both for the individual client and others.
  • Microsoft Azure Data Factory and Linked Services - As with the cloud version of Power BI, this tool has various security concerns which prevent us from enabling it as there is no way to secure the system to our standards given what needs to be altered for it to work. Additionally, its purpose as a tool is primarily to pull data or run heavier reporting which is not the intended use of the login. 
  • Linked Servers – Although this technology is supported by Azure, creating linked servers between systems in the same network is already a performance concern and shouldn't be used unless all associated databases on both sides are isolated and not responsible for running an application. Running syncs or reporting using linked servers can be even more problematic when different networks, companies, production systems, and Azure are involved. Since there is no isolated, non-production environment for clients, it is not something we currently support due to quality of service, security, maintainability concerns. 
  • Data Replication (like Qlik) – Programs like this are meant for very specific use cases and best suited for internal-to-internal communication where the administrators have full access to both sides of the system. Although we have seen customers try to connect this way, it can have severe performance impacts, is not recommended, and will be neither installed nor maintained by TeamDynamix.

If use of these or similar programs is a requirement for your organization, we suggest signing up for access to nightly backups. Private Cloud customers already have this built into their contracts and can request access at no additional cost through their Implementation Consultant or Customer Success. This will be set up as a nightly job to export your database to a BACPAC saved in Azure Cloud storage. Next, you will be given additional SAS credentials where you can pull it down to your local network to be imported to a local Microsoft SQL Server of version 2016 or higher. Once restored, you can use it internally for ETL, reporting, and quick access to relatively up-to-date data while your institution controls the permissions and applications used.  

Connecting

Setup, IPs, and Connection Strings

When you open a ticket for your read-only credentials, TeamDynamix will ask you for a list of user IP addresses which will connect to the system. You can also submit tickets to request we add or remove IPs at any time. We will then need to change the server so it is publicly accessible, open port 3342, and add the requested IP addresses to the Allow List before you’ll be able to connect.

Note: At this time, we do not support dynamic IPs. If you are unsure of your public-facing IP, your network administrator should be able to provide the IP or a list of IPs, or you can simply Google “what is my ip”.

Once the ticket is completed, you will receive a read-only account of the format [institution short name]_customer_user_ro_[calendar year]. For example, if TeamDynamix had an account, it might look like teamdyn_customer_user_ro_2023. The provided login name includes the year as a security measure because we rotate all accounts’ credentials for SOC II compliance towards the end of every year.

You will also be given a connection of the format tdx-[region]-prd-[customer].public.[hex string].database.windows.net,3342. Along with your credentials, you need the entire string here including the “,3342” at the end to get in. For example, a TeamDynamix server in Columbus, Ohio, might have a string similar to: tdx-eus-prd-teamdyn.public.5e4a1c4bf749.database.windows.net,3342.

Although the server does have "prd" in the name, it is the only server you have access to. The Sandbox and Release Preview databases are stored here, as well. You do not change the connection string to access your other databases. 

Available Databases

During initial configuration, all Private Cloud customers get a unique [customer]-prd, [customer]-sb, and [customer]-rp database. These are the Production, Sandbox, and Release Preview environments, respectively. More information about these environments and how often data is refreshed can be found here

If you want to know more about the tables in our system, you can read this summary article about our database tables and changes between releases. On that page, you’ll find a quick description of the core tables in the system as well as schema comparisons for the last several major versions. At this time, we do not provide a full description of every table or database diagrams to customers, though data discovery and schema tools may be able to assist you locally if you choose to get customer backups.

Best Practices

One of the most critical things to understand before you open a connection is that you will be querying your live Production system. The database with the most recent data is also the one driving your ticketing software. We do grant access to and monitor the performance of all servers, but it is up to individual clients to be cautious with whom they give their credentials to and how those permissions are used.

Things to watch out for:

  • Configuration settings in query-builders like Power BI Desktop.
  • Writing ad-hoc code which joins large tables to other large tables.
  • Queries without WHERE clauses over large rowcounts.
  • Aggregate sums over long periods of time.
  • Anything that may produce a high volume of calls.
  • Queries returning few rows but taking a very long time to run.
  • Pulling a lot of data across the network into your own system.

Certain programs will pull the entire query contents of all associated tables before doing any kind of filtering. Large aggregates may use up substantial portions of system resources shared by the live system, even when done against the Sandbox or Release Preview databases. TeamDynamix doesn’t discourage the use of these programs, we just wish to add context for how the connection can best be utilized.

It is also a good practice to minimize the impact of your queries by either setting the isolation level to read uncommitted or adding WITH(NOLOCK) after table names where applicable.

Note: For more advanced users, we recognize this does introduce the possibility of dirty and phantom reads, but they are rare and many of our tables don’t contain the type of data where this would have a significant impact in the overall results of customer reports.

Example Connection Platform Options

Connecting with SQL Server Management Studio

One you have downloaded SSMS and received your login credentials and connection string as noted above, open SSMS from the Start menu. You will be greeted by a program screen that looks similar to the screenshot below. In the upper left corner of the software, you should see an Object Explorer box (note: if you do not, go to the upper tool bar, View > Object Explorer or press F8). Click the Connect button and select Database Engine option.

Uploaded Image (Thumbnail)

This will open a Connection dialog box. In Server Name, put in the full server with ,3342 as mentioned earlier. Under the Authentication method, select SQL Server Authentication.

Uploaded Image (Thumbnail)

Next, click Connect.

Uploaded Image (Thumbnail)

In the Object Explorer, you should now see your server. Hit the + by Databases to expand the list. All our servers have an IndexMaintenance database for routine system work. You will not have access to this, and it can be ignored.

For now, let’s try selecting some data by clicking the + by Tables. You can see in the screenshot below that I’ve filtered the tables to include only those with ‘Tickets’ in the name. Next, right-click the table you want to see and choose “Select Top 1000 Rows”.

Uploaded Image (Thumbnail)

This should display data from your database that looks similar to the following:

Uploaded Image (Thumbnail)

From here, you should be able to start writing your own queries. If you’re a little new to SQL or need a refresher, you can trust the articles you find from the following sources:

Connecting with Power BI for Desktop

First, download and open Power BI. When you login through PowerBI, click the SQL Server button.

Uploaded Image (Thumbnail)

Fill in the connection to your Private Cloud server with your provided database name and click OK.

Uploaded Image (Thumbnail)

In the next dialog box, choose the “Database” authentication type. This is where you’ll put in the customer RO account name and password. Here, we’ll use the same example provided in the Setup section. This should connect fairly quickly. If you’ve followed the setup instructions in this KB and the connection appears to be spinning, you may need to reach out for support.

Uploaded Image (Thumbnail)

Once your credentials are accepted, you should now be connected to your server and the specific customer database you chose in the dialog. A Navigator box will open where you can search for and examine tables.

Uploaded Image (Thumbnail)

Next Steps

Most other programs connect in a way similar to either SSMS or Power BI.

  • If you are unable to connect or experiencing errors, check to make sure your current public-facing IP address has not changed and was added to TeamDynamix’s allow list.
  • As noted earlier in the article, if these options do not meet your organization’s needs or you have concerns about potential performance impacts of your reporting, we suggest creating a local SQL Server and requesting access to nightly backups.
  • If you are still unable to connect or have questions, you can open a support ticket and our database staff will be happy to assist you.

 

Print Article

Related Articles (1)

Standards, timeline, and instructions for how and when passwords will be rotated annually for customers' read-only database accounts.

Attachments (0)

No attachments found.