Linux SQL Server

Discussions relating to plugin development, and the Jiwa API.

Linux SQL Server

Postby pricerc » Tue Mar 03, 2020 10:32 am

Have y'all tried using a Linux SQL Server for a JIWA database yet?

I would expect it to work, since it's "just a database server", but thought I should ask.

It occurred to me that it might be something that 2nd-tier cloud providers might offer as a (possibly cheaper) alternative to Windows-based SQL Servers.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Linux SQL Server

Postby Mike.Sheen » Tue Mar 03, 2020 10:33 am

pricerc wrote:Have y'all tried using a Linux SQL Server for a JIWA database yet?

I would expect it to work, since it's "just a database server", but thought I should ask.

It occurred to me that it might be something that 2nd-tier cloud providers might offer as a (possibly cheaper) alternative to Windows-based SQL Servers.


Not tried it - but there's absolutely no reason why it would not work.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Linux SQL Server

Postby Scott.Pearce » Tue Mar 03, 2020 10:34 am

If you try it, please do let us know how it goes.
Scott Pearce
Senior Analyst/Programmer
Jiwa Financials
User avatar
Scott.Pearce
Senpai
Senpai
 
Posts: 742
Joined: Tue Feb 12, 2008 11:27 am
Location: New South Wales, Australia
Topics Solved: 221

Re: Linux SQL Server

Postby SBarnes » Tue Mar 03, 2020 2:03 pm

I suppose you could test it under docker, there's plenty of information around on doing that on a mac such as https://database.guide/how-to-install-s ... -on-a-mac/ I have been meaning to try find the time to play with it and asp.net core.

You'd have to assume it would be fairly close to that to make it happen.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Linux SQL Server

Postby pricerc » Tue Mar 03, 2020 8:25 pm

Jiwa on SQL Linux.png


Installing SQL in Linux is way less work than installing it in Windows.

I used Ubuntu Budgie, which I happened to have an ISO for, and just did a 'vanilla' installation of said Ubuntu into a new VM, after which I followed the SQL Server installation instructions for Ubuntu.

No GUI management tools, but then I've got a Windows PC for JIWA with SSMS on it.

Jiwa database wizard worked a treat. At first, I thought it was stuck on restoring the bacpac file, but I was just being impatient.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Linux SQL Server

Postby pricerc » Tue Mar 03, 2020 8:28 pm

SBarnes wrote:I suppose you could test it under docker


there's a whole ecosystem I've yet to try.

I've yet to try docker, but then I almost never do the same thing twice when it comes to system deployments, so I'm not sure I'm the target market.
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Linux SQL Server

Postby Mike.Sheen » Tue Mar 03, 2020 11:02 pm

pricerc wrote:At first, I thought it was stuck on restoring the bacpac file, but I was just being impatient.


Yeah - I'm yet to drill into why it takes so long to either export to bacpac or import from BACPAC, but it's slow everywhere when compared to the traditional backup and restore operations. In previous versions of Jiwa 6 we went to insane efforts to package demo and new databases into our own format (a .zip containing SQL queries and bulk data copy aka BCP) and there was literally several thousand lines of code which used to perform the restore of a database. All replaced now with about 10 lines of code in version 7 which now uses SMO to operate on BACPAC files instead.

Apologies for the increased time in creating a new demo database or a blank one, but it made sense to hand off to an official Microsoft SQL library (SMO) to handle those operations for us instead of our awesomely fast custom one, which would have been prone to failure in future versions of SQL and probably have not handled odd edge cases, or a wider range of supported SQL Server versions.

pricerc wrote:
SBarnes wrote:I suppose you could test it under docker


there's a whole ecosystem I've yet to try.

I've yet to try docker, but then I almost never do the same thing twice when it comes to system deployments, so I'm not sure I'm the target market.


Docker is great, but like you I'm yet to find myself a problem where I would find it useful. One thing which I did see a use for - I did try dockerising Jiwa + SQL Server in various combinations of Jiwa and SQL server at one point to allow our support dept. to rapidly create environments for testing, but the user experience back then (and it's likely much the same now) was all CLI and it wasn't well received. If I had the time I would have layered a gui or web interface over it to make it more palatable - but... well - you probably know how it is.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Linux SQL Server

Postby Mike.Sheen » Tue Mar 03, 2020 11:29 pm

Whilst on the topic of BACPAC files - don't ever generate one from an in-use database and expect a consistent database to be within the BACPAC produced.

If you generate a BACPAC (from SSMS it's "Export Data Tier Applicaction") - it doesn't snapshot the database state, so if transactions were in play when exporting to BACPAC, when you restore the BACPAC ("Import Data Tier Application" from SSMS) then you have a very real risk (confirmed from my own personal experience) that that data you end up with is inconsistent.

It exports uncommitted data, so importing that BACPAC means bits will be missing if it was in use during export.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Linux SQL Server

Postby SBarnes » Wed Mar 04, 2020 4:50 pm

I would assume you could get the same problem with Azure SQL when you export the database given it creates a bacpac file in blob storage?
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Linux SQL Server

Postby Mike.Sheen » Wed Mar 04, 2020 5:03 pm

SBarnes wrote:I would assume you could get the same problem with Azure SQL when you export the database given it creates a bacpac file in blob storage?


I know for sure that Azure SQL suffers the same.

The work-around for Azure is to Copy the database first - which is really stupidly fast and more importantly results in a consistent database - and then generate a bacpac from that.

But you don't have to create it in Blob storage, point your local SSMS to an Azure db and you can import and export locally to and from Azure.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Next

Return to Technical and or Programming

Who is online

Users browsing this forum: No registered users and 3 guests

cron