SSMA_TimeStamp field never gets updated

[GPGeorge]

I really don't like that it doesn't transfer the hidden indexes Access adds for foreign keys. If that is deliberate, they should warn us. If it is a bug, they should fix it.

Pat. Can I get a copy of an accdb BE that has hidden Access indexes so I can see what the current version of SSMA does with it? If so, can you call out the specific indexes so I can focus on them?

I've looked over the reports; they seem pretty thorough, but I could be missing something.

 

[Pat Hartman]

To get Access to add a hidden index, just create two tables. Then create a relationship and enforce RI. do NOT put an index on the FK. If you look at the indexes dialog, you will only see the PK for the child table. However, if you look at the indexes using code or the built in documenter report, you see it.

Design view = Only the PK index.

Documentation report shows the second index on Person which I did not create and which does not show on the field or in the indexes dialog.

 

[Pat Hartman]

Here's the complete database. I removed my data. You might find it useful

 

[GPGeorge]

Thanks, I am obviously lazy, but I also want to test with an accdb which is known to have hidden indexes to be sure I'm not just overlooking something.

 

[GPGeorge]

Here's the complete database. I removed my data. You might find it useful

I verified that the current version still doesn't automatically create an index on the field you identified.

That puts my mind to rest on that point.

That said, it appears from the documentation I can find, that SQL Server itself does not automatically index Foreign Keys.

One reference suggested that it is the responsibility of the DBA to add indexes manually, if and when they are deemed appropriate.

That leaves the question of why the SSMA tool doesn't include these hidden indexes. I guess we should raise the point in the email list.

 

[GPGeorge]

This is quirky. I want to start over fresh in the morning.

 

[ions]

Typically not. The field value is not updated client-side.

Thanks Tom. Makes Sense.

 

[Pat Hartman]

That leaves the question of why the SSMA tool doesn't include these hidden indexes. I guess we should raise the point in the email list.

I raised it when I discovered the problem a few months ago. No one was interested. I'm guessing that they don't often convert from ACE to SQL Server or they would be more concerned. Maybe they've always created their own indexes and never noticed that the ACE db had two indexes on the same column. Perhaps you have more juice. It would be good to know if this is intentional or a bug at least. It would also be good to know how bad having two identical indexes on the same column is.

I haven't converted a database in a very long time so I don't know how long this has been a "feature". I always used the upsizing wizard as long as it was available and only switched to SSMA when MS took away the tool that actually worked.

I also noticed that SSMA always creates clustered indexes for the PK. I think it should give us an option. Clustered indexes are fine if you have autonumber PKs since they never change and in most cases are inserted sequentially but if you are using something other than incremental, then you probably don't want a clustered index and if your PK is not an autonumber, you may also not necessarily want a clustered index.

 

[GPGeorge]

What I'm looking into now is how hard it is going to be to make post-migration adjustments. SSMA does generate the T-SQL scripts that create the database and tables during the migration. I haven't found a way yet to edit those scripts, but I think there should be.

Right now I'm thinking that the main reason hidden indexes on Foreign Key fields would not be created automatically is the assumption that SQL Server itself doesn't automatically do that. It would make sense to consider that the default if you look at it in a certain way. But there should be at least a way to tell SSMA to include them as an optional setting. New day, fresh coffee, I'll let you know what I see in a while.

Another part of the problem is that SSMA is not owned by the Access team; requests for changes to it have to go across the wall to whoever does own it.

 

[Pat Hartman]

Another part of the problem is that SSMA is not owned by the Access team; requests for changes to it have to go across the wall to whoever does own it.

Access never should have given up their upsizing wizard. At least it worked reliably.

There are many options in SSMA. Why not one for this if they're not going to do it automatically? My problem was that there was never a warning that these indexes were not being transferred. The scripts generated by my compare tool run to hundreds of lines. It is really hard to review them all manually. It wasn't until the day before I was supposed to send the script to the client DBA that I realized that all the FK indexes were missing. Luckily the db wasn't big so there were only 20 indexes I needed to add but I hate being blindsided by a tool I thought I could trust.

 

[spaLOGICng]

Access never should have given up their upsizing wizard. At least it worked reliably.

There are many options in SSMA. Why not one for this if they're not going to do it automatically? My problem was that there was never a warning that these indexes were not being transferred. The scripts generated by my compare tool run to hundreds of lines. It is really hard to review them all manually. It wasn't until the day before I was supposed to send the script to the client DBA that I realized that all the FK indexes were missing. Luckily the db wasn't big so there were only 20 indexes I needed to add but I hate being blindsided by a tool I thought I could trust.

Hi Pat,

Not sure what you are talking about. I just used the SSMA and default Settings to generate an SQL Server DB from the Northwind Developer Edition Access Database Template that is available on the Access Backstage. As you can see in this snippet, it does in fact create the Foreign Keys. Primary Keys, and Indexes.

Now, if you used SSMS Generate Scripts Feature, you need to go into Advanced Settings to include FK's and Indexes, Etc....

I never experienced the problem or issue that you have described, not with either SSMA or SSMS Generate Script Feature.

 

[GPGeorge]

Check for HIDDEN Indexes on Foreign Keys in Access on the NW tables and look at the corresponding tables in SQL Server. In my tests over the weekend, SSMA does a good job of any index you can see in the interface in the Index Property Sheet. It did not create the hidden index on a foreign key field, which Pat posted a screenshot of in her post. If I added a second manual index on the Foreign Key field in Access, that one did get migrated. Still SQL Server ended up with only that one, not the hidden one.

Because SQL Server does not automatically create indexes for Foreign Key fields, this behavior could be seen as internally consistent. And creating it in SQL Server is a simple script, so it's not a deal breaker in and of itself. The problem is that someone could migrate the accdb, not realizing this behavior exists, and fail to even LOOK for "missing" indexes after the migration.

The truth is that I had never noticed this and was, regrettably, skeptical until I was compelled to go beyond the surface.

I can see exposing the existence of the hidden Access instances and making it optional to give the user the chance to decide which behavior they want.

 

[spaLOGICng]

Check for HIDDEN Indexes on Foreign Keys in Access on the NW tables and look at the corresponding tables in SQL Server. In my tests over the weekend, SSMA does a good job of any index you can see in the interface in the Index Property Sheet. It did not create the hidden index on a foreign key field, which Pat posted a screenshot of in her post. If I added a second manual index on the Foreign Key field in Access, that one did get migrated. Still SQL Server ended up with only that one, not the hidden one.

Because SQL Server does not automatically create indexes for Foreign Key fields, this behavior could be seen as internally consistent. And creating it in SQL Server is a simple script, so it's not a deal breaker in and of itself. The problem is that someone could migrate the accdb, not realizing this behavior exists, and fail to even LOOK for "missing" indexes after the migration.

The truth is that I had never noticed this and was, regrettably, skeptical until I was compelled to go beyond the surface.

I can see exposing the existence of the hidden Access instances and making it optional to give the user the chance to decide which behavior they want.

I am not sure what a "Hidden Index" is. For the life of me, I have never heard that ever existed and when I do a search on it, all that comes up is related to MongoDb. Can you tell me how they are created and can you provide me a screenshot of a hidden index? Is this possibly what SSMS refers to as "Full-Text Indexes"?

 

[GPGeorge]

See Pat's post #22 explaining and illustrating.
They are regular indexes that exist on fields designated as Foreign Keys in Access. "Full-Text Indexes" are, as the name implies, on the entire contents of a VarChar(x) field.

 

[spaLOGICng]

See Pat's post #22 explaining and illustrating.
They are regular indexes that exist on fields designated as Foreign Keys in Access. "Full-Text Indexes" are, as the name implies, on the entire contents of a VarChar(x) field.

Understood. Not sure why the never dawned on me.


As you can see in this snippet, the Northwind DB that Iused SSMA to create the Tables on SQL Server, that it did fact create the Index on a Foreign Key. "EmployeePrivileges$EmployeesEmployeePrivileges" below is the Index and is the FK for the ID in the Employee Table...

 

[GPGeorge]

Understood. Not sure why the never dawned on me.


As you can see in this snippet, the Northwind DB that Iused SSMA to create the Tables on SQL Server, that it did fact create the Index on a Foreign Key. "EmployeePrivileges$EmployeesEmployeePrivileges" below is the Index and is the FK for the ID in the Employee Table...


View attachment 114090

The important factor is whether that index is visible in the accdb, in the Index property sheet for that table.

 

[Pat Hartman]

@GPGeorge What I don't understand is how you ended up with only the FK indexes you manually created in Northwind. I end up with two indexes. The hidden one and the one I create. That was how I discovered the hidden indexes. I have a documentation tool I created about 15 years ago and when I ran it, I saw two indexes for each FK. The one I manually created and the one Access created. Once I figured out that Access was "helping" me by creating these hidden indexes, I stopped creating them manually. Also, it didn't seem to matter if I created a table and created an index on the FK and THEN defined the relationship or if I created the table without the FK index, created the relationship and then created a visible index manually. Both paths led to TWO indexes.

@spaLOGICng I don't see anywhere in SSMA that last option menu (the one with the pink highlights). What version are you using? Or does that image come from SSMS?

 

[GPGeorge]

@GPGeorge What I don't understand is how you ended up with only the FK indexes you manually created in Northwind. I end up with two indexes. The hidden one and the one I create. That was how I discovered the hidden indexes. I have a documentation tool I created about 15 years ago and when I ran it, I saw two indexes for each FK. The one I manually created and the one Access created. Once I figured out that Access was "helping" me by creating these hidden indexes, I stopped creating them manually. Also, it didn't seem to matter if I created a table and created an index on the FK and THEN defined the relationship or if I created the table without the FK index, created the relationship and then created a visible index manually. Both paths led to TWO indexes.

@spaLOGICng I don't see anywhere in SSMA that last option menu (the one with the pink highlights). What version are you using? Or does that image come from SSMS?

I'm not clear on what you mean about NW2 indexes. Now that I am on the same page, though, I'll remigrate NW 2 dev and pay closer attention to the "before" and "after" picture.

The settings are from SSMA. I now have version 9.5 installed as of a few days ago.

 

[spaLOGICng]

The important factor is whether that index is visible in the accdb, in the Index property sheet for that table.

I have never seen or never heard of a hidden index, not in Access, and not in SQL Server.

 

[spaLOGICng]

I'm not clear on what you mean about NW2 indexes. Now that I am on the same page, though, I'll remigrate NW 2 dev and pay closer attention to the "before" and "after" picture.

The settings are from SSMA. I now have version 9.5 installed as of a few days ago.

View attachment 114092


View attachment 114093

There have been a lot of recent updates to SSMA, but I have been using it exclusively for years and have never seen a missed index on a FK, so long as the Index was already defined in the Access Table, regardless if it was used in a FK.

The only time I have ever changed any of the Project Settings is related to the DateTime2 setting for Dates, which is the default. I want all dates in SQL Server to be DATETIME, regardless.

The only setting related to Indexes is to create a Primary Key where a Unique Index cannot be found.

I migrated the OP's Database to SQL Servers. He did not have Indexes on the Foreign Key. I have conformed this in his Access Database. For instance, the PersonID in the Surgeries Tables is not indexed even though it is the FK from the Person Table.