Jiwa 7 and Azure  Topic is solved

Discussions relating to plugin development, and the Jiwa API.

Jiwa 7 and Azure

Postby SBarnes » Thu Jun 09, 2016 5:53 pm

Hi Guys

Are you able to list the components that need to be setup in order to get Jiwa working under Microsoft Azure, it doesn't need to be terribly detailed, just a listing of the basic steps/components required.

Thanks.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Jiwa 7 and Azure  Topic is solved

Postby Mike.Sheen » Thu Jun 09, 2016 10:59 pm

The ideal scenario is to use RemoteApp to deliver the client, as that will automatically spin up VM's as needed - so the following steps are based around that

The first step is to create a base VM - you'll want to keep this for when you need to update the RemoteApp image with new versions of Jiwa :
  • 1. Create a virtual machine (VM) from gallery.
    • a. Image: Windows Server Remote Desktop Session Host
    • b. Version Release Date: Latest date.
    • c. Virtual Machine Name: JiwaBase
    • d. Tier: Standard
    • e. Size: A3 (4 cores, 7GB memory) - not important as RemoteApp will provision it's own sized machines on demand
    • f. New User Name: jiwaadmin (or choose another)
    • g. New Password: <VM admin password>
    • h. Cloud Service: Create a new cloud service
    • i. Cloud Service DNS Name: JiwaBase
    • j. Subscription: <Name of sub>
    • k. Region: Australia East
    • l. Storage Account: Use an automatically generated storage account
    • m. Availability Set: None
    • n. Endpoints: [Defaults]
  • 2. Configure the VM.
    • a. Under Control Panel->Clock, Language, and Region->Region
      • i. Formats tab:
        1. Set the Format to English (Australia). Apply.
        2. Set the Short date to dd/MM/yyyy. Apply.
      • ii. Location tab:
        1. Home location to Australia
      • iii. Administrative tab:
        1. Change system locale to English (Australia)
    • b. Reboot (as prompted by previous step)
    • c. Under Control Panel->Clock, Language, and Region->Region
      • i. Administrative tab:
        • 1. Copy settings to:
          a. Welcome screen and system accounts
          b. New user accounts
    • d. Under Control Panel->Clock, Language, and Region->Date and Time
      i. Change time zone to (UTC +10:00) Canberra, Melbourne, Sydney
  • 3. Execute the ValidateRemoteAppImage powershell script from the VM desktop to prepare the VM for RemoteApp creation. The VM will shut down when complete.
  • 4. Create an image from the VM
    • a. Select the VM and click “Capture”
      • i. Image Name: JiwaBase
      • ii. Image Description (Label): "Windows Server Remote Desktop Session Host" <Gallery Image Date>. Location Australia. Timezone UTC+10:00.
Now create a RemoteApp image, SQL Server and publish the RemoteApp:

  • 1. Create a virtual machine (VM) from gallery.
    • a. Image: JiwaBase (created in previous section)
    • b. Host Name: <choose a host name> (you may have to abbreviate the name, as there is a limit).
    • c. User name: jiwaadmin
    • d. Password: <VM admin password>
    • e. Pricing Tier: Standard A3 (4 cores, 7GB memory - again, not important as this will be ignored when RemoteApp creates it's own VM's)
    • f. Optional Configuration -> OS Settings -> Time Zone: UTC+10
    • g. Resource Group: Default-Storage-AustraliaEast
    • h. Subscription: <Name of sub>
    • i. Location: Australia East
  • 2. Create a SQL Server.
    • a. Subscription: <Name of sub>
    • b. Login Name: jiwaadmin
    • c. Login Password: <SQL Server jiwaadmin password>,
    • d. Region: Australia East
    • e. Allow Windows Azure Services to Access the Server: <Ticked>
    • f. Enable Latest SQL Database Update (V12): <Ticked> NOTE: This may no longer be visible - if not then ignore this step
    • g. When created, select server, choose Configure. Add this rule to the “allowed ip addresses”, then click “Save” = this is to allow you to connect via Management Studio:
      i. Rule Name: <choose a rule name>
      ii. Start IP Address: <your ip address>
      iii. End IP Address: <your ip address>
      iv. Allowed Services:
      1. Windows Azure Services: Yes
  • 3. Connect to the VM created in step 1 IN THIS SECTION.
  • 4. Install Jiwa. Reboot (as prompted).
  • 5. Run Jiwa and delete all existing connections.
  • 6. Create a Jiwa demonstration database:
    • a. Task: Create a new Jiwa demonstration database
    • b. Server: <Generated SQL server name from step 5>.database.windows.net
    • c. SQL Login: jiwaadmin
    • d. SQL Password: <password defined at step 2c>
    • e. Database Name: JiwaDemo
    • f. Please automatically add the database to my connection list: Ticked
    • g. Expand Advanced:
      i. Reset all staff SQL logins and passwords: Ticked
      ii. SQL Password: <SQL Password>
      iii. Reports Password: <Reports SQL Password>
  • 7. Copy %appdata%\Jiwa Financials\JiwaConnections.xml to c:\Program Files (x86)\Jiwa Financials\Jiwa 7\JiwaConnectionsTemplate.xml
  • 8. Using SQL Management Studio, connect to the Azure SQL Server and create the JiwaLogin user in the master database
    a. CREATE USER JiwaLogin
  • 9. Using SQL Management Studio, change the password for JiwaUser and JiwaReports to match those used in step 6) g) ii) and 6) g) iii).
    a. ALTER LOGIN JiwaUser WITH PASSWORD = '<SQL Password>'
    b. ALTER LOGIN JiwaReports WITH PASSWORD = '<Reports SQL Password>'
  • 10. Log in to the database created in steps 7.
    a. Add licences if required (C:\Program Files (x86)\Jiwa Financials\Jiwa 7\Licences)
  • 11. In the Azure portal, set the service tier for the SQL database as desired
  • 12. In the Azure portal, set the Max Size for the database as desired.
  • 13. Execute the ValidateRemoteAppImage powershell script from the VM desktop to prepare the VM for RemoteApp creation. The VM will shut down when complete.
  • 14. In the Azure console, select the VM and choose “Capture”.
    a. Image Name: <Choose an image name>
    b. Image Label: <Choose an image label>
    c. I have run Sysprep on the virtual machine: Ticked
  • 15. Add (Import) the image created above into RemoteApp->Template Images.
    a. Name: Jiwa-v<Jiwa version number>
    b. Location: Australia East (or other location)
    c. Subscription: <Name of sub>
  • 16. Delete the image from Virtual Machines -> Images (it has been copied into RemoteApp Template Images now).
  • 17. Delete the Cloud Service called “<Name of sub>-v<Jiwa version number>” (the name of the sub may be abbreviated).
  • 18. Create a new Remote App Collection. This typically takes around 30 minutes to complete.
    a. Activate the subscription (if required)
    b. Choose Quick Create
    c. Name: <Name of sub>Jiwa
    d. Region: Australia East
    e. Subscription: <Name of sub>
    f. Plan: Premium (if less than 10 users), Standard (if 10 or over users).
    g. Template Image: jiwa-v<Jiwa version number>
  • 19. Change the End-User Feed Name to <Name of sub> (under Settings->RemoteApp).
  • 20. Install Azure Powershell on your local machine if required, run it and perform the following:
    • a. Log in to the azure subscription:
      i. Add-AzureAccount
    • b. Switch to the <Name of sub> subscription:
      i. Select-AzureSubscription <Name of sub>
    • c. Enabled both USB and driver redirection for the Remote App Collection:
      i. Set-AzureRemoteAppCollection -CollectionName <collection name> -CustomRdpProperty "drivestoredirect:s:*`nusbdevicestoredirect:s:*"
  • 21. Publish the Remote App Programs
    a. Jiwa: Ticked
  • 22. Add Microsoft account usernames as users to the remote app.

When this is all done, users should be able to see the Jiwa application from the RemoteApp client when run. There is also a HTML5 version of this in preview, so they don't need to install a client.

There could be some mistakes in the above: I took our procedure we use internally (as we use Azure RemoteApp and Azure SQL for our internal Jiwa database) and modified it to be a bit more generic - but it should set you on your way. Also apologies for the formatting - I had it indented nicely, but phpbb messed it all up when I submitted.

A few notes on pricing:

The SQL database is priced per database per service tier. Internally we use S2 and that's $103 per month, AUD currently. This service tier is adequate for a production database with moderate to high performance demands.
The RemoteApp cost is a per user per month, I won't attempt to lay out the options, as they are in flux, so visit the pricing calculator to work that out. The good news is all the price changes I've seen the last 2 years has been downwards.
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: Jiwa 7 and Azure

Postby SBarnes » Thu Jun 09, 2016 11:35 pm

Hi Mike,

This it should be enough to get me started in getting it sorted, no need for aplogies on the formatting, its the content that counts, by the way the forum didn't send an email??

But I'm not complaining about that as I know you've been working on it from what Beth let me know, just letting you know in case you expected it to, as I said I can live with just checking back.

Thanks for the assistance and prompt reply.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Jiwa 7 and Azure

Postby Mike.Sheen » Thu Jun 09, 2016 11:57 pm

SBarnes wrote:by the way the forum didn't send an email??


That's still a work in progress, we've changed DNS providers as the problem I think was erroneous SPF records due to a fault with our DNS provider - that should be fixed, but I need to get around to some testing an diagnostics. Networking infrastructure and DNS isn't really in my knowledge domain, but I think in a few days we should have the email issue sorted.

Thanks for your patience.
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: Jiwa 7 and Azure

Postby SBarnes » Wed Jun 15, 2016 12:02 am

Hi Mike,

Thanks for the update happy to wait until you can get to it, as I have no doubt you've got a hundred other things to do, I was just letting you know in case you thought it was resolved.

Thanks for looking at it, let me know if you need any feedback from this end once you think its resolved.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Jiwa 7 and Azure

Postby DannyC » Thu Jul 07, 2016 10:12 am

Hi,
I have a client keen to get running on Azure.

After battling issues with creating a BACPAC from SQL Server 2012 (I ended up downloading Management Studio for 2016 which has more compliance with new Azure features so is more forgiving when generating the BACPAC), and then working out why I couldn't connect the Jiwa client to the database (I followed your steps and I think the trick was step 8 - create the JiwaLogin user in the Master database) I have now reached my next hurdle.

As part of my general Jiwa support I often turn to SQL Profiler to capture SQL commands as they hit the database. Very handy to see what is happening in the background.

Unfortunately SQL Profiler no longer exists for Azure databases. A few days of research to find Profiler alternatives or workarounds have proved fruitless so thought I'd turn here to see if you know of anything I can do to capture commands as they hit the db? I've gone through Sessions and Extended Events but for various reasons they don't work for me, in particular it seems it will only save to a file, but that file is on https storage, not the local file system and my permissions in this particular Azure environment do not give me that kind of access.
I am also aware that the Azure web portal may give me that information via the monitoring facility, but once again I have not been given access to the Azure web management portal.
I am basically stuck with using SQL Server Management Studio.

For now my particular interest is to find out why some reports don't work - they come back with various database errors. I need to run the identical command in a SQL query window to see what errors are returned within SQL. But I need to capture that command!

Cheers
User avatar
DannyC
Senpai
Senpai
 
Posts: 635
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 29

Re: Jiwa 7 and Azure

Postby DannyC » Fri Jul 08, 2016 12:18 pm

To answer my own question, I have here the reward for my week-long quest.

For anyone else who needs to capture SQL statements as they hit the Azure database, this will do it.

1. Look up information on Extended Events. This link is good.
https://msdn.microsoft.com/en-us/library/mt733217.aspx
a) in SQL Server Management Studio (I used SSMS from SQL Server 2016 because it's more compliant with Azure), expand the database tree.
b) Extended Events -> New Session

2. Give the session a useful name.
a) No need to tick any of the boxes
b) Events page, these will be the events the session will capture, so just find the events starting with 'sql_'. I only selected sql_batch_completed and sql_statement_completed. Move them into the right-side pane.
c) Click the Configure button, and select sql_text and any other properties you may need such as database name, hostname etc.
d) Data Storage page. Select ring_buffer.
e) Advanced page. Select 'No event loss (not recommended)'. I select this one as I want to capture every event as it comes to the database just in case the session happens to miss the actual command I need.
f) Set maximum dispatch latency to 1 second.
g) Click OK to save the session.

3. a) Right-click the session and Start. You will now be capturing events
b) In Jiwa, do what you need to do.
c) Keep the session running, in Management Studio, expand the Session tree and you will see a new entry package.ring_buffer. Double click it, and then click the link which displays.
d) The results are displayed in XML, not not too user friendly to read, but you should be able to locate the SQL text you're after.
e) If you're happy you've found what you need, stop the session.

Knock yourself out. If anyone knows of third party utilities which do much the same but display the results in a nice grid or table, let us know.
User avatar
DannyC
Senpai
Senpai
 
Posts: 635
Joined: Fri Mar 22, 2013 12:23 pm
Topics Solved: 29

Re: Jiwa 7 and Azure

Postby pricerc » Wed Nov 13, 2019 3:42 pm

Any chance of an update to the instructions for Azure in Late 2019?

Microsoft in their inimitable fashion have changed everything, and when I finally got to have a go, it didn't go as smoothly as I was hoping.

e.g. I (think) I found the "Windows Server Remote Desktop Session Host" (Server 2016 version), but when I got to step 3; I realised that said PowerShell script didn't exist. So I don't know if I picked the wrong template, or if I did something else wrong.

It would also be interesting to know if we should be using WVD instead; e.g. using Windows 10 multi-session as an O/S instead of Windows Server with RDS.
/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: Jiwa 7 and Azure

Postby Mike.Sheen » Wed Nov 13, 2019 3:55 pm

pricerc wrote:Any chance of an update to the instructions for Azure in Late 2019?


Oh, yes it's completely different now.

Those instructions were for Azure RemoteApp which was discontinued a few years ago - you now just use Azure as an IaaS platform and create a VM with the Windows Server 2019 Datacentre edition, and add the RDS role.

There are no step-by-step guides as it varies so much based on whether you're connecting the RDS in Azure to an on-premise DC via a VPN, On-prem directory password hash syncing, Azure ADDS, or a DC as a VM in Azure also.

Then there's concerns around the virtual network - is this machine going to be one of many which will need access to each other, and so on.

But on the whole it's a LOT simpler now - a lot less steps but a lot more branches on the decision tree.

So your first decision you need to make is how is the RDS server VM you create going to connect to a DC? You should know that BEFORE you provision the VM because the network subnets and the like are impacted by this. If you've not done this before, be prepared to make mistakes and have to nuke all the resources and start over a few times.
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


Return to Technical and or Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron