Working with Microsoft SQL Server

Section 4: Database Platforms for Migration. Lesson 18.

Microsoft SQL Server is a proprietary relational database management system (RDBMS) developed and maintained by Microsoft. It is one of the most widely used RDBMSs in the world, and a very common source or target in enterprise migrations. It is known for stability, reliability, strong security and deep integration with the Microsoft ecosystem (Windows, Azure, Power BI, .NET, Active Directory).

SQL and RDBMS

SQL stands for Structured Query Language: the standard language for defining, querying and changing relational data. SQL Server's own dialect is called T-SQL (Transact-SQL).
An RDBMS is a DBMS (lesson 17) that stores data as related tables of rows and columns, linked by keys and protected by constraints. It is the enterprise-grade form of the DBMS, built for many users, large volumes and strict integrity.

Editions

Edition	Cost	Use it for
Developer	Free	Full features for dev and test; not licensed for production
Express	Free	Small production apps and learning; size and resource limits (10 GB per database)
Standard	Paid	Mid-tier production workloads
Enterprise	Paid	Mission-critical, tier-one workloads, full features and scale

Related offerings: Power BI Report Server for on-premises reporting, SQL Server on Azure Virtual Machines, Azure SQL Database and Azure SQL Managed Instance (fully managed cloud versions), and SQL Server enabled by Azure Arc.

For learning and migration practice, download the free Developer edition from Microsoft's SQL Server downloads page, along with SQL Server Management Studio (SSMS) or Azure Data Studio as the client tool.

Licensing and Azure Arc

Traditional licensing: per-core or server plus client access licences (CAL).
Software Assurance: a volume-licensing programme that adds benefits to an existing licence investment, such as upgrade rights and licence mobility to the cloud.
Pay-as-you-go through Azure Arc: Azure Arc connects on-premises and other-cloud SQL Server instances to Azure. You pay only for what you use, and get central management, security and governance, and business-continuity features.

Azure Arc is optional. SQL Server runs perfectly well without it; Arc adds hybrid management and the pay-as-you-go billing model.

Key features

Security: encryption (Always Encrypted, TDE), row-level security, auditing, dynamic data masking
Performance: in-memory OLTP, columnstore indexes, Query Store for tuning
Availability: Always On availability groups, failover clustering, backups and log shipping
Analytics and AI: integrated with Power BI; recent versions add built-in AI and vector features
Integration: SSIS (Integration Services) for ETL, which is often used in migrations

Database objects a migration must handle

Object	What it is	Migration concern
Tables	Rows and columns of data	Type mapping, keys, constraints
Views	Saved queries	T-SQL syntax must be rewritten for other engines
Stored procedures	Server-side T-SQL programs	Usually the biggest manual rewrite
Functions	Reusable T-SQL logic	Rewrite; built-ins differ
Indexes	Speed up lookups	Recreate; types like columnstore may not exist
Triggers	Code that fires on changes	Different models across engines
Logins, users, roles	Security	Recreate permissions in the target

Each object needs to be analysed, mapped, converted and validated. The team must understand how SQL Server organises data, enforces relationships, manages security and optimises performance to plan a smooth migration.

sql

-- Inventory a SQL Server database before migrating
SELECT type_desc, COUNT(*) AS objects
FROM sys.objects
WHERE is_ms_shipped = 0
GROUP BY type_desc
ORDER BY objects DESC;

-- Largest tables by row count
SELECT t.name AS table_name, SUM(p.rows) AS row_count
FROM sys.tables t
JOIN sys.partitions p ON p.object_id = t.object_id AND p.index_id IN (0, 1)
GROUP BY t.name
ORDER BY row_count DESC;

Migration tooling from Microsoft: SQL Server Migration Assistant (SSMA) for moving Oracle, MySQL, Db2 and others into SQL Server; Azure Database Migration Service for moving into Azure; and the Data Migration Assistant for upgrade-compatibility checks.

Database engines and CRUD

Every database runs on a database engine (or storage engine): the core software component a DBMS uses to perform CRUD operations and to store, index and retrieve data on disk and in memory.

CRUD stands for Create, Read, Update and Delete, the four basic operations behind almost everything a database does:

sql

INSERT INTO customers (customer_id, full_name) VALUES (1001, 'Sarah Johnson');  -- Create
SELECT full_name FROM customers WHERE customer_id = 1001;                    -- Read
UPDATE customers SET full_name = 'Sarah J. Johnson' WHERE customer_id = 1001; -- Update
DELETE FROM customers WHERE customer_id = 1001;                              -- Delete

Examples of engines:

InnoDB: the default storage engine inside MySQL and MariaDB
SQLite: an embedded engine that runs inside the application as a single file
PostgreSQL: its own integrated engine with MVCC storage
Berkeley DB and RocksDB: embedded key-value engines used inside other systems
Microsoft Access Database Engine (ACE, formerly Jet): behind Access files
SQL Server's own engine: the Database Engine service, with its relational engine and storage engine

The engine matters for migration because it decides transaction behaviour, locking, supported types and index structures. Two databases can both speak SQL and still behave differently under load.

Key takeaways

SQL Server is Microsoft's proprietary RDBMS, and T-SQL is its SQL dialect.
Use the free Developer edition to practise; Express, Standard and Enterprise serve production at different scales.
Pay-as-you-go through Azure Arc is optional hybrid billing and management.
Migrations must map tables, views, procedures, functions, indexes, triggers and security objects.
The database engine performs CRUD and shapes how data is stored and locked.

Next: 19. Working with MySQL.