How to create MSSQL Database Server ?

by | Sep 8, 2021 | Blog | 0 comments

MSSQL Database Server


Microsoft SQL Server is a relational DB management system, developed by Microsoft. As a database server, its primary function is to store and retrieve data upon binded apps’ requests, regardless of the programming language they are written in. Microsoft SQL Server together with the embedded Server Management Studio combines the benefits of broad functionality, feature completeness and inclusion of many useful extra options like analytics, performance monitoring, etc.

By default, GPUonCLOUD distributes the Express edition of this server, which has some small limitations, but is completely free of charge (the comparison of editions’ features can be seen here). Still, the exact version depends on the chosen hosting provider and may differ or being provided for a fee.

In order to get started with your Microsoft SQL Database Server at GPUonCLOUD, refer to the corresponding sections below with information on how to:

  • get a MSSQL Server at GPUonCLOUD Cloud

  • manage your MSSQL Server

MSSQL 2012 at GPUonCLOUD

  1. Click the New environment button at the top-left dashboard corner and switch to the required language tab in the opened Environment Wizard dialog frame.

Then, choose the MSSQL 2012 database inside the SQL database section (circled above) and the rest of the needed nodes, set the resources limits for them, type the name for your environment (e.g. mssql) and click on Create.

2. Wait a few minutes until your environment appears at the dashboard.

The created DB server and databases inside can be easily managed by means of the built-in Microsoft SQL Server Management Studio, available at your node’s remote desktop. In order to access it, you need to connect to your DB node via RDP.


This can be done in one of the following ways:

  • through the Guacamole HTML5 client (using either the one-time link from the received email or the appropriate dashboard option, shown in the image above)

  • through the local remote desktop client (using the RDP access credentials you’ve received in the email after environment creation)

To get the detailed instructions on these operations, visit the Remote Desktop Access page in our documentation.

3. In order to connect your .NET application with this DB server and enable it to work with the data inside, you need to use the connection link of the following format:

  • for standard security

Server=mssql{node_id}-{your-environment-name}.{hoster_domain};Database={db_name};User Id=sa;Password={password};

  • for trusted connection

Server=mssql{node_id}-{your-environment-name}.{hoster_domain};Database={db_name};Trusted_Connection=True;

  • for connection to a SQL server instance

Server=mssql{node_id}-{your-environment-name}.{hoster_domain}\{instance_name};Database={db_name};User Id=sa;Password={password};

The strings in curly brackets should be substituted with the following data:

  • {node_id} – ID of the required MSSQL node, which can be seen at the dashboard (e.g. for our environment above it stays for 13408)

  • {your-environment-name} – obviously, name of your environment (NOT an alias)

  • {hoster_domain} – platform domain of the chosen hosting provider (see the last column here)

  • {db_name} – name of a particular database (you can create a new one beforehand)

  • {password} – the one you’ve received via email (for admin access)

  • {instance_name} – the default value for MSSQL Express edition is MSSQLSERVER

MSSQL 2012 at GPUonCLOUD

Once you’ve connected to the required node’s remote desktop, run the SQL Server Management Studio tool, using the corresponding shortcut inside the folder at your desktop:


In a few seconds you’ll see this application opened with an authentication frame in front of it. You can access your server’s settings with the already stated credentials for the Windows Authentication method, though it won’t grant you enough permissions for database(s) editing.

Thus, switch the Authentication method to the SQL Server Authentication one and fill in the activated Login and Password fields with the admin access credentials you’ve received via email previously. Proceed with the Connect button.

Below we’ll show you how to perform some basic DB operations, like:

  • adding a new database

  • creating a new DB user

If you’d like to know more about the available MSSQL 2012 possibilities, refer to its official documentation.

Add Database

1. After the connection is established, the Object Explorer pane at the left will be filled with your server’s data.

                                                                                                                                             

Let’s add a new database: right click on the Databases folder and select the New database option from the context menu.

2. In the appeared New Database frame, type the desired name for it (e.g.GPUonCLOUDdb) and edit any other available parameters you may need (owner, initial size for DB files, etc).

Hit the OK button when ready.

3. Now, if you need a specific table for you data to be stored in, you can navigate to the Databases >{name_of_your_db} section inside the Object Explorer pane, right-click on the Tables item and select New Table.


                                                              

In the opened table section, specify the needed Column Names and set Data Types for them.

                                  

Also, here you can type the required Name for your table in the properties section at the right (mytable in our case) and adjust any other required settings. Once done, Save the changes with the Ctrl + S shortcut or with the corresponding icon at the top tools’ panel.

Add User

1. In order to control the accessibility of your data, stored within a particular DB server, you may create a separate server’s user with the limited permissions/management possibilities for your apps or other developers, if needed.

         

 

This can be done through expanding the Security section at the left-hand Object Explorer frame and choosing the New Login option at the Login folder’s context menu.

2. In the opened window, you need to fill in the fields with the new authentication data and adjust the necessary settings.

The main required configurations are:

  • specify Login name (e.g.GPUonCLOUD)

  • choose the SQL Server authentication option

  • set and confirm Password

  • uncheck – the User must change password at next login line

  • select the Default database from the drop-down list (in our case – the previously createdGPUonCLOUDdbone)

3. Beside that, you can make configurations at other pages (switch between them using the list at the left). An important tab is User Mapping, intended to define which databases the newly created DB user will have the access to.

At least, such a permission should be ticked for the DB, which was set as default for the current user (GPUonCLOUDdb in our case). Select OK to confirm the creation.

Great! As a result, you’ve got a new DB account with a separate database, allocated for it to work with and simultaneously denying access to other ones.

 

Submit a Comment