How to Install ArangoDB on Ubuntu 20.04

Reading Time: 5 minutes
ArangoDB_logo

What is ArangoDB?

ArangoDB is a high availability and scalable multi-model NoSQL database that natively works with graphs and documents and includes a full-text search. It is a free, open-source software developed and maintained by ArangoDB GmbH. The system supports three different data models with one database core and includes a unified query language called AQL (ArangoDB Query Language). This query language is declarative and allows the combination of different data access patterns in a single query. The software can be implemented on multiple systems, including Windows, macOS, Linux, Kubernetes, Docker, and on our Managed Cloud. It has a strong community support base on GitHub, StackOverflow, Google Groups, and Slack. 

Continue reading “How to Install ArangoDB on Ubuntu 20.04”

Troubleshooting Microsoft SQL Server Error 18456

Reading Time: 4 minutes

Login errors with Microsoft SQL Server (MSSQL) are a fairly common issue and can be easily solved with some basic troubleshooting steps. Before we dig in, let’s take a look at the details of the error to try and determine the cause.

connect to server error
Continue reading “Troubleshooting Microsoft SQL Server Error 18456”

What is NoSQL and How is it Utilized?

Reading Time: 5 minutes

What is NoSQL?

A NoSQL or a NoSQL Database is a term used when referring to a “non SQL” or “not only SQL” database. NoSQL databases store data in a different format than a traditional relational database management systems. This is why NoSQL is often associated with the term “non-relational” database. Simply put, NoSQL databases are modern databases with high flexibility, blazing performance, and built for scalability. These databases are used when you require low latency and high extensibility while working with large data structures. The versatility of NoSQL is due to the nature of as being unrestricted in comparison to relational databases models such as MySQL or DB2. 

Continue reading “What is NoSQL and How is it Utilized?”

Choosing Your Cloud Sites Technology Setup

Reading Time: 2 minutes

Standing behind our Liquid Web Cloud Sites product, are server racks full of both powerful and stable Linux and Windows servers which power well over 100,000 sites and applications. Every Windows-based package is served from these clusters that are built and optimized especially for Windows. All Linux-based packages are also served from these same brawny server clusters created and specifically optimized for Linux. We use advanced load balancing technologies to automatically detect the type of technology you are running and route each request to the proper pool of servers.

Continue reading “Choosing Your Cloud Sites Technology Setup”

How to Install Microsoft SQL on Linux

Reading Time: 6 minutes

In this article, we will be discussing how to install Microsoft SQL or MSSQL on Linux. Microsoft SQL, colloquially referred to as MSSQL, is a relational database management system created by Microsoft. Open-source MySQL and PostgreSQL are typically synonymous with Linux distributions, but working with MSSQL on Linux is also supported. MSSQL offers some features that its open-source counterparts don’t, and depending on application requirements, it might be the right choice for an RDBMS. In this tutorial, we are going to walk through how to install MSSQL on CentOS 7 and Ubuntu 16.04.

Continue reading “How to Install Microsoft SQL on Linux”

Finding Resource Usage Details in MSSQL

Reading Time: 7 minutes

When running MSSQL or Microsoft SQL Server, we need to determine whether it is optimized or will it need more resources to achieve better performance. This article reviews what behaviors to look for,  where to find them, and how to view signs of distress.

Continue reading “Finding Resource Usage Details in MSSQL”

How to Setup and Use Microsoft SQL Server Management Studio

Reading Time: 4 minutes

What is SSMS?

SQL Server Management Studio (SSMS) is a free Windows application to configure, manage, and administer Microsoft SQL Server (MSSQL). SSMS includes an Object Explorer to view and interact with databases and other elements, a Query window to write and execute Transact-SQL queries, and script editors for developers and administrators. Continue reading “How to Setup and Use Microsoft SQL Server Management Studio”

SQL Databases Migration with Command Line

Reading Time: 3 minutesWhat if you have dozens of SQL databases and manually backing up/restoring each database is too time-consuming for your project? No problem! We can script out a method that will export and import all databases at once without needing manual intervention. For help with transferring SQL Logins and Stored Procedures & Views take a look at our MSSQL Migration with SSMS article. Continue reading “SQL Databases Migration with Command Line”

SQL Database Migration with SSMS

Reading Time: 9 minutesMigrating MSSQL between servers can be challenging without the proper guidelines to keep you on track. In this article, I will be outlining the various ways to migrate Microsoft SQL Server databases between servers or instances. Whether you need to move a single database,  many databases, logins or stored procedures and views we have you covered! There are many circumstances where you will need to move a database or restore databases. The most common reasons are:  
  • Moving to an entirely new server.
  • Moving to a different instance of SQL.
  • Creating a development server or going live to a production server.
  • Restoring databases from a backup.
  There are two main ways to move SQL databases. Manually with Microsoft SQL Server Management Studio (SSMS) or with the command line. The method you choose depends on what you need to accomplish. If you are moving a single database or just a few, manually backing up and restoring the databases with SSMS will be the easiest approach. If you are moving a lot of databases (think more than 10) then using the command line method will speed up the process. The command line method takes more prep work beforehand, but if you are transferring dozens of databases, then it is well worth the time spent configuring the script instead of migrating each database individually. If you aren’t sure which method to use, try the manual approach first while you get comfortable with the process. I recommend reading all the way through for a deeper understanding of the methodology.   Useful References for Terminology SSMS – An acronym for Microsoft SQL Server Management Studio. Source Server – The server or instance you are moving databases from or off. Destination Server – The server or instance you are moving databases to.  

Moving SQL databases with the manual method can be very easy. It is the preferred process for transferring a few or smaller databases. To follow this part of the guide, you must have MSSQL, and Microsoft SQL Server Management Studio (SSMS) installed.  

1. Begin by logging into the Source server (the server you are moving databases from or off of). You will want to open Microsoft SQL Server Management Studio by selecting Start > Microsoft SQL Server >  Microsoft SQL Server Management Studio. 2.Log into the SQL server using Windows Authentication or SQL Authentication. 3. Expand the server(in our case SQL01), expand Databases, select the first database you want to move (pictured below). Select your database within Microsoft SQL Server Management Studio. 4. Right click on your database and select Tasks then click Back Up. Back up button in Microsoft SQL Server Management Studio. 5. From here you are now at the Back Up Database screen. You can choose a Backup Type such as Full or Differential, make sure the correct database is selected, and set the destination for the SQL backup. For our example, we can leave the Backup Type as Full. 6. Under Backup Type, check the box for “Copy-only backup.” If you are running DPM or another form of server backup, backing up without the Copy-Only flag will cause a break in the backup log chain. 7. You will see a location under Destination for the path of the new backup. Typically you will Remove this entry then Add a new one to select a folder that SQL has read/write access. Adding a new Backup Destination shows a path similar to the following: C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\ This C:\ path is where your stored database backup is. Note this location for later reference, as this is the default path to stored backups and will have to have proper read/write access for SQL services.
Note:
Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.
8. Next, append a filename to the end of this path such as AdventureWorks2012-081418.bak – Be sure to end the filename with the extension .bak and select OKSet the file name with the .bak extension in Microsoft SQL Server Management Studio 10. Once you have pressed OK on the Select Backup Destination prompt, you are ready to back up the database! All you need to do now is hit OK, and the database will begin backing up. You will see a progress bar in the bottom left-hand corner, and when the backup is complete, a window will appear saying ‘The backup of database ‘AdventureWorks2012’ completed successfully. Navigate to the destination path, noted earlier, (in this case C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\) you will see your newly created file (in this case AdventureWorks2012-081418.bak) – Congratulations! This file is the full export of your database and is ready to be imported to the new server.  If you have more databases, then repeat the steps above for each database you are moving. After copying all database process to the next step of restoring databases to the destination server.  

You should now have a .bak file of all your databases on the source server. These database files need to be transferred to the destination server. There are numerous ways to move your data to the destination server; you can use USB, Robocopy or FTP. After copying a database you can store it on your destination server,  for our example, we have stored it on the C drive in a folder named C:\dbbackups . 1. Open Microsoft SQL Server Management Studio. 2. Log in to the SQL server using Windows Authentication or SQL Authentication. 3. Expand the server and right click on Databases and select Restore Database. 4. The Restore Database screen looks very similar to the Back Up Database screen.Under Source, you will want to select Device instead of Database. Selecting Device allows you to restore directly from a file. Once you’ve chosen Device, click the browse icon […] 5. Select Add, then navigate to the folder in which your .bak files lives. (In this case, C:\dbbackups). 6. Select the first database .bak you would like to restore and click OK. Select the .bak file to import your database into the destination server via SSMS. 7. Click OK and now you are ready to import the database. Before importing, let’s take a look at the Options section on the left-hand side. Under Options, you will see other configurations for restoring databases such as Overwrite the Existing Database, Preserve the Replication Settings and Restrict Access to the Restored DatabaseIn this case, we are not replacing an existing database so I will leave all these options unchecked. If you wanted to replace an existing database (for example, the backed up database has newer data than on the destination server or you are replacing a development or production database) then simply select Overwrite the Existing Database.
Note:
Advanced users may be comfortable leaving the destination as is, provided the permissions are correct on the output folder.
8. Clicking OK  begins the restore process as indicated by the popup window that reads ‘Database ‘AdventureWorks2012′ restored successfully.’ You have migrated your database from the source to the destination server. Repeat this process for each database that you are migrating. You can then update path references in your scripts/application to point to the new server, verify that the migration was successful.  

After importing your databases if you are unable to connect using your SQL login, you may receive the error ‘Login failed for user ‘example.’ (Microsoft SQL Server, Error: 18456).‘ Because the database is in the Traditional Login and User Model, logins are stored separately in the source server and credentials are not contained within the database itself. From this point on, the destination server can be configured to use the Contained Database User Model which keeps the logins in your database and out of the source server. Until then, we will have to move and interact with the users as part of the Traditional model. Continue below to proceed with the migration of your SQL users. Backing up and restoring the databases did move your SQL logins relation to the databases (your logins are still associated with the correct databases with the correct permissions) but the actual logins itself did not transfer to the new server.  You can verify this by opening SSMS (SQL Server Management Studio) on the destination server and navigating to Server > Security > Logins. You will notice that any custom SQL logins you created on the previous server did not transfer over here, but if you go to Server > Databases > Your Database (AdventureWorks2012 in this case) > Security > Users you’ll see the correct login associated with the database. If you have one or two SQL users, you can just delete the user’s association to the database in Servers > Databases > AdventureWorks2012 > Security > Users, re-create the user in Server > Security > Logins and map it to the proper database. If you have many logins, you will have to follow an additional process outlined below. To migrate all SQL users, open a New Query window on the source server and run the following script:
SQL Login Script
USE master GO IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL DROP PROCEDURE sp_hexadecimal GO CREATE PROCEDURE sp_hexadecimal @binvalue varbinary(256), @hexvalue varchar (514) OUTPUT AS DECLARE @charvalue varchar (514) DECLARE @i int DECLARE @length int DECLARE @hexstring char(16) SELECT @charvalue = '0x' SELECT @i = 1 SELECT @length = DATALENGTH (@binvalue) SELECT @hexstring = '0123456789ABCDEF' WHILE (@i <= @length) BEGIN DECLARE @tempint int DECLARE @firstint int DECLARE @secondint int SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1)) SELECT @firstint = FLOOR(@tempint/16) SELECT @secondint = @tempint - (@firstint*16) SELECT @charvalue = @charvalue + SUBSTRING(@hexstring, @firstint+1, 1) + SUBSTRING(@hexstring, @secondint+1, 1) SELECT @i = @i + 1 END SELECT @hexvalue = @charvalue GO IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL DROP PROCEDURE sp_help_revlogin GO CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS DECLARE @name sysname DECLARE @type varchar (1) DECLARE @hasaccess int DECLARE @denylogin int DECLARE @is_disabled int DECLARE @PWD_varbinary varbinary (256) DECLARE @PWD_string varchar (514) DECLARE @SID_varbinary varbinary (85) DECLARE @SID_string varchar (514) DECLARE @tmpstr varchar (1024) DECLARE @is_policy_checked varchar (3) DECLARE @is_expiration_checked varchar (3) DECLARE @defaultdb sysname IF (@login_name IS NULL) DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa' ELSE DECLARE login_curs CURSOR FOR SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM sys.server_principals p LEFT JOIN sys.syslogins l ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name OPEN login_curs FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin IF (@@fetch_status = -1) BEGIN PRINT 'No login(s) found.' CLOSE login_curs DEALLOCATE login_curs RETURN -1 END SET @tmpstr = '/* sp_help_revlogin script ' PRINT @tmpstr SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' PRINT @tmpstr PRINT '' WHILE (@@fetch_status <> -1) BEGIN IF (@@fetch_status <> -2) BEGIN PRINT '' SET @tmpstr = '-- Login: ' + @name PRINT @tmpstr IF (@type IN ( 'G', 'U')) BEGIN -- NT authenticated account/group SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']' END ELSE BEGIN -- SQL Server authentication -- obtain password and sid SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) ) EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT -- obtain password policy state SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']' IF ( @is_policy_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked END IF ( @is_expiration_checked IS NOT NULL ) BEGIN SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked END END IF (@denylogin = 1) BEGIN -- login is denied access SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name ) END ELSE IF (@hasaccess = 0) BEGIN -- login exists but does not have access SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name ) END IF (@is_disabled = 1) BEGIN -- login is disabled SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE' END PRINT @tmpstr END FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin END CLOSE login_curs DEALLOCATE login_curs RETURN 0 GO
This script creates two stored procedures in the source database which helps with migrating these logins. Open a New Query window and run the following: EXEC sp_help_revlogin This query outputs a script that creates new logins for the destination server. Copy the output of this query and save it for later. You will need to run this on the destination server. Once you’ve copied the output of this query, login to SSMS on the destination server and open a New Query window. Paste the contents from the previous script (it should have a series of lines that look similar to — Login: BUILTIN\Administrators CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE = [master]) and hit Execute. You have now successfully imported all SQL logins and can now verify that the databases have been migrated to the destination server by using your previous credentials.

Views and stored procedures will migrate with the database if you are using the typical SQL Tape backups. Follow the instructions below if you need to migrate views and stored procedures independently.
  1. Open Microsoft SQL Management Studio on the Source server.
  2. Log in to your SQL server.
  3. Expand the server and as well as Databases.
  4. Right click on the name of your database and go to Tasks > Generate Scripts.
  5. Click Next.
  6. We will change Script entire database and all database objects to Select specific database objects and only check Views and Stored Procedures.Transfer Stored Procedures and Views within Microsoft SQL Server Management Studio
  7. Click Next, notice the Save to File option. Take note of the file path listed. In my case, it is C:\Users\Administrator\Documents\script.sql – The path of saved views and stored procedures.
  8. Click Next >> Next >>Finish, and select C:\Users\Administrator\Documents\script.sql and copy it to the destination server.
  9. Go to the destination server, open SSMS and log in to the SQL server.
  10. Go to File > Open > File or use the keyboard shortcut CTRL+O to open the SQL script. Select the file C:\Users\Administrator\Documents\script.sql to open it.
  11. You will see the script generated from the source server containing all views and stored procedures. Click Execute or use the keyboard shortcut F5 and run the script.
Note:
Unfortunately, there is no built-in way to do this with the command line. There are 3rd party tools and even a tool by Microsoft called mssql-scripter for more advanced scripting.
You have now migrated the views and stored procedures to your destination server! Repeat this process for each database you are migrating. A little guidance goes a long way in database administration. Every SQL server will have its own configurations and obstacles to face, but we hope this article has given you a strong foundation for your Microsoft SQL Server Migration. Looking for a High Availability, platform-independent SQL service that is easily scalable and can grow with your business? Check out our SQL as a Service product offered at Liquid Web. Speak with one of our amazing Hosting Advisers to find the perfect solution for you!  

Installing SQL Express

Reading Time: < 1 minute

MSSQL Express 2017 on a Dedicated Server

Microsoft SQL Server is a powerful database that is commonly used with ASP.NET and other website programming languages. However, licensing for MSSQL can be expensive and is sometimes prohibitive for smaller businesses and applications. Fortunately, Microsoft provides a free version of MSSQL server called MSSQL Express. Installing MSSQL Express on your dedicated server is quick and easy, especially with the new features included in MSSQL Express 2017. Continue reading “Installing SQL Express”