Transfer an SSL to Ubuntu 16.04 or CentOS 7

SSL certificates have become a de facto part of every website. If you don’t yet have an SSL on your site to encrypt data, you should. Rather than showing an extra layer of security on sites protected by SSL, modern browsers instead now display a warning when a website does not have an SSL, essentially requiring sites to maintain their positive image.

When moving from one server to another, what needs to happen to your SSL to maintain your secure status? We’ll cover the basics for transferring traditional and Let’s Encrypt SSLs to Ubuntu 16.04 and CentOS 7.

Note:
This article will address SSLs in Apache specifically, but the same concepts apply to any service that supports SSL encryption.

Can SSLs be transferred between servers?

Absolutely! An SSL consists just of a handful of text files and a bit of configuration for the secure service. Additionally, there is a misconception that certificates are tied permanently to their IP address or physical server, but this is not true. In reality, they are linked only to the domain name(s) listed the certificate and are very migratable. Technically, an SSL can be used on multiple servers or services to protect the same domain name. We will leverage this concept to help us move our SSLs.

Prerequisites

You will need to have a copy of your SSL Certificate and SSL Private Key, as well as any ‘Chain Certificates.’ Often these will be two separate files, called domain.com.crt for the certificate, and domain.com.key for the private key, as well as an additional signing_authority.crt file for the chain certificate. Another popular naming convention for these files is cert.pem and key.pem, but sometimes they have no file suffix at all (.pem or .crt).

How can you tell where to get these files?  We’ll get into how to determine the locations for these configurations first.

Step 1: Collecting SSL Files

Debian SSL File Location

In distributions of Linux, including Ubuntu, you can check the loaded virtual hosts on Apache using:

apache2ctl -S

 

CentOS SSL File Location

For RHEL based distributions, including CentOS, you can use:

httpd -S

 

Both of these commands will list the running configuration settings, including all loaded virtual hosts and their configuration file locations. We’re looking for the domain name of the SSL we want to copy, and the HTTPS port 443. On my Ubuntu test machine, for example, this line is the output for the domain in question:

port 443 namevhost domain.com (/etc/apache2/sites-enabled/domain.com.conf:1)

Since we are copying the SSL for the domain.com domain, we’ll check this configuration file, and look for the virtual host block that references port 443.

vim /etc/apache2/sites-enabled/domain.com.conf

Searching through the file, we find:

<VirtualHost *:443>
ServerName domain.com
ServerAlias www.domain.com
DocumentRoot /data/www/domain.com
SSLCertificateFile /etc/ssl/domain.com/cert.pem
SSLCertificateKeyFile /etc/ssl/domain.com/privkey.pem
SSLCertificateChainFile /etc/ssl/domain.com/chain.pem
</VirtualHost>

This block of text describes all three files that we need to bring to the new server: the certificate file (cert.pem), the private key (privkey.pem) and the intermediate certificates (chain.pem).

 

Step 2: Copying SSL Files

From the file paths we discovered in the last step, we know that all of the SSL files for this site exist in the /etc/ssl/domain.com directory. So, we can copy this whole folder to the new server using the rsync tool. Assuming that the old server has an IP of 123.45.67.89, hop over to your new server, and run the command below. Trailing slashes (/) are very important for this command!

mkdir -p /etc/ssl
rsync -avz root@123.45.67.89:/etc/ssl/domain.com /etc/ssl/

This method preserves the ownership and permissions of these files, so we should not have to do anything else in that regard.  Our next section, step 3, is split into two sections.  Pick step 3A if you’re new server is Ubuntu 16.04 or step 3B if you new server is CentOS 7.

 

Step 3A: Setting up Apache on the new server (Ubuntu 16.04)

A non-SSL virtual host set up is necessary for your domain on the new server. If you have not yet done so, you will need to install and activate mod_ssl to allow Apache to parse SSL communication on the target machine:

apt-get install mod_ssl
a2enmod ssl

If you get a message that mod_ssl is not available for installation, its possibly installed through a different package. Check to see if that is the case:

dpkg -S mod_ssl.so

If this returns a positive result, you ‘ll run:

a2enmod ssl to complete the activation. After enabling mod_ssl, restart Apache:

systemctl restart apache2

Lastly, there is one configuration file to change, which will allow serving named virtual hosts. The activation of the SSL module lets Apache listen on port 443, but we need to be able to host multiple sites per IP. Open up the /etc/apache2/ports.conf file:

vim /etc/apache2/ports.conf

Ensure that the contents mimic this output:

<IfModule ssl_module>
NameVirtualHost *:80
Listen 80
NameVirtualHost *:443
Listen 443
</IfModule>

Specifically, we are ensuring that the NameVirtualHost lines are present. Once this is true, we can test and reload the configuration to bring it into effect:

apachectl configtest
systemctl reload apache2

Note:
Even though enabled sites have configurations listed in /etc/apache2/sites-enabled/, the actual configuration files should live in /etc/apache2/sites-available/, so that the apache control scripts can turn sites on and off. The sites-enabled folder consist exclusively of symlinks to configurations in sites-available.

Next, we must add a configuration for the website to call the SSL files we just copied over. If you are using the same document root on the new server as on the old, you can copy and paste the original virtual host block into a new configuration file in /etc/apache2/sites-available/. Be sure to update referenced IPs to match the new server.

If there are differences in the running modules or other customizations performed on the new machine, make a copy of the existing non-SSL virtual host block on the target server into a separate file. Also, under /etc/apache2/sites-available/, update the port number, and add the three lines for the SSL files. For our example, I copied and pasted the exact contents of the old file into /etc/apache2/sites-available/domain.com_ssl.conf.

After having done so, we run:

a2ensite domain.com_ssl

This links the configuration into the /etc/apache2/sites-enabled/ folder. This site name will change depending on what you named the configuration file (just cut off the .conf). Next, we test this new configuration:

apachectl configtest

If everything comes back OK, we can reload the configuration files for the apache2 service:

systemctl reload apache2

 

Step 3B: Setting up Apache on the new server (CentOS 7)

Each command line operation for CentOS is very different, but the overall procedure is the same, and the configuration files will have similar content. First, confirm that Apache is configured to support SSL traffic:

yum install mod_ssl

This installation procedure should automatically set up a ssl.conf file for you in /etc/httpd/conf.d/ for listening on port 443 and restart Apache, but it may not enable named virtual hosts. Make sure you add the following line to your httpd.conf file as well if it is not already present. Adding these lines allows you to serve multiple sites per IP address using virtual host blocks.

<IfModule mod_ssl.c>
NameVirtualHost *:443
</IfModule>

Next, we must add a configuration for the website to call the SSL files we just copied over. If you are using the same document root on the new server as on the old, copy and paste the original virtual host block into a new configuration file in /etc/httpd/conf.d/, making sure that the IP addresses are updated to match those on the target server.

If there are differences in the running modules or other customizations performed on the new machine, you can make a copy of the existing non-SSL virtual host block on the target server into a separate file. Also, in /etc/httpd/conf.d/, update the port number, and add the three lines for the SSL files.

Once created, test the configuration syntax of the file:

httpd -tThe output will either say ‘Syntax OK’ or tell you what file and line is problematic. If everything is okay, you can reload the configuration for Apache:

systemctl reload httpd

 

What if I use Let’s Encrypt?

Let’s Encrypt SSLs are just as transferable as any other SSL, but manually creating an SSL virtual host config file sometimes causes conflict with future installations of Let’s Encrypt. So, there is a particular procedure to be followed.

Transferring Let’s Encrypt Installations

First, Let’s Encrypt should be installed on the new server. For Ubuntu 16.04, that looks like this:

add-apt-repository ppa:certbot/certbot
apt-get update
apt-get install python-certbot-apache

 

For CentOS 7, use these commands:

yum install epel-release
yum install python-certbot-apache

Once installed on either distro, set a cron to renew the certificates automatically. You can set these up by running

crontab -e as the root user. The cron on my Ubuntu machine looks like this:

45 2 * * 6 /usr/local/letsencrypt/certbot-auto renew && systemctl reload apache2

On my CentOS server, it looks like this:

45 2 * * 6 /usr/local/letsencrypt/certbot-auto renew && systemctl reload httpd

Next, we can bring over the entire /etc/letsencrypt directory, which houses the configuration files. This directory holds the certificates and the keys themselves. On the new server, we run:

rsync -avz 123.45.67.89:/etc/letsencrypt /etc/

Now that the files are in place, we can have Let’s Encrypt reinstall all of the certificates we synced over:

/usr/local/letsencrypt/certbot-auto install --apache

Running the command presents a numbered list of the certificates in the /etc/letsencrypt folder. Select the number for the site you’d like to set up a configuration for, and it will create your configuration file; Once done, you only need to reload Apache:

systemctl reload apache2 #ubuntu
systemctl reload httpd #centos

 

Step 4: Confirming operation

The new server can now be tested using hosts file modification, detailed in this article. It’s truly is the best way to test the functionality of a migration target, as both browser and server believe that this is real traffic on the real domain name.

Once you set up your hosts file and flush your DNS cache, you should simply be able to load up the website in your browser using https, and see your website secured with the original certificate on the target machine! No worries though; live traffic is still routed to the original server with the same SSL, so your site visitors will not notice your testing.

After you finish testing your SSL installations and have confirmed that your websites are working well on the new server, DNS for the migrated sites can be updated to make the new server live.

If you are using Let’s Encrypt, it is important to keep your certificate expiration dates in mind. For instance, if your certificates are slated to expire in 7 days, you will want to update DNS promptly after the transfer, so that the new server can take over certificate renewals. If you miss this window and the certificate renews on the source server, you can simply re-run the rsync command where we collected /etc/letsencrypt:

rsync -avz 123.45.67.89:/etc/letsencrypt /etc/

Need help ordering a signed SSL or ordering a new server for your migration target? Chat with our solutions team!

 

SQL Databases Migration with Command Line

What 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.

1. Open SSMS (Microsoft SQL Server Management Studio) on the source server, log in to the SQL instance and open a New Query window. Run the following query:

SELECT name FROM master.sys.databases

This command will output a list of all MSSQL databases on your server. To copy this list out, click anywhere in the results and use the keyboard shortcut CTRL+A (Command + A for Mac users) to select all databases. After highlighting all the databases right click and select copy.

2. Open Notepad, paste in your results and delete all databases (in the newly copied notepad text) you do NOT wish to migrate, as well as deleting the following entries:

  • master
  • tempdb
  • model
  • msdb

These entries are the system’s databases, and copying them is not necessary. Make sure to delete everything except explicitly the databases you need to migrate.  You should now have a list of all required databases separated by a line. i.e.

  • AdventureWorks2012
  • AdventureWorks2014
  • AdventureWorks2016

3. Save this result on the computer as C:\databases.txt.

4. Create a new Notepad window, copy/paste the following into the document and save it as C:\db-backup.bat

mkdir %systemdrive%\dbbackups
for /F "tokens=*" %%a in (databases.txt) do ( sqlcmd.exe -Slocalhost -Q"BACKUP DATABASE %%a TO DISK ='%systemdrive%\dbbackups\%%a.bak' WITH STATS" )

5. Now that you’ve saved the file as C:\db-backup.bat, navigate to the Start menu and type cmd and right click on Command Prompt to select Run as Administrator.Type the following command:

cd C:\

And hit enter. Afterward, type db-backup.bat and hit enter once again.

At this point, your databases have begun exporting and you will see the percentage progress of each databases export (pictured below).

Command line shows the process of each database that is exported.

Take note of any failed databases, as you can re-run the batch file when it’s done, using only the databases that may have failed. If the databases are failing to back up, take note of the error message displayed in the command prompt, address the error by modifying the existing C:\databases.txt file to include only the failed databases and re-run db-backup.bat until all databases are successfully exported.

 

By now you have the folder C:\dbbackups\ that contains .bak files for each database you want to migrate. You will need to copy the folder and your C:\databases.txt file to the destination server. There are numerous ways to move your data to the destination server; you can use USB, Robocopy or FTP. The folder on the C drive of the destination server should be called C:\dbbackups . It’s important to accurately name the file as our script will be looking for the .bak files here. Be sure that the destination server has your C:\databases.txt file as well, as our script will be looking for the database names here.

 

1. Open a Notepad and copy/paste the following into the document and save it as C:\db-restore.bat

for /F "tokens=*" %%a in (C:\databases.txt) do (
sqlcmd.exe -E -Slocalhost -Q"RESTORE DATABASE %%a FROM DISK='%systemdrive%\dbbackups\%%a.bak' WITH RECOVERY"
)

2. Save the file as C:\db-restore.bat 

3. Navigate to the Start menu and type cmd.

4. Right click on Command Prompt and select Run as Administrator. Type the following command:

cd C:\

and hit Enter. Now type db-restore.bat and hit Enter.

Your databases have now begun importing. You will see the percentage of each databases restoration and the message “RESTORE DATABASE successfully processed” for each database that has been successfully processed.

Take note of any failed databases, as you can re-run the batch file when it’s done, using only the databases that have failed. If the databases are failing to back up, take note of the error message displayed in the command prompt, address the error (you can change the batch file as necessary), modify C:\databases.txt to include only the failed databases and re-run db-restore.bat until all databases are successfully exported.

Congratulations, you have now backed up and restored all of your databases to the new server. If you have any login issues while testing the SQL connections on the destination server, refer to the Migrating Microsoft SQL Logins (anchor link) section of this article and follow the steps therein. To migrate views or stored procedures please refer to the Migrating Views and Stored Procedures section. Every SQL server will have it’s own configurations and obstacles to face but we hope this article has given you a strong foundation for your Microsoft SQL Server Migration.

 

SQL Database Migration with SSMS

Migrating 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. (You can read more about this here.)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 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 a lot of 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 it’s 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!

 

Using rsync to Transfer and Synchronize Local and Remote Systems

Rsync is a powerful tool that facilitates the transfer and synchronization of data between both local and remote systems.

File Synchronization 101: The Basics
I. Using rsync to Transfer and Synchronize Local and Remote Systems
II. How to Setup SSH Keys for Use with File Synchronization

 

Continue reading “Using rsync to Transfer and Synchronize Local and Remote Systems”