Secure, optimize, and deploy database solutions

-- Always Encrypted: the driver encrypts on the client; the engine never sees plaintext.
-- Keys: a column master key (in Azure Key Vault) protects column encryption keys.
CREATE TABLE dbo.Patient
(
    PatientID  int IDENTITY PRIMARY KEY,
    NationalId char(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Patient,
                        ENCRYPTION_TYPE = DETERMINISTIC,          -- equality lookups, joins, grouping
                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    Diagnosis  nvarchar(400)
        ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = CEK_Patient,
                        ENCRYPTION_TYPE = RANDOMIZED,             -- stronger; no operations without an enclave
                        ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NULL
);
-- Client connection string: Column Encryption Setting=Enabled
-- Queries must be parameterized so the driver can encrypt the parameter values.

-- Column-level (cell-level) encryption: the engine encrypts with a symmetric key.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'<strong password>';
CREATE CERTIFICATE CertPayroll WITH SUBJECT = 'Payroll column key protector';
CREATE SYMMETRIC KEY SymPayroll WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE CertPayroll;

OPEN SYMMETRIC KEY SymPayroll DECRYPTION BY CERTIFICATE CertPayroll;
UPDATE dbo.Employee SET SalaryEnc = ENCRYPTBYKEY(KEY_GUID('SymPayroll'), CAST(Salary AS varchar(20)));
SELECT EmployeeID, CAST(DECRYPTBYKEY(SalaryEnc) AS varchar(20)) AS Salary FROM dbo.Employee;
CLOSE SYMMETRIC KEY SymPayroll;

-- Dynamic data masking: obfuscates results for users without UNMASK. Data is unchanged.
ALTER TABLE dbo.Customer ALTER COLUMN Email
    ADD MASKED WITH (FUNCTION = 'email()');                -- aXXX@XXXX.com
ALTER TABLE dbo.Customer ALTER COLUMN Phone
    ADD MASKED WITH (FUNCTION = 'partial(0, "XXX-XXX-", 4)');
ALTER TABLE dbo.Customer ALTER COLUMN CreditLimit
    ADD MASKED WITH (FUNCTION = 'default()');              -- 0 for numbers, XXXX for strings
ALTER TABLE dbo.Customer ALTER COLUMN BirthDate
    ADD MASKED WITH (FUNCTION = 'datetime("Y")');          -- masks the year

-- Granular UNMASK (SQL Server 2022+, Azure SQL, Fabric): database, schema, table or column
GRANT UNMASK ON dbo.Customer(Email) TO SupportAgent;

-- Row-level security: an inline TVF predicate bound to a security policy
CREATE SCHEMA Security;
GO
CREATE FUNCTION Security.fn_TenantPredicate (@TenantID int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS allowed
       WHERE @TenantID = CAST(SESSION_CONTEXT(N'TenantID') AS int)
          OR IS_MEMBER(N'db_owner') = 1;
GO
CREATE SECURITY POLICY Security.TenantPolicy
    ADD FILTER PREDICATE Security.fn_TenantPredicate(TenantID) ON dbo.Orders,               -- hides rows from reads
    ADD BLOCK  PREDICATE Security.fn_TenantPredicate(TenantID) ON dbo.Orders AFTER INSERT   -- stops writing other tenants' rows
WITH (STATE = ON);
GO
-- The application sets the tenant once per connection
EXECUTE sys.sp_set_session_context @key = N'TenantID', @value = 42, @read_only = 1;

-- Least privilege through roles and schemas, not individual users and objects
CREATE ROLE app_reader;
GRANT SELECT ON SCHEMA::Sales TO app_reader;                -- every current and future object in the schema
CREATE ROLE app_writer;
GRANT EXECUTE ON SCHEMA::Api TO app_writer;                 -- procedures only; no direct table access
DENY  SELECT ON dbo.Salary TO app_reader;                   -- DENY wins over any GRANT

-- Passwordless: a contained user for an app's managed identity (Entra ID)
CREATE USER [contoso-orders-api] FROM EXTERNAL PROVIDER;
ALTER ROLE app_writer ADD MEMBER [contoso-orders-api];

-- SQL Server / Azure SQL MI: server audit + database audit specification
CREATE SERVER AUDIT AuditToFile TO FILE (FILEPATH = N'D:\Audit\');
ALTER SERVER AUDIT AuditToFile WITH (STATE = ON);

CREATE DATABASE AUDIT SPECIFICATION PatientAccess
FOR SERVER AUDIT AuditToFile
    ADD (SELECT, UPDATE ON dbo.Patient BY public),
    ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP)
WITH (STATE = ON);

-- Read the logs (SQL Server, MI, Azure SQL Database and Fabric all expose this function family)
SELECT event_time, action_id, server_principal_name, statement
FROM sys.fn_get_audit_file(N'D:\Audit\*.sqlaudit', DEFAULT, DEFAULT);

-- Secure a model endpoint with the database's managed identity (no API key in the database).
-- Grant the identity the "Cognitive Services OpenAI User" role on the Azure OpenAI / Foundry resource first.
-- Azure SQL Database and Fabric: the logical server's / item's identity.
-- SQL Server 2025: connect the server to Azure Arc and enable its primary managed identity, then
--   EXECUTE sp_configure 'allow server scoped db credentials', 1; RECONFIGURE;
CREATE DATABASE SCOPED CREDENTIAL [https://contoso-ai.openai.azure.com/]
    WITH IDENTITY = 'Managed Identity',
         SECRET   = '{"resourceid":"https://cognitiveservices.azure.com"}';

CREATE EXTERNAL MODEL EmbeddingModel
WITH (
    LOCATION   = 'https://contoso-ai.openai.azure.com/openai/deployments/text-embedding-3-small/embeddings?api-version=2024-02-01',
    API_FORMAT = 'Azure OpenAI',
    MODEL_TYPE = EMBEDDINGS,
    MODEL      = 'text-embedding-3-small',
    CREDENTIAL = [https://contoso-ai.openai.azure.com/]
);
GRANT EXECUTE ON EXTERNAL MODEL::EmbeddingModel TO app_writer;

-- Reader sees the last committed version instead of waiting for writers
ALTER DATABASE CURRENT SET READ_COMMITTED_SNAPSHOT ON;      -- on by default in Azure SQL Database

-- Transaction-level SNAPSHOT: one consistent view for the whole transaction
ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
    SELECT SUM(Balance) FROM dbo.Account;                   -- stable for the whole transaction
COMMIT;

-- Pessimistic read-then-update without a lost update: lock the row you read
BEGIN TRANSACTION;
    SELECT Quantity FROM dbo.Stock WITH (UPDLOCK, HOLDLOCK) WHERE ProductID = 42;
    UPDATE dbo.Stock SET Quantity -= 1 WHERE ProductID = 42;
COMMIT;

-- Optimistic concurrency with rowversion
UPDATE dbo.Product SET ListPrice = @NewPrice
WHERE ProductID = @ID AND RowVer = @RowVerReadEarlier;
IF @@ROWCOUNT = 0 THROW 50001, 'The row was changed by someone else.', 1;

-- What is running now, and who is blocking whom?
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time, r.cpu_time,
       t.text AS sql_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID;

-- Most expensive cached statements by CPU
SELECT TOP (10) qs.total_worker_time / qs.execution_count AS avg_cpu, qs.execution_count,
       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1, 200) AS stmt, qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY avg_cpu DESC;

-- Azure SQL Database: resource use in 15-second intervals for the last hour
SELECT end_time, avg_cpu_percent, avg_data_io_percent, avg_log_write_percent
FROM sys.dm_db_resource_stats ORDER BY end_time DESC;

-- Query Store: on by default in Azure SQL Database and new SQL Server 2022+ databases
ALTER DATABASE CURRENT SET QUERY_STORE = ON (OPERATION_MODE = READ_WRITE);

-- Find a regressed query and its plans
SELECT q.query_id, p.plan_id, rs.avg_duration, rs.last_execution_time
FROM sys.query_store_query AS q
JOIN sys.query_store_plan AS p           ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
WHERE q.query_id = 1234
ORDER BY rs.avg_duration;

-- Fix it without touching the application
EXECUTE sp_query_store_force_plan @query_id = 1234, @plan_id = 17;                     -- pin the good plan
EXECUTE sp_query_store_set_hints  @query_id = 1234, @query_hints = N'OPTION (RECOMPILE)'; -- or add a hint

<!-- An SDK-style SQL project: every .sql file under the folder is included by default -->
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="2.3.0" />
  <PropertyGroup>
    <Name>Contoso.Database</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.SqlAzureV12DatabaseSchemaProvider</DSP>
    <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
  </PropertyGroup>
  <ItemGroup>
    <PostDeploy Include="Scripts\PostDeployment.sql" />
  </ItemGroup>
</Project>

-- A tSQLt unit test: arrange with a fake table, act, assert
EXEC tSQLt.NewTestClass 'OrderTests';
GO
CREATE PROCEDURE OrderTests.[test usp_PlaceOrder inserts one header]
AS
BEGIN
    EXEC tSQLt.FakeTable 'dbo.OrderHeader';
    DECLARE @no bigint;
    EXEC dbo.usp_PlaceOrder @CustomerID = 7, @OrderNo = @no OUTPUT;
    DECLARE @rows int = (SELECT COUNT(*) FROM dbo.OrderHeader WHERE CustomerID = 7);
    EXEC tSQLt.AssertEquals @Expected = 1, @Actual = @rows;
END;
GO
EXEC tSQLt.Run 'OrderTests';

-- Scripts/PostDeployment.sql — idempotent reference data, runs after every publish
MERGE dbo.OrderStatus AS target
USING (VALUES (1, N'Placed'), (2, N'Shipped'), (3, N'Delivered'), (4, N'Cancelled'))
      AS source (StatusID, Name)
ON target.StatusID = source.StatusID
WHEN MATCHED AND target.Name <> source.Name THEN UPDATE SET Name = source.Name
WHEN NOT MATCHED BY TARGET THEN INSERT (StatusID, Name) VALUES (source.StatusID, source.Name)
WHEN NOT MATCHED BY SOURCE THEN DELETE;

# Build the model and validate it
dotnet build Contoso.Database.sqlproj -c Release      # → bin/Release/Contoso.Database.dacpac

# What would a publish change? (XML report and a reviewable script)
sqlpackage /Action:DeployReport /SourceFile:bin/Release/Contoso.Database.dacpac \
  /TargetConnectionString:"$TARGET" /OutputPath:deployreport.xml
sqlpackage /Action:Script /SourceFile:bin/Release/Contoso.Database.dacpac \
  /TargetConnectionString:"$TARGET" /OutputPath:deploy.sql

# Has anyone changed a registered database outside the pipeline?
sqlpackage /Action:DriftReport /TargetConnectionString:"$TARGET" /OutputPath:drift.xml

# Publish, refusing anything that could lose data
sqlpackage /Action:Publish /SourceFile:bin/Release/Contoso.Database.dacpac \
  /TargetConnectionString:"$TARGET" /p:BlockOnPossibleDataLoss=true

-- Change Tracking: which rows changed since version N (no old values)
ALTER DATABASE CURRENT SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE dbo.Product ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

DECLARE @last bigint = 1520;                                  -- stored by the consumer after its last sync
SELECT ct.ProductID, ct.SYS_CHANGE_OPERATION, ct.SYS_CHANGE_VERSION, p.Name, p.ListPrice
FROM CHANGETABLE(CHANGES dbo.Product, @last) AS ct
LEFT JOIN dbo.Product AS p ON p.ProductID = ct.ProductID;     -- current values; deleted rows return NULL
SELECT CHANGE_TRACKING_CURRENT_VERSION();                     -- save as the next @last

-- Change data capture: every change with before and after images
EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'Product', @role_name = NULL;
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Product(
    sys.fn_cdc_get_min_lsn('dbo_Product'), sys.fn_cdc_get_max_lsn(), N'all update old');

-- Change event streaming (preview): publish row changes as CloudEvents
-- SQL Server 2025 needs PREVIEW_FEATURES and the FULL recovery model; Azure SQL is always FULL.
EXEC sys.sp_enable_event_stream;
EXEC sys.sp_create_event_stream_group
     @stream_group_name      = N'ordersStream',
     @destination_type       = N'AzureEventHubsApacheKafka',   -- SQL Server 2025 / MI; 'AzureEventHubs' on Azure SQL Database and Fabric
     @destination_location   = N'contoso-ns.servicebus.windows.net:9093/orders',
     @destination_credential = CesEventHubsCredential,          -- a database scoped credential
     @partition_key_scheme   = N'Table',
     @encoding               = N'JSON';
EXEC sys.sp_add_object_to_event_stream_group
     @stream_group_name = N'ordersStream', @object_name = N'dbo.Orders';
SELECT * FROM sys.dm_change_feed_errors;   -- delivery errors