MS SQL
MS SQL
Background
The web site PeacePays.org (which won't work for you) uses a Flash front end (support ceased 01/01/2021), has a MS SQL database back end and is hosted on an expensive web hosting provider. It would be nice to re-create the site using some supported technical solution and with updated content. My task:
- get the content out into human-readable form;
- try to create a working copy of the site for reference;
- remove the site from its current hosting company.
The developers of the web browser Pale Moon are committed to continuing to provide Flash functionality, despite Adobe removing support for Flash. This runs under Linux and Windows and provides a way to run the Flash content on the site. Getting Pale Moon working under Linux gave its own challenges.
Having copied the files from the PeacePays,org web site to a PC, I can disconnect the PC from the Internet and run the Flash part of the site in Pale Moon, but it requires the MS SQL database to work. So I need to:
- install MS SQL on the PC, in particular MS SQL Server
- transfer the database to the PC
- see if I can edit the Flash code to point to the new database location.
I am committed to Linux partly on principle and partly because I only have Windows 7. MS SQL will run on older versions of Linux but not Windows 7.
I looked at converting the database from MS SQL to something Linux compatible such as MySQL, but since I am unfamiliar with the content and structure of the database, I cannot predict what problems the compatibility changes will cause.
There is a free version of MS SQL for Linux.
Installing MS SQL in Linux
I am running Xubuntu 18.04.5
Microsoft says SQL Server is available on Ubuntu 16.04 and 18.04, with XFS or EXT4 file systems (I only use ext4). So that's OK.
A repository is required from which to install the relevant packages. Choices are between SQL Server 2017 v SQL Server 2019 and CU v GDR. Web sites say 2019 is better than 2017. The GDR (? ? ?) repository only has critical fixed; the CU (Cumulative Update) repository has all updates. I'll go for 2017 (I don't need much functionality, just a database to connect to) and GDR (I don't need to be pestered about updates).
Installed the GPG keys:
sudo curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
Added the repository for SQL Server 2017 GDR
sudo add-apt-repository "$(curl https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2017-gdr.list)"
returns
Error: '<html> <head><title>404 Not Found</title></head> <body> <center><h1>404 Not Found</h1></center> <hr><center>nginx/1.16.1</center> </body> ' invalid
and
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2017-gdr.list)"
returns
Error: '' invalid
Turns out there is no GDR / CU versions of SQL Server 2017, so the command is just:
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2017-gdr.list)"
which worked.
sudo apt-get update sudo apt-get install mssql-server
Then, as instructed:
sudo /opt/mssql/bin/mssql-conf setup
Various options for licensing come up; I used "3) Express (free)". I used an appropriate password for SQL Server in January 2021.
Is the MS SQL Server server instance running?
This command says whether the SQL Server service is running:
systemctl status mssql-server --no-pager
Add the repository for the SQL Server command line tools and install them
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/prod.list)" sudo apt-get update sudo apt-get install mssql-tools unixodbc-dev
Add the sqlcmd command to the path:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
but that did not work, now even when I changed .bash_profile to .profile . Running sqlcmd requires the full path:
/opt/mssql-tools/bin/sqlcmd
To actually run it requires this command:
sqlcmd -S localhost -U SA -P '<YourPassword>'
where 'localhost' means this PC and 'SA' us the username that is used by default. That worked, and I was able to create a database and remove it:
CREATE DATABASE TestDB SELECT Name from sys.Databases GO DROP DATABASE TestDB GO
To confirm the version and edition of SQL SERVER in use: Transact-SQL query to verify the version and edition of SQL Server that you are running:
SELECT @@VERSION
GO
SELECT SERVERPROPERTY('Edition')
GO
Installing Azure Data Studio
That can be downloaded and installed from somewhere. Oops. Did too many steps without documenting them.
Right click on the server with the database, Data-tier Application wizard, export the scheme and data from a database.
Right click on localhost, Data-tier Application wizard, create a database from a .bacppac
Select the downloaded .bacppac file and create a new local database.