SQL Server – How to Rename Index with SSMA

sql serverssma

I am using Microsoft SQL Server Migration Assistant v6 (aka SSMA) to update a database from Access to SQL Server. I am getting informational messages that say "A2SS0029: Index name 'Name' was changed during conversion.".

As these are just index names, that shouldn't cause a problem for me. But I do wonder why the name has to be changed. Particularrly since I was able to create an index with the given name manually after the migration.

Why are the index names being renamed? And am I wrong about that not causing any problems (given that I don't have any queries that use index hints, if that is even possible with Access).

Best Answer

While there are reasons that a tool might make intelligent decisions about index names (like to prevent duplicate names in cases where there are actual collisions, and further when it actually matters, like when they are in the same table), it would appear that SSMA just creates new, unique names for all indexes. Perhaps only in the case where it finds at least one index name that is reused, not sure. I would test this, but I don't have Access installed anywhere.

Why does it do that? Who knows? Extreme conservatism? Paranoia? I think it's just being extremely safe.

There are cases where duplicate names would be a problem, but these are isolated to first class entities that end up in sys.objects, like primary key constraints. Index names are only enforced to be unique at the table level (since the name is only exposed in sys.indexes), not at the database level.

Related Solutions

Sql-server – .NET SQL Server Authentication Schema issue

I'm not sure what is preventing you assigning a default schema to the user. You can simply use:

ALTER USER [NT AUTHORITY\Authenticated Users] 
WITH DEFAULT_SCHEMA = [Production]

Obviously replacing the user/schema names as appropriate.

However, it would be best if you could update the code to use a schema prefix - some more reading on that is here (functional reasons) and here (performance reasons).

Sql-server – Workarounds for setting default schema for domain group

Taking @Mr.Brownstone's idea, you could definitely try this:

DECLARE @sql nvarchar(MAX) = N'';

SELECT @sql += N'
  ALTER SCHEMA dbo TRANSFER custom_schema.' + QUOTENAME(name) + ';'
  FROM sys.objects
  WHERE SCHEMA_NAME([schema_id]) = N'custom_schema';

EXEC sys.sp_executesql @sql;

Now, it may be the case that you have foreign keys etc. that will need to be dropped and re-created (and I've shown how to automate that too), or as I mentioned in a comment, you may have explicit references to custom_schema in your existing objects. You may also have duplicate objects (say, dbo.Customers and custom_schema.Customers) that you'll need to handle. But the general idea should be sound.

Another workaround would be to upgrade to SQL Server 2012, where you can set a DEFAULT_SCHEMA for a domain group. I haven't tested this, but this Connect item says it has been made so.

In either case, I still think the app should be fixed eventually. As I've mentioned many times before, you should ALWAYS use an explicit schema prefix when creating or referencing objects.

Best Answer

Related Solutions

Sql-server – .NET SQL Server Authentication Schema issue

Sql-server – Workarounds for setting default schema for domain group

Related Question