July 25, 2019 at 1:51 pm
We have several databases that have been migrated to SQL Server 2016, but they are at compatibility level 100. They need to be at 130. I was able to successfully run the Data Migration Assistant for 3 of them without any problems, the largest of those is about 60Gb and the DMA took about 10 minutes. The last 2 are our EHR databases (Meditech) and I've been trying to run the DMA against testmdb without success. It is 10 Gb, I ran the DMA through the GUI and had to quit after 3 hours. I then realized that the DMA should be installed on a different server and run from there. My manager installed it on a separate server, ran it from the command line and it ran for over 12 hours before he terminated it. He sees nothing in the log files indicating any problem. No error messages popped up in the GUI when I ran it that way either.
Any advice/suggestions/pointers as to where to begin figuring out what the issue is? TIA
July 25, 2019 at 3:00 pm
DMA issues SQL calls to the database. You can view currently executing SQL statements on SQL Server with the following SQL:
SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
So it might be worth checking this to see what, if anything, DMA is doing.
July 25, 2019 at 4:28 pm
Unless I am missing something, you are only changing the compatibility level of the database?
Why won't this work? What's the purpose of the DMA to simply change the compatibility level?
USE [master]
GO
ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 130
GO
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 25, 2019 at 6:16 pm
Unless I am mistaken - Meditech is a vendor supplied and supported database. Before upgrading the database system to 2016 - the vendor should have signed off on making that change.
Generally - these kinds of applications cannot support upgrading the database engine or compatibility level without also upgrading the application code. If you perform this upgrade and it doesn't work - you may find yourself in a situation where the vendor cannot support the system until you revert back to a supported version.
I would be very careful about making this change without approval and sign-off from the vendor.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 25, 2019 at 6:28 pm
Michael L John wrote:What's the purpose of the DMA to simply change the compatibility level?
I think OP wants to make sure there won't be any incompatible code when level is changed.
DMA can help you with that, but that doesn't mean that it'll go smoothly, you might find good and fast running queries at Compatibility Level 100 but when changing to 130, these queries may take minutes to complete.
If you are looking for deprecated functions, practices, system tables, i think you are better off using Extended Events.
CREATE EVENT SESSION [deprecated] ON SERVER
ADD EVENT sqlserver.deprecation_announcement(
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.deprecation_final_support(
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
WHERE ([package0].[not_equal_unicode_string]([message],N'''''') AND [severity]>(10)))
ADD TARGET package0.event_file(SET filename=N'D:\XEvents\Deprecated.xel',max_file_size=(100),max_rollover_files=(0))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
July 25, 2019 at 6:40 pm
rVadim wrote:Michael L John wrote:What's the purpose of the DMA to simply change the compatibility level?
I think OP wants to make sure there won't be any incompatible code when level is changed.
DMA can help you with that, but that doesn't mean that it'll go smoothly, you might find good and fast running queries at Compatibility Level 100 but when changing to 130, these queries may take minutes to complete. If you are looking for deprecated functions, practices, system tables, i think you are better off using Extended Events.
CREATE EVENT SESSION [deprecated] ON SERVER
ADD EVENT sqlserver.deprecation_announcement(
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.deprecation_final_support(
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)),
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.query_plan_hash,sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
WHERE ([package0].[not_equal_unicode_string]([message],N'''''') AND [severity]>(10)))
ADD TARGET package0.event_file(SET filename=N'D:\XEvents\Deprecated.xel',max_file_size=(100),max_rollover_files=(0))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO
My thoughts exactly. The DMA will only tell you what is deprecated, and warn you about features that may perform poorly.
Significant testing is required when upgrading versions. In this case, going from 2008 to 2016 may uncover some performance issues and code may need to be re-written.
Curious to hear more details from the OP about this process.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 25, 2019 at 9:51 pm
Great points guys, Thank You!
--Vadim R.
July 26, 2019 at 12:04 pm
Thanks for all your responses. The vendor is requiring the compatibility level change to ensure that their Meaningful Use code works correctly. My understanding is that running the DMA will highlight any potentially incompatible code so that it can be changed prior to changing the compatibility level. We have customer defined views in this database, but I believe everything else is vendor supplied. And I thought that this is something Microsoft recommends. Attached is a snip of the log that my manager provided after the DMA was running for 3+ hours . I don't know how to interpret this.
I will try running the extended events this morning and see what that reveals. If nothing concerning comes out of doing that, perhaps then I just go ahead and alter the db?
July 26, 2019 at 1:04 pm
What settings did you choose when you ran the DMA?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
July 26, 2019 at 1:34 pm
C:\Program Files\Microsoft Data Migration Assistant\DmaCmd.exe /AssessmentName="DMA_Output_testmdb" /AssessmentDatabases=
"Server=ServerName;Initial Catalog=testmdb;Integrated Security=true" /AssessmentEvaluateCompatibilityIssues /AssessmentOverwriteResult /AssessmentResultCSV=
"\\sharedrive\InfoSvcs\UserC\ServerName\testmdb.CSV" > "\\sharedrive\InfoSvcs\UserC\ServerName\testmdb.LOG"
July 26, 2019 at 3:23 pm
Alejandro,
I have started the XE. At what point would I stop it?
I'm seeing a few deprecation_final_support notifications in the package0.event_file, but nothing truly alarming.
Thanks for providing the code for this, it's not something I am familiar with.
July 26, 2019 at 3:42 pm
Alejandro, I have started the XE. At what point would I stop it? I'm seeing a few deprecation_final_support notifications in the package0.event_file, but nothing truly alarming. Thanks for providing the code for this, it's not something I am familiar with.
My recommendation would be:
Use it in production environment, as the time passes your customers/users should be using the application which means your extended event should be gathering information, make sure your extended event can have multiple files, it can grow quite large and if you can make it create another file as one fills.
i.e: the file can grow up to 500MB and can roll up to 5 files, 5x500 = 2.5GB of XE data.
The time really depends on your application and business, maybe something happens in the middle of the month, every week, monthly, everyday at the end of the day, some processes that is required at the end of the day to happen, watch everything closely, if you think there's something deprecated, recommendations, make your Development team FIX these. push these changes to a QA environment, NOT production just yet, make your QA team test it from head to toes, get times, how many seconds, minutes this process takes BEFORE and after the change.
It's not something that will just take a day, its a quite long process depending on how your business works, how big your application is and your users.
In our case we spent months gathering data, we have thousands of users and we had Microsoft help us in the migration, there was a TON of stuff we had to change and EVEN after the migration, we still had issues which made us switch back to compat level 100, we ran into queries from taking minutes to never finishing when compatibility level was changed to 130, our QA Team is small and they said we were good to go, but when an end user made a really long transaction inserting, modifying thousands of rows in hundreds of tables it would never end.
In the end, its really hard, nothing will end as perfect as you wish, things might change a little. after changing compatibility level keep track of queries using sp_whoisactive inserting in a table, grab these times, see if something is locking.
It's quite a challenging process.
July 26, 2019 at 4:02 pm
I really appreciate your advice and insight. I can see that XE can be a very useful tool that will highlight any issues over time. I'm going to keep it going at least through month-end, then evaluate.
I do have the QueryStore set up so I can compare plans before and after, if we have performance issues.
Ironically, the DMA finally finished after 19 hours. From what I can see, it looks like all issues (30K lines in the csv, mostly implied JOINs) are Meditech's own code. (It may be from modules we don't use)
As the only SQL person at this hospital, I'm grateful for the help available here.
Thanks again!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply