SSMA and Migration Path Planning (1 Viewer)

[Acme]

Hi All,

Last night I tested migrating my backend of a split access 2013 database to an SQL database on the Azure platform.

Remember that saying, I only wish I knew then what I know now:

• Trying to be clever, I named my timestamp fields When in Access, but this is reserved in SQL (ding)
• Trying to enforce quality, I established more than a few fields in the tables as lookup fields, and lookup fields are not allowed in SQL (ding) - this means that in order to have lookup fields in forms, I need to probably create a query for every lookup field I have in order to deploy on Azure.

I was wondering if we should add a thread for forward thinking database design for potential sql migration considerations. Anyone agree?

Also, does anyone have any experience with SSMA and how to accurately debug these kinds of issues? Immediately, I am thinking I will need to rename some fields, and this potentially means updating a whole lot of queries with new field names. Also, I am looking at basically redesigning a bunch of form fields with queries. And I have not read the entire SSMA metadata report yet. So, part of me thinks, well, maybe I would be better off starting from scratch with a new back end for Azure, and redesigning an entire front end for that instance.

Thoughts?

 

[spikepl]

I hate breaking this to you, but you have rediscovered the wheel.

The curse of lookups in tables is a well known invention of Beelzebub (the one residing in Redmond) - scan through the Tables forum, and many threads advise people not to use it.http://access.mvps.org/access/lookupfields.htm

Naming conventions is something you discover with experience and stop using names that could possibly be part of the programming language itself. http://allenbrowne.com/AppIssueBadWord.html

To cpomplete the migration, I'd advise you to do what is necessary to keep the SSMA-thing happy - change your db until you can mugrate with no comments, becaue you can keep testing your unmigrated but changed version vs the original, without adding a new applicaiton into the equation. For renaming (or searches for instances of specific words) , download the free V-tools.

 

[Acme]

Nothing like being fashionably late.

I will read through the posts. My concerns are enforcing referential integrity which I suppose will need to be done programmatically.

 

[Acme]

Ok, I am almost there, but need advice on the denial of multi-value fields.

Situation is that I have a table called Periods which include period names, start dates and end dates. In access, I was able to select multiple values of these periods for a foreign table called Period_Groups. So, a Period_Group could have N periods and a Period could belong to N Period_Groups. Now, SQL will not allow this. My hunch is I will need to create a middle table that includes N rows for each group the period belongs too. Is that the way to do this?

Thanks in advance

 

[ButtonMoon]

Just a general comment but I believe the most important lesson to learn about migration is this: the time to migrate a database to Azure or any other target platform is at the beginning of your development project, never near the end.

It makes no sense to develop on one platform if you actually intend to migrate anyway - you will only end up doing more work that way. No migration is ever likely to be 100% painless so the sooner you introduce the desired DBMS into your development and testing the better.

 

[Acme]

I am delinquent in responding to this thread but wanted to reply, albeit late.

ButtonMoon, I do not disagree with you. I had not planned to deliver a cloud solution. As such, I had not set out to prepare a database that was normalized for SQL. However, when the bleeding heart virus appeared on the scene, I began to realize that Microsoft Cloud solutions offers an advantage I had not foreseen, in that it might actually be more secure than customer databases.

It was my good fortune that I only had to create a handful of junction tables, and modify a few validation fields in order to normalize my database for Azure/SQL. And with a few weeks of rewiring some front end logic, and adding scripts for connecting and account creation, was able to migrate to the cloud in about 5 weeks. Works like a charm. Very happy to have done it.

So, for others, who experience a change of heart regarding their target backend databases, do not be discouraged. The nice thing about Microsoft is that they have seeded more than enough helpful resources out there to guide you through it, including this great community.