SQL Server modernization risk: when the database is the application

The SQL patterns that change scope

These are the recurring patterns that turn "migrate the database" into a much larger piece of work. Each one is field-observed, invisible to a code-only review, and moves the estimate.

• Business logic in stored procedures and triggers. The rule that decides whether an order ships, the calculation behind a customer's price, the validation that keeps bad data out -- if these live in T-SQL rather than the application, modernizing the application doesn't touch them. Where to look: sys.sql_modules for procedure and view definitions, sys.triggers for DML triggers, and the size of those definitions. A 4,000-line stored procedure is not a storage detail; it's an application nobody named.
• Cross-database and cross-server coupling. Queries that use three- or four-part names (OtherDatabase.dbo.Table, LinkedServer.Db.dbo.Table) assume the databases and servers will always sit together. They won't, in a cloud target. Where to look: sys.servers for linked servers (direct connections from one database to another), and a search of procedure text for cross-database references. This pattern alone can decide which Azure target is even viable -- see below.
• SQL Agent jobs doing real work. ETL, billing runs, nightly reconciliation, data cleanup, the export the finance team depends on -- scheduled inside SQL Server, outside the application. Where to look: msdb.dbo.sysjobs and sysjobsteps to see what each job actually runs. Jobs are the single most common "we didn't know that was there" finding, because nobody opens SQL Server Agent during an application review.
• Reporting that reads production tables directly. SSRS reports, Excel workbooks, and Power BI datasets pointed straight at the operational schema. The moment the schema changes during modernization, these break silently, and they're often the most politically visible thing in the building. Where to look: the SSRS ReportServer catalog, and connection strings in the reporting estate.
• SSIS packages as hidden integration. The data flows between systems frequently run as SSIS packages (Microsoft's tool for moving data between systems) on a schedule, not as application code. Where to look: the SSIS catalog (SSISDB) or msdb, and .dtsx files on the server. An SSIS package is an integration that has to be rebuilt or re-hosted, not a query.
• Engine features that don't port to every target. SQL CLR assemblies, Service Broker queues, FILESTREAM and FileTable, and distributed transactions are all specialized SQL Server capabilities that a modern managed cloud database may not support. Where to look: sys.assemblies for CLR, sys.service_queues for Service Broker, and column metadata for FILESTREAM. Each one narrows your migration options -- if your system depends on one the cheaper targets don't offer, those targets are simply off the table.
• Compatibility level masking behavior. A database running on a current SQL Server instance but set to an old compatibility level (a setting that makes a new SQL Server act like an older one) behaves, in places, like the old version -- including the query optimizer. Modernizing can quietly change the level and change query plans with it. Where to look: sys.databases.compatibility_level. Azure SQL Database won't accept a level below 100 at all.
• Data quality the old system tolerates. Disabled or untrusted foreign keys, cleanup logic that runs in the application instead of constraints, "we fix that in the nightly job" data. Where to look: sys.foreign_keys and sys.check_constraints for is_disabled / is_not_trusted. Data the old system tolerated will break the new one's assumptions, and reconciling it is its own workstream.

The SQL inventory

This is the database-side inventory the triage checklist points to. Every item below is a query against system metadata, which means you can produce most of it in an afternoon without guessing. An afternoon of looking now replaces a year of finding out later, and it converts the database from a hopeful assumption in the budget into a measured number.

• Programmable objects and their weight. sys.procedures, sys.sql_modules (the definition text), sys.triggers, and the function objects in sys.objects. Don't just count them -- measure them. The total line count of T-SQL logic is a direct proxy for how much application lives in the database.
• Scheduled work. msdb.dbo.sysjobs and msdb.dbo.sysjobsteps for SQL Agent jobs and what each step runs. Note which jobs touch other systems -- those are integrations, not maintenance.
• Cross-boundary coupling. sys.servers for linked servers, and sys.sql_expression_dependencies to surface cross-database references. These determine which targets are viable.
• Integration and reporting. The SSISDB catalog (or msdb) for SSIS packages, and the SSRS ReportServer database for reports and their data sources. Both are work that doesn't appear in the application repository.
• Engine-feature dependencies. sys.assemblies (user-defined CLR), sys.service_queues and the database's is_broker_enabled flag (Service Broker), and FILESTREAM usage via is_filestream in sys.columns. Each one constrains the target.
• Behavior and integrity flags. sys.databases.compatibility_level for each database, and is_disabled / is_not_trusted on sys.foreign_keys and sys.check_constraints. These predict where data reconciliation will be needed.
• Backups that have actually been restored. Backup history is in msdb, but the real question is whether a restore has ever been tested. A backup that has never been restored is a theory with a timestamp.

How much of the business logic lives in the database?

Modernization Shield reviews stored procedures, schema complexity, SQL Agent jobs, linked servers, and application data-access patterns before the database becomes missing scope. It shows where business logic is hiding, what is tightly coupled, and which target platforms the current design can support.

The SocialGoal sample Modernization Risk Report shows how those database findings are evidenced and sequenced alongside the application work.