Over this last weekend, we finally pulled the trigger on the iMIS 15 upgrade. It went very well. All of the pre-testing and documenting steps worked as planned and I had the entire site upgraded in just over six hours. I ran the upgrades all the way to 15.1.2.6369.

We didn't get to do the SQL 2008 upgrade like we wanted, but that may have been for the best. I was all ready to do the SQL 2008 upgrade until our eCommerce vendor said they would not perform their part of the upgrade on 2008 because it was not supported by ASI and therefore not by them either. They offered to install on SQL 2005, but I had not tested that and felt that was just a half-step, so we are still running SQL 2000 on the backend.

That just means that I have more upgrades to do later to get the backend more up to date.

I'm currently waiting for the eCommerce vendor to complete their upgrades so the I can finally complete this upgrade. This phase at least.

Brian

We are still moving forward with the upgrade. We have been working with ASI and have a much more favorable licensing fee than before. We also got really tough on who needed iMIS access and got the license count down.

With that in mind, I got a group of my full license users around to do some testing. The initial reaction was, "Did anything change, it looks the same." To me that's good and bad. I was hoping that the new version would be redesigned, front-end and back-end, but alas, it was not. The good of it is that the users are not balking about doing the upgrade. Although they still question why until they started using the system.

Many of the features they use today in 10, are much improved, speed wise in 15. Some items are still broken or require the same goofy way to get it done. But for the most part, the list I got from them was nice to haves, not, this does not work or I can't find a function I use in 10. So that is good news.

Next up is getting our ISGWeb test environment upgraded and making sure our eCommerce still works after the upgrade.

If you need to run an upgrade script, say 15.1.2.5184 or 15.1.2.5454, on SQL 2008 after the database has been converted to 15.1.2.4260 there are a couple of things you need to do.

1) You need to install the SQL 2005 Backward Compatibility Components. You can download from http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en. This only needs to be done one time.

2) Before you click 'Begin Update' in the iMIS DB Upgrader, you need to edit three files each time you run an update.

    - Go to C:Program FilesASIiMIS15NetiMIS15DBUpgrade

    - Replace the osql line in IMIS154Patch02.cmd, IMIS155Patch.cmd and IMIS156Update.cmd with the following:

    osql -t -S %SERVERNAME% -U %USERNAME% -P %PASSWORD% -d %DBNAME% -n -b -r -h-1 -Q "EXIT(SELECT LEFT(CONVERT(NVARCHAR(10), SERVERPROPERTY('ProductVersion')), CHARINDEX('.', CONVERT(NVARCHAR(10), SERVERPROPERTY('ProductVersion'))) - 1) + '0')" > NUL

    - Click 'Begin Update'

After the files are changed, the upgrade will run as expected. For whatever reason the osql command in those three files does not add the product sub-version. So it returns the SQL version as 10 instead of 100 and the scripts look for a version greater than 80. For those that don't know 80 is SQL 2000, 90 is SQL 2005 and 100 is SQL 2008.

Just another bump in the road.

I've been pushing ahead with my testing of iMIS 15 while we did a survey of our users to find out about our licensing needs. We completed the survey of our staff and compiled the numbers. Once I had that done I contacted ASI to find out the damage. It tooks them a couple of days to get back to me and then...the train wreck.

Holy cow. I had no idea that it was going to cost so much. The resulting figure is a 5 digit number. Before the decimal point! We don't have that kind of money to spend in this fiscal year, next or maybe ever.

Aside from getting on current underlying technology (SQL 2008, Reporting Services) and getting away from a mainstream unsupported application (iMIS 10.6). I have not found a killer feature to sell this upgrade. Everything I have looked at, so far, is the same look and feel. Plus, I now have to license an additional server if I don't want to run IIS on my SQL Server. Which I don't.

The licensing from ASI does include enough full users based on our current concurrent user count. I used the "extra" licenses that they provide as part of the upgrade to obtain a number of casual user licenses. My task now is to find out if we can live within the full and casual counts to keep from having to pay for additional licenses.

I'm hoping some, or all, of my view only users can be satified with reports to find the info they need instead of requiring a client user license. Or maybe ASI will come back with a price we can work with.

Time will tell.

It's amazing that it has a month since my last post. Oh, how time flies. I've covered a lot of ground, I've just been lax on writting it up. This gives me a new appreciation for all of those blog posts I read on a daily basis. It takes a bit of time to maintain that kind of pace.

For my upgrade testing I set up three virtual machines on my laptop. All three run Windows Server 2003, but all have different versions of SQL Server. One is SQL 2000, one is SQL 2005 and the last is SQL 2008.

I really don't plan on using the SQL 2005 VM, but I figured if 2008 really did not work out I'd be ready.

For my first foray into iMIS 15, I decided to just do a clean install and give it look. I tried installing 15.1.1.2998 on the SQL 2008 VM. No dice, the install failed as soon as it detected the SQL version. I looked on the ASI site and found 15.1.2.4260 and tried the install again. The install worked with no issues.

I kicked the clean install around for a day or so and decide while it was nice to see it running it would be better to see our data.

I won't bore you with all of the details of the trial and failures I've encountered. I must have run the upgrade process around 30 times to this point trying different approaches to get my database to version 15 running on SQL 2008.

The quick answer is, yes, you can run iMIS 15 on SQL 2008, you just can't get there directly. It's got to be done in stages. That I will cover below is detail.

Oh yeah, this is going to be a long post. If I could figure a good way to split it I would, but nothing comes to mind.

I'm not going to cover all of the upgrade steps, you can find those at http://docs.imis.com/15.1/. I'm just going to go over the ones which I found to be the most important. You also need to have an application (web) server to run iMIS along with the database server. If possible do not run the application on the same box as SQL. I'm a DBA at heart and don't like anything but SQL running on my SQL servers.

Before upgrading to 15.1.2.4260

• Run view metadata script from the upgrade docs
• Correct any schema issues (I found views and tables that needed to be removed. Of course that was only after failed upgrades. Oh, joy)
• Run DBCC CHECKDB
• Set database recovery mode to simple
• Apply 15.x license in iMIS 10.x System Setup. Do not change the version number!
• Change administrative user authorization levels from 9 to 8. Level 9 does not exist in 15 and the users are set up with no permissions after the upgrade if left at 9.
• Make sure you know the password for the MANAGER user. Change it if you don't. You will not be able to log in after the upgrade if you don't know it.
• Back up the database
• Start the install. The database upgrade starts automatically.

Check the docs for install options. Select to use an existing database when asked. Then sit back and wait. My upgrades ran in just under 5 hours. There are 21 steps in the database upgrade process. It upgrades to each iMIS release version along the way (10.6 to 15.0, 15.0 to 15.0.1, etc.).

If there we no errors, or at least no fatal errors, during the upgrade, fire up the iMIS Desktop app and log in as MANAGER. Just a note, MANAGER!SA no longer exists, you will just use MANAGER.

Before upgrading to 15.1.2.5454

• Run view metadata script from the upgrade docs. Yes again. I had a view that broke after the initial upgrade. Apparently it was using a field that is no longer available.
• Fix/delete any broken views.
• Start the install. The database upgrade waits for you to enter the sa password before continuing.
• If there we no errors, or at least no fatal errors, during the upgrade, fire up the iMIS Desktop app and log in as MANAGER.

The database can now be moved to SQL 2008. My database server has SQL 2000 as the default instance and SQL 2008 as a named instance. Unfortunately I have not been able to get 15 to run in that configuration. If SQL 2008 is the default instance on the server, things seem to work fine.

After the database is running on SQL 2008 you will need to run the iMIS DB Repair Utility. Before you can run the repair, you need to install the SQL 2005 Backward Compatibility Tools. They can be download from here. After the tools are installed, start the DB Repair Utility, log in and run Prepare Database. This will fix the SQL logins for your users.

At this point iMIS 15 should be running fine on SQL 2008. At least mine is in the test environment.

Till next time.

Our current environment:

Database server

• Windows Server 2003 Enterprise SP2
• 4 - 3.40GHz Xeon CPU
• 8 GB RAM
• OS HDD 25 GB RAID 1 (2 drives)
• SQL HDD 265 GB RAID 10 (4 drives)
• SQL 2000 SP4 (iMIS 10.6.30.17)
• SQL 2008 SP1 (SharePoint 2007)

Web/SharePoint server

• Windows Server 2003 Standard SP2
• 4 - 3.40GHz Xeon CPU
• 4 GB RAM
• OS 75 GB, Web 500 GB RAID 5 (6 drives)
• IIS 7
• SharePoint 2007 SP2
• ISGWeb 2.0

Potential Users

• 75 internal iMIS users
• 36k web/SharePoint users

After we upgrade to iMIS 15, the database server will only be running SQL 2008. I know that ASI does not support SQL 2008 yet, but we are making the jump anyway. Otherwise there will be no changes to our existing servers.

I will be adding an internal web server for hosting iMIS, Reporting Services and our internal SharePoint instance (future project). Not sure what I'm going to find for that server yet, I have several possibilities lined up.

We are looking to maintain our ISGWeb implementation for outside member access. The public access part of iMIS 15 will be off to start. I'll look at it later and decide how to proceed with it, if at all.

I have a laundry list of items to review with the 15 upgrade. Will all of our reports work, what about all the ad-hoc Access databases, will SharePoint still work correctly, etc., etc. Seems like I come up with more items every day.

Oh well, I guess I won't really know until I get me database upgraded and start testing.

In my next post I will catch up to what is currently happening in my testing.

Till next time.

In school and other professional training classes, they always emphasize that you start with a plan. Document, flowchart, diagram what it is you are going to do. Me, I always want to just dive in write some code, start creating a database and get moving. I know what my plan is, get from here to there.

Unfortunately that type of approach does not give anyone, yourself included, any clue as to how or when you are going to get there. My past experience has been that no matter how much time you spend on the plan and estimate, it will still be wrong. Something will not work as expected and delays will occur. 

I also like to see working examples when I try to estimate completions. The working examples can be other scripts, databases, sites, applications that are similar, but not related to the project at hand. Part of my nature is explain what you want and show me something that works and I'm good to go.

With this iMIS 15 upgrade, my boss has asked from the start, when are we going to have user acceptance testing. Now I know he has to schedule with the other directors to get time allocated in each department for testing. The problem I have is that I really don't know how to answer his question. I can read the documentation and maybe estimate something, but I'll probably be wrong.

So my initial thought is to get iMIS 15 running somewhere so I can kick the tires. Then I can see what is different and get an idea for when we can proceed.

In my next post I'll start detailing where we are and where we want to go and how I'm testing to get us there.

Before I started working for the State Bar of Michigan almost two years ago, I was a DBA/developer for a consulting firm doing custom training management web sites for automotive manufactures in Michigan. My main focus was the database. Schema, data access, performance, that was what I did. I read, and still read, many blogs in the SQL Server space. I’ve learned a lot from those blogs and always wanted to give back. But for many reasons, it never happened.

I work at the Bar as a DBA/developer, but my focus has changed. I’m no longer in a fully custom, total control world. My job consists of managing iMIS 10.6, SharePoint 2007 and our eCommerce (ISGWeb/iBO) solution. I’m now working in an environment that I cannot just tweak the database directly. Change something without using the app UI and you potentially break the product. So my giving back to the SQL community in general now seems extremely limited.

I had not worked with iMIS prior to my job at the Bar, so I went out looking for the blogs that helped me so much with SQL Server. I found a few, but the content I was looking for is sparse. Maybe I just have not found the pot-of-gold yet. Maybe it just does not exist.

Then I found the NiUG Member Community and the ability to have my own blog. Here is a way to give back and potentially help others with the issues that have been frustrating me.

Anyway, I am currently investigating our upgrade from iMIS 10.6 to 15. I’ll be posting periodically how the process is going. This to me provides two benefits, one it makes me document the process and two, others can learn from my effort and potential pain. I'll also post about other topics as they arise and I think there may be some interest in the community.

Till next time.