get ddl references or other references

  • arvind.patil 98284

    SSC-Addicted

    Points: 410

    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

     

     

  • Grant Fritchey

    SSC Guru

    Points: 396617

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720436

    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

  • Chris Harshman

    SSC-Forever

    Points: 42145

    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;

     

  • frederico_fonseca

    SSChampion

    Points: 14705

    Adding to the sql from Chris above.

    you wanna search for any module that has

    • bulk insert
    • openrowset
    • openquery
    • xp_cmdshell
    • oa_*** extended sp's ( OLE Automation)

    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 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply