As I sat up til nearly 1:00 am last night (er, this morning) playing with the new Azure VM, I was thinking "This is going SO smoothly!". I should have known it wasn't going to be that easy. 😄

See yesterday's "Part 1" post for the introduction to this journey I'm on, which covers why I'm doing this, going from a new subscription to provisioning a SQL server VM. At the end of yesterday's post, I stopped at "I've provisioned my VM". After that, I tried some things and ran into a problem.

My original plan

At first, before what I documented yesterday, I was going to try to upload the .vhd file from my Hyper-V machine, which already works and is configured the way I want, to Azure. I read some blogs about it, prepped my machine, installed Azure Powershell on my laptop and hooked it up to my subscription and all was working well. Then I put the command into "Add-AzureVHD"… well it said it was going to be 2.8 days to upload that! Uh…. ok, maybe that isn't the best plan!

Plan B

I have this magnet on my fridge, which sums up a lot of things quite nicely!

Plan B better be good magnet.
"Plan B better be good.". (from the very funny and talented Ileana Grimm, great stuff!)

Plan B in this case was just to set up a machine and install GP etc. from scratch like setting up any other new machine. What I wrote yesterday was plan B in action. Here are some things I learned yesterday, some things I thought would be simple were hard, and some things I thought would be hard were easy. Go figure!

My plan more specifically is this:

  • Treat this VM like I was moving GP2010 to a new server, copying SQL backups over, modified reports and the various script results etc., following KB878449 for moving GP.
  • Install GP2015 (skip installing GP2010, just move the server as if it was there before upgrading). I was going to install GP2010 but I've decided I don't need to do that I don't think, so I could have installed a SQL 2014 VM after all…
  • Run through the upgrade process.

How to Connect to an Azure VM

Rating: Easy Peasy

Log into the Azure portal and by default, the view contains all items:

View of all items in my Azure portal.
View of all items in my Azure portal.

You'll notice that my Cloud Service and my Virtual machine are stopped. Stopping or starting a VM will also stop or start the Cloud Service, I didn't shut that down myself. I stop my machine when I'm done actively using it since I don't need 24x7 access (and want to minimize the costs). It's not a big deal for me to start it when I need it, so I click on my Virtual Machine and click Start at the bottom of the screen, just like managing Hyper-V machines locally.

Then, once it's started, click Connect at the bottom of the screen to open an RDP session. Note: I wait until its status is "Running", not "Starting (Deallocated)" just to make sure it's fully ready for me to use. The "Connect" option is there under both statuses but my assumption is it's still doing something and I should let it wake up on its terms! 😄

Clicking Connect prompts the user first to open or save the RDP connection, so look at the bottom of the screen for the prompt. I've saved my connection to an RDP folder I have with other clients' RDP connections that I use frequently, so in theory, if I left my machine running in Azure all the time, I wouldn't have to log into the portal, I could just connect from my laptop like I would with any other machine I have RDP access to.

Log in with the administrative user that was set up when the machine was configured in the first place (if this is the first time using it). The first time it will be doing some setup things like any other "first time login" to a new server.

Copying Files to Azure VM

Rating: Easy peasy

Copy and Paste. Seriously, copy and freakin' paste!

Now there are likely situations where this might not work but it was that easy for me. The only caveat was I tried copying multiple things and it seems to only like to copy one "batch" of things at a time. Example: I was copying SQL backups (3 in total) so I copied all 3 and pasted and it started copying those over. Then I was going to copy in the GP2015 install files and hotfix and reg keys etc., but it wouldn't work while the other files were still being copied. Normally in Windows, users can copy/paste other things too and it will copy that while the other stuff is copying. Azure doesn't appear to do that so I had to wait for the 3 .bak files to copy over then copy/paste the additional items. Note to self: copy and paste a bunch of things at once to avoid waiting for something to finish first.

Normally with RDP, users can see their local drives to be able to use Windows Explorer while they're inside the VM but I can't. This could be because I haven't connected my network with the Virtual Network in Azure, I don't know. Copy and paste works for me for now, even with big files like SQL backups, so that's good enough.

SQL Configuration Changes

The next step was to change the installed SQL instance to Mixed Mode Authentication. It was installed in Windows Authentication mode by default.

  • Go into the properties of the SQL instance in SQL Server Management Studio and change the authentication method.
  • Stop and Restart the SQL services for this change to take effect.
  • Go back into SSMS, set a password for the 'sa' user, and enable the 'sa' account (it's disabled by default).

Install Dynamics GP 2015

I won't go into too much detail on this part, since the point of these posts is about the learning curve with Azure… installing GP 2015 was "normal" up until I went to run GP Utilities to start the upgrade process, but the issue I ran into had nothing to do with Azure.

  • First I checked that the SQL Server was configured the same as the default I use - dictionary order, case insensitive etc., by running the sp_helpsort command. All looked good. Or so I thought at the time… this is the foreshadowing moment I hated hearing about in high school English class.
  • I restored my 3 databases (TWO, DYNAMICS plus one more database of my own for GP) and all was fine. No issues.
  • I ran the Dynamics GP 2015 setup and that too was fine, everything was normal. I had selected the Canadian install so I saw all of my normal install options, and all was good.
  • I launched GP Utilities and received an error as it went to validate the versions… unfortunately, I didn't capture a screenshot. One of those DBMS messages that looks like a score. Yeah, one of those…
  • I knew there was a hotfix out for GP and I recall seeing something about needing the hotfix to upgrade from the build of GP2010 I was on, so I didn't pay attention to the error. It was more of a head-smack moment where I thought I had just forgotten to install the hotfix first before launching GP Utilities. So I installed that first.
  • Next, I launched GP Utilities again and I got the same message. No screenshot but the text was "The stored procedure verifyVersionInformation() of form duSQLInstall Pass Through SQL returned the following results: DBMS: 468, Microsoft Dynamics GP: 0". Huh.

I googled the error and found this KB article which said "This problem occurs if the collations and the sort orders of the DYNAMICS database and the company database do not match the collations and the sort orders of the Microsoft SQL Server system databases.". Now I was stumped, I thought I checked that. When I ran the sp_helpdb command, it became more obvious that there was a slight difference that I didn't notice at first glance.

Differences between Canadian and US SQL installs: different collations!

Who knew (eh?)! *Smacking my head*… this will only affect people who are trying to copy over SQL databases from a Canadian install of SQL Server to an Azure default install SQL VM… and it is likely not just Canada-US, but US-Anybody else.  Honestly, and I love my American "cousins", there are more times like this than I can count where we are slightly FUBAR'd if we aren't simply working with US installs. And it's a pain in the butt. A few "FFS" were uttered last night at this point…

  • The collation on my 3 databases, installed with Canadian regional settings on my server locally is: Latin1_General_CI_AS
  • The collation on the server, installed on a server in Azure with US regional settings is: SQL_Latin1_General_CP1_CI_AS

This is why I'm smacking my head. When not comparing the collations on different databases to see them side by side, it's close enough to what is expected that we don't realize it's different. And they are both the same thing more or less, except they are not. So GP can't upgrade these databases, without the collation matching.

What is *really* interesting is the System Requirements for any version of Dynamics GP specifically says SQL_Latin1_General_CP1_CI_AS collation (or Binary) as the "Supported" collation which means technically, based on what I'm seeing, everyone installing on servers other than EN-US could be running "unsupported"!  Interesting…

Options

So my options are, I think:

  • Uninstall and reinstall SQL Server with the right collation on the Azure VM.
  • Change the collation of my databases to the "US" one to match the server collation.
  • Abandon the pre-installed SQL VM, go to a plain Windows Server VM and install SQL from scratch myself.
  • Abandon my non-Fabrikam database, install GP2015 fresh, and convert over what I want to keep (and there are things on my GP2010 I want to keep).
  • Give up and start the weekend now. 😄

Changing the collation sounds scary. It's not a simple click-and-change thing from what I am reading. Abandoning my GP2010 database and recreating it from scratch is also not desirable. Honestly, my thought is if I decide to stop using Azure, and want to download my data to use locally again, will I have the collation problem in reverse if I simply copy a backup of my Azure SQL data in the future? All of the local machines I create are installed with Canadian settings, not US ones so in theory this will always be an issue…

I went with uninstalling SQL and reinstalling with the hope that I could select the proper collation during installation and move on from there. The SQL install software is on the Azure VM in case there is a need to add more features etc., so it's not like I have to find my copy or anything.

As I write this, I have uninstalled the default instance of SQL 2012 on the Azure VM, rebooted, started installing a new instance, crashed my VM for some unknown reason (related or not I have no idea!) and am back in again trying to reinstall SQL 2012. I've managed to get through this without crashing (so I think that was just a bad coincidence earlier today), except the Server Configuration settings still show a SQL_ collation.

Changing Regional Settings (if need be) to install a Windows collation in SQL Server

When I first ran through this re-install and got to the Server Configuration screen in the SQL Server 2012 Setup process, the Collation tab still was giving me SQL collations, not Windows collations, with no option to choose the other. So I had to stop that and go back to the Control Panel to see what I was missing. Here's what I changed and now SQL sees the right default collation which is music to my ears!

  • Go to Control Panel and the Clock, Language and Region settings
  • Under Region, choose Change Location. My Location already said Canada, so I was initially confused…
Regional Settings home location field.
Regional Settings home location field.
  • Click on Formats, and my format said English (Canada) already so again, a little confused.
Regional Formats window.
Regional Formats window.
  • Click on Administrative, and under Language for non-Unicode programs it was still set to English (US) so I changed that, and it appears that is the one that matters to the SQL install routine.
Region Administrative tab, changing the system locale.
Region Administrative tab, changing the system locale.
  • After doing all of that and re-running the SQL Server 2012 setup routine, the collation matches what I need it to be. Yay!
SQL Server 2012 Setup collation setting window.
SQL Server 2012 Setup collation setting window.

 Rating Ease of Use and Cost so far

So far, this has taken me several hours of effort to get right. Granted, stopping during the process to take screenshots and document them to blog accounts for a few of those hours so I can't count that. However, if I read one more post about "click on what VM you want and it's provisioned in 3 minutes", I might just throw up a little… it's not *super* complicated, but it's also not 3 minutes to get it working. At least in my scenario. Those who are based in the US or don't care about using an EN-US server (don't need to copy anything over that might have a different collation) will be fine… it might be an hour or two to get done and GP installed and something operating. But for the rest of us, it might take a little more work.

Is it worth it? Yes… I can't help but love learning about new things, as frustrating as it can be at times. Thankfully there are a TON of Azure blogs out there that one can Google nearly anything and take a reasonable sampling of the results to find what works. I just haven't found anything that gets into this level of building an Azure VM from scratch so I'm hoping whoever reads this will find it useful.

Cost-wise, whatever I did yesterday cost me $3. Damn! LOL… since I'm using credits, there is a handy little Credit Status button at the top of my portal when I log in that I can view my "balance" and get more details. Anyone should be able to get into this overview though, I just don't know if there is a handy status button for Pay As You Go versions or not.

Here's a good example of what the costs start to shake out as, like myself, who were afraid to get into this not have a good idea of how much this might cost. It's all well and good for the pricing info on Azure to show machine costs as estimates but sometimes that also freaks me out. It's going to cost $xxx a month for a server? At least that was my initial impression costing out some things… but that's based on 24x7 usage (or in-use, accessible) and for those using this for production, do an on-prem vs cloud comparison to see what makes sense. For small partners like me who need an occasional machine for testing or development, I can turn it off to reduce costs pretty easily most of the month.

The cost summary so far is $106.83.
The cost summary so far is $106.83.

So far what I've learned:

  • Transferring data into Azure doesn't cost (or hasn't cost me yet, even though I copied over 7 GB of data yesterday.
  • Transferring out does cost (I don't even recall what I copied out, or whether this is measuring something else entirely).
  • Storage has a cost, but I'll have to monitor how this works over the coming days. Storage is not something one can turn "off" to reduce billings.
  • Computing hours I need to monitor, this is where I assume the bulk of the costs would be - running machines. I see two lines here but initially, I had created a blank VM, not a SQL Server VM so the one compute hours that doesn't say SQL could be that. Or it could be the cost of running the VM apart from the cost of the SQL server. Who knows…
  • The Standard IO I'm not sure about but it's related to storage obviously and time will tell what that looks like.

I'll continue posting more as I learn and I'll post more screenshots like the one above to show what this is costing and how much I've actually "used" the machines in that time, to give an idea of what it costs to do this trial. At the rates above, and the time I anticipate using this, I can envision that my costs would be very minimal, like under $50 a month, and if I'm using it more often for a project, in an ideal world I'm building the incremental cost of that into my project rates so it's not pure admin cost.

Well, that's all for now… I won't be posting every day most likely, just as I run into things I wish I knew before!