Dynamics GP on Microsoft Azure – Part 2
As I sat up til nearly 1:00am 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 actually upload the vhd from my Hyper-V machine, that already works and is configured the way I want, to Azure. I read some blogs about it, I prepped my machine, I installed Azure Powershell on my laptop and hooked it up to my subscription and all was working well. Then I put the command in to “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.”. (from the very funny and talented Ileana Grimm, great stuff!)
Plan B in this case was just 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., if you follow 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 really 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 your Azure VM
Rating: Easy Peasy
Log into your Azure portal and by default you’ll view all items when you log in:
You’ll notice that my Cloud Service and my Virtual machine are stopped. Stopping or starting your VM will also stop or start your 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 24×7 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 you would if you were 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 own terms!
Clicking Connect prompts you first to open or save the RDP connection, so look on the bottom of your screen for the prompt. I’ve actually 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 don’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 you set up when you configured the machine 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 you 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, you can see your local drives to be able to use Windows Explorer while you’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 and set a password for the ‘sa’ user, and enable the ‘sa’ account (it’s disabled by default)
Install Dynamics GP2015
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 you 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, all was good.
- I launched GP Utilities and received an error as it went to validate the versions… unfortunately I didn’t capture a screen shot. One of those DBMS messages. You know the ones, it looks like a score, and you’re on the losing side getting your butt kicked? 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 really 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 screen shot 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 of 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 I think if you 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 that I can count where you are slightly FUBAR’d if you 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
You see why I’m smacking my head? When you’re not comparing the collations on different databases to see them side by side, you see either of these by themselves and it’s close enough to what you’re expecting that you 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.
Reading a little more on the subject, the difference is if you’re installing on an EN-US server, you are getting SQL collation options by default but if you install on any other kind of regional settings, like EN-CA, you are getting Windows collation options by default (SQL_ or no SQL_).
What is *really* interesting is if you read the System Requirements for any version of Dynamics GP, it 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 and go to a plain Windows Server VM and install SQL from scratch myself.
- Abandon my non-Fabrikam database and 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 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 my 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 can select the proper collation during install and move on from there. The SQL install software is on the Azure VM for you already in case you need to add more features etc., so it’s not like I have to find my own 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 shows a SQL_ collation.
Changing your 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 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…
- Click on Formats, and my format said English (Canada) already so again, a little confused.
- Click on Administrative, and under Language for non-Unicode programs it was still set at English (US) so I changed that, and it appears that is the one that matters to the SQL install routine.
- After doing all of that and re-running the SQL Server 2012 setup routine, the collation matches what I need it to be. Yay!
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 screen shots and document it to blog it definitely accounts for a few of those hours so I can’t really 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 and you’re working. At least in my scenario. If you are based in the US or don’t care about using a EN-US server (don’t need to copy anything over that might have a different collation) then you’re 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 you can google nearly anything and take a reasonable sampling of the results you get back to find what works for you. 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 you have 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, for those of you, like myself, were afraid to get into this not having 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 you 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 24×7 usage (or in use, accessible) and if you’re using this for production, do your on-prem vs cloud comparisons 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.
So far what I’ve learned:
- transferring data into Azure doesn’t cost (or hasn’t cost me yet, even though I copied in over 7gb 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. Obviously if you have storage, you have storage and it’s not something you turn “off” to reduce billings.
- Computing hours I need to monitor, this is where I assume the bulk of the costs would be – actually 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 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 screen shots like the one above to show what this is costing and how much I’ve actually “used” the machines in that time, to give you 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!
*This post is locked for comments