Data Quality Server and Client: SQL Server Legacy Installation Notes

Data Quality Server and Data Quality Client belonged to SQL Server’s historical DQS feature set, enabling knowledge-driven data profiling, cleansing and review.

Concepts and context

The server held the knowledge bases and related project data. The client offered workflows for discovering values, maintaining domain rules, running cleansing projects, and reviewing uncertain corrections.

Practical application

For an existing supported legacy installation, verify the exact SQL Server version, permissions, feature state, system prerequisites, backups and documented installation order. For new workflows, use constraints, profiling, validation rules and review queues.

Compatibility and cautions

DQS is removed in SQL Server 2025 and remains associated with older supported releases. Always test schema and cleanup procedures on copied data before applying them to business records.


AI and reuse disclosure: This newly written tutorial was generated with AI using an existing Open Education article (post 22427) as its starting point; explanations and examples may have been added. You may copy, edit and reuse the generated text with or without a reference. Rights in trademarks and third-party content remain with their respective owners. AI can make mistakes. Verify version compatibility, code and operational advice against current official documentation.

Configuring SQL Server Master Data Services: Legacy Checklist and Migration

Older SQL Server deployments could configure Master Data Services to expose shared master data through a database and management application. These instructions should be read as historical.

Concepts and context

A former setup considered the MDS database, web application, IIS and service identities, model and entity permissions, application URL, and downstream integrations. Exact prerequisites depended on SQL Server release.

Practical application

Before changing an existing deployment, inventory databases, models, versions, integration jobs, user roles, backups, and dependencies. Export representative models and validate key relationships and business rules in a separate environment.

Compatibility and cautions

SQL Server 2025 no longer includes MDS. Do not follow legacy installers on a current instance. Compare a replacement solution’s governance, import/export, validation, security and auditing features before migration.


AI and reuse disclosure: This newly written tutorial was generated with AI using an existing Open Education article (post 22429) as its starting point; explanations and examples may have been added. You may copy, edit and reuse the generated text with or without a reference. Rights in trademarks and third-party content remain with their respective owners. AI can make mistakes. Verify version compatibility, code and operational advice against current official documentation.

SQL Server Master Data Services: Entities, Models, and Governance (Legacy)

Master Data Services (MDS) historically helped organizations manage shared reference entities such as customers, products, and locations. It addressed governance rather than ordinary application transactions.

Concepts and context

An MDS model groups entities, members and attributes. Business rules, hierarchies, versioning, and stewardship workflows help maintain consistent business identities and values across systems.

Practical application

For a product-master example, define stable product codes, permitted categories, naming standards, approvals and downstream synchronization contracts. Evaluate duplicates by business key and keep change history.

Compatibility and cautions

Microsoft removed MDS from SQL Server 2025. Maintain existing installations only according to supported-version guidance and plan migration before an upgrade. Modern alternatives include governed data tables, controlled APIs, and dedicated MDM platforms.


AI and reuse disclosure: This newly written tutorial was generated with AI using an existing Open Education article (post 22431) as its starting point; explanations and examples may have been added. You may copy, edit and reuse the generated text with or without a reference. Rights in trademarks and third-party content remain with their respective owners. AI can make mistakes. Verify version compatibility, code and operational advice against current official documentation.

SQL Server Data Quality Services: Knowledge Bases and Cleansing (Legacy)

Data Quality Services (DQS) was used to cleanse data through domain knowledge and supervised matching. An older DQS project typically created a knowledge base, applied its rules to source records, and reviewed ambiguous results.

Concepts and context

A data-quality domain can standardize recognized values such as city names or postal codes. A project profiles source fields, applies accepted mappings and rules, and separates automatic corrections from records that need manual approval.

Practical application

For a modern implementation, profile missing values, normalize formatting, enforce relational constraints, maintain approved reference sets, and record exactly which rows were changed. A simple first step can be SELECT CustomerID, UPPER(LTRIM(RTRIM(City))) AS NormalizedCity FROM dbo.CustomerStaging.

Compatibility and cautions

DQS was removed from SQL Server 2025 (17.x). Older installation and client screenshots apply only to earlier supported versions such as SQL Server 2022. Do not treat approximate matches as reliable without human validation.


AI and reuse disclosure: This newly written tutorial was generated with AI using an existing Open Education article (post 22433) as its starting point; explanations and examples may have been added. You may copy, edit and reuse the generated text with or without a reference. Rights in trademarks and third-party content remain with their respective owners. AI can make mistakes. Verify version compatibility, code and operational advice against current official documentation.

SQL Server DMVs and DBCC Commands: A Beginner’s Primer

Dynamic management views and DBCC commands are two foundations of SQL Server administration. DMVs answer questions about current and accumulated engine state, while DBCC offers specific database-console operations.

Dynamic management views

Examples include sys.dm_exec_requests for active requests, sys.dm_os_wait_stats for aggregate waits, and sys.dm_db_index_usage_stats for index-use indicators. Some values reset; permissions differ by version.

DBCC commands

DBCC CHECKDB assesses logical and physical consistency; DBCC CHECKTABLE targets a table. Other DBCC commands have very different effects, so learn each syntax and risk separately.

A short diagnostic query

SELECT session_id, command, status, wait_type
FROM sys.dm_exec_requests
WHERE session_id > 50;

How to learn safely

Begin with supported read-only queries against a development instance. Read the documentation for your SQL Server version; measure collection overhead and check permissions before attempting checks against live systems.

Next steps

Study Query Store, Extended Events, actual execution plans, restore drills, and how to use diagnostic results to form a verifiable hypothesis.


AI content and reuse notice: Written using AI based on this site’s earlier post (22513), with added explanations and examples. The newly generated text may be reused, edited, or republished with or without attribution. Third-party works and trademarks remain subject to their own rights. AI can make mistakes. Check all code, version-specific features, and operational decisions against current vendor documentation before production use.

Using Windows Performance Monitor for SQL Server Health and Tuning

Performance Monitor can record CPU, memory, disk, and SQL Server counters. Its value is strongest when measurements are compared over time and tied to a specific application symptom.

Set up a baseline

Create a data collector set with a manageable sample interval. Monitor system CPU, physical disk latency, memory conditions, and applicable SQL Server performance objects.

Interpret in context

A high counter is not automatically a fault. For example, buffer cache usage can be expected; disk latency matters most alongside actual request volume and affected data files.

Connect the dots

Use Query Store or an actual execution plan to identify expensive statements, and compare their execution windows with host-resource graphs. Assess blocking and waits before scaling hardware.

Safe tuning loop

Change one parameter or query design at a time, collect before/after metrics, document production impact, and retain a rollback plan.


AI content and reuse notice: Written using AI based on this site’s earlier post (22515), with added explanations and examples. The newly generated text may be reused, edited, or republished with or without attribution. Third-party works and trademarks remain subject to their own rights. AI can make mistakes. Check all code, version-specific features, and operational decisions against current vendor documentation before production use.

SQL Server DMVs and DBCC: Monitoring and Consistency Checks

Dynamic management views provide state information; DBCC commands perform diagnostic, maintenance, and consistency-related tasks. Treat them as different tool categories that sometimes complement each other.

A read-only DMV example

SELECT DB_NAME(database_id) AS DatabaseName,
       num_of_reads, num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL,NULL);

Consistency checks

DBCC CHECKDB examines database consistency; DBCC CHECKTABLE examines a selected table. Both can consume significant resources and may require appropriate privileges. Plan the workload, monitoring, and follow-up.

Separate observability from repair

A report from a DMV does not by itself fix an issue. Some DBCC operations can change database state; never run a command from an old tutorial without understanding its exact effect.

Investigation workflow

Capture the error or performance symptom; identify affected database objects; gather version and state information; check documented maintenance procedures; and preserve backups before any invasive repair attempt.


AI content and reuse notice: Written using AI based on this site’s earlier post (22517), with added explanations and examples. The newly generated text may be reused, edited, or republished with or without attribution. Third-party works and trademarks remain subject to their own rights. AI can make mistakes. Check all code, version-specific features, and operational decisions against current vendor documentation before production use.

Windows Resource Monitoring for SQL Server: Reading System Signals Correctly

Operating-system monitoring reveals whether SQL Server competes for memory, CPU, storage throughput, or network capacity. Those readings are clues rather than complete diagnoses.

Windows tools

Task Manager offers an instant overview; Resource Monitor adds process and I/O detail; Performance Monitor (perfmon) collects named counters over time.

SQL Server context

Relate system metrics to Query Store regressions, active requests, wait categories, file-level I/O information, and blocking. High memory utilization is not necessarily a problem because the database engine uses memory for caching.

Collection method

Log metrics during healthy and slow periods with identical collection settings. Record workload, maintenance jobs, backup activity, and number of active application users.

Actions

Improve the verified bottleneck: tune a high-read query, address a blocked transaction, resolve resource contention, or adjust capacity only after measuring the impact.


AI content and reuse notice: Written using AI based on this site’s earlier post (22519), with added explanations and examples. The newly generated text may be reused, edited, or republished with or without attribution. Third-party works and trademarks remain subject to their own rights. AI can make mistakes. Check all code, version-specific features, and operational decisions against current vendor documentation before production use.

Custom Expressions and Code in SQL Server Paginated Reports

Report expressions solve many formatting and calculation requirements without custom code. Older SSRS tutorials also demonstrate embedding Visual Basic code in report definitions for shared logic.

Choose the right place for logic

Filter and aggregate meaningful business values in the SQL dataset; use report expressions for presentation; use report custom code only when neither is appropriate and supported.

Report expression example

=IIF(IsNothing(Fields!Discount.Value), 0, Fields!Discount.Value)

Embedded code considerations

Older SSRS authoring tools supported custom VB.NET snippets under report properties. Check support and code-access limitations in the exact report designer and server version before relying on legacy demonstrations.

Testing

Verify correct output when fields contain NULL, numbers are negative, cultures format decimals differently, and reports render to PDF or Excel. Parameter authorization must be enforced at the data source, not merely hidden in the user interface.

2025 note

Microsoft consolidates on-premises reporting into Power BI Report Server starting with SQL Server 2025; evaluate your target platform before following historical setup instructions.


AI content and reuse notice: Written using AI based on this site’s earlier post (22521), with added explanations and examples. The newly generated text may be reused, edited, or republished with or without attribution. Third-party works and trademarks remain subject to their own rights. AI can make mistakes. Check all code, version-specific features, and operational decisions against current vendor documentation before production use.

SQL Server Database Portability: Scripts, Imports, Exports, Backups, and Restores

Not all ways of moving SQL Server data accomplish the same thing. A schema script creates definitions, data export transfers rows, and database backup and restore captures a recovery-oriented image of the database.

Choose an appropriate method

Use migrations or database projects for controlled schema changes, export/import for selected data, and BACKUP/RESTORE for full database recovery or supported cloning scenarios.

Backup example

BACKUP DATABASE TrainingDB
TO DISK = 'D:\SQLBackups\TrainingDB_2026.bak'
WITH CHECKSUM, COMPRESSION;

Script review

Verify dependencies, sequence of object creation, logins versus database users, compatibility level, collation, cross-database references, and instance-specific settings.

Restore tests

A restore may require MOVE clauses for files, a compatible SQL Server version, permissions, and matching application configuration. Use a sandbox destination and never overwrite production unexpectedly.

Quality controls

Validate restored and imported data against source counts and business totals; test critical procedures; and document how to reverse the migration if checks fail.


AI content and reuse notice: Written using AI based on this site’s earlier post (23008), with added explanations and examples. The newly generated text may be reused, edited, or republished with or without attribution. Third-party works and trademarks remain subject to their own rights. AI can make mistakes. Check all code, version-specific features, and operational decisions against current vendor documentation before production use.