May 8, 2020 at 11:28 am
Hi Experts ,
We are currently working on Sql server 2012 and thinking of migrating the database to Azure sql database. My clients wants me to check if there are any DLL reference(s) such as reading or writing files to local storage (such as Excel), or any other dependencies like this?
can you help me in this task.
Thanks in advance
May 8, 2020 at 12:32 pm
I'm not sure what you mean, DLL references. You'll have to clarify.
Can you query an Azure SQL Database and output the results to a local file like Excel? Yes. Pretty much any way you would normally make a connection to a database and query it to pull information out, you can do that with Azure SQL Database.
In general though, the best practice, and the best performance, is to keep the data you put into the cloud, in the cloud. Access and processing Azure data, through Azure, is the better approach. If, for example, you want to create dashboards & such, instead of using Excel like it's 2005, you use Data Bricks or one of the other data visualization tools in Azure.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2020 at 3:47 pm
You can look for filestream on tables, which is not supported in Azure SQL database. The list of things not supported is here: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-features
May 8, 2020 at 7:44 pm
One place that there could be DLL references would be in any CLR code you have in your database, you can't see the CLR code but you can find the DLLs:
SELECT a.name AS assembly_name, a.clr_name, a.permission_set_desc, af.name AS file_name
FROM sys.assemblies a
INNER JOIN sys.assembly_files af ON a.assembly_id = af.assembly_id
WHERE a.is_user_defined = 1;
A place to look for local file references would be in any SQL Agent jobs you have setup:
SELECT j.job_id, j.name, js.step_id, js.step_name,
p.name as proxy_name, js.command, js.output_file_name,
CASE js.on_success_action WHEN 1 THEN 'Quit with success' WHEN 2 THEN 'Quit with failure' WHEN 3 THEN 'Go to next step' WHEN 4 THEN 'Go to step ' + CAST(js.on_success_step_id AS varchar(3)) END AS on_success_action,
CASE js.on_fail_action WHEN 1 THEN 'Quit with success' WHEN 2 THEN 'Quit with failure' WHEN 3 THEN 'Go to next step' WHEN 4 THEN 'Go to step ' + CAST(js.on_fail_step_id AS varchar(3)) END AS on_fail_action,
js.retry_attempts, js.retry_interval, js.flags
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
LEFT OUTER JOIN msdb.dbo.sysproxies p ON js.proxy_id = p.proxy_id
WHERE j.enabled = 1
AND (js.command LIKE '%sp_send_dbmail%@query_attachment_filename%'
OR js.output_file_name IS NOT NULL
OR js.subsystem = 'CmdExec'
OR js.subsystem = 'PowerShell')
ORDER BY j.name, js.step_id;
While you're at it check stored procedures for calls to sp_send_dbmail also:
SELECT s.name AS schema_name, o.name AS object_name, o.type_desc
FROM sys.objects o
INNER JOIN sys.sql_modules m ON o.object_id = m.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE m.definition LIKE '%sp_send_dbmail%query_attachment_filename%'
OR m.definition LIKE '%sp_send_dbmail%file_attachment%'
ORDER BY o.type_desc, s.name, o.name;
May 8, 2020 at 8:16 pm
Adding to the sql from Chris above.
you wanna search for any module that has
you also need to look for linked servers - not supported
and have a look at https://docs.microsoft.com/en-us/azure/sql-database/sql-database-features for further differences
you need to go through each one of them and see if your client uses any of them - and then look at options to replace that functionality.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply