Our real estate portal platform serves 150 client websites through a shared SQL Server 2008 database, originally designed for 10-20 clients. The current architecture exhibits these critical issues:

-- Example of current table structure
CREATE TABLE Listings (
    ListingID INT PRIMARY KEY,
    ClientID INT,  -- Foreign key to Clients table
    PropertyData XML,
    LastUpdated DATETIME
    -- ...50+ other columns
);

Key performance symptoms:

• UPDATE operations on one client's 800 listings (80% of 1,000) create lock contention
• DELETE operations for client offboarding cause full table scans
• Hourly data feeds trigger unpredictable latency spikes

Option 1: Schema Per Client

-- Implementation example
CREATE SCHEMA client_123;
CREATE TABLE client_123.listings (
    ListingID INT PRIMARY KEY,
    PropertyData JSONB,  -- PostgreSQL example for modern systems
    TSVECTOR tsvector  -- For full-text search
);

Pros:

• Logical isolation with shared connection pooling (20% connection reduction)
• Single backup/mirroring pipeline
• Schema-specific security policies

Option 2: Partitioned Tables

-- SQL Server partition function
CREATE PARTITION FUNCTION client_part_func (INT)
AS RANGE RIGHT FOR VALUES (100, 200, 300...);

CREATE PARTITION SCHEME client_part_scheme
AS PARTITION client_part_func
ALL TO ([PRIMARY]);

Performance test results:

Operation	Single Table	Partitioned
UPDATE 800 rows	2.4s	0.7s
DELETE client	9.1s	0.3s (partition switch)

For your 150-client scale, I recommend this combined approach:

-- 1. Client-specific schemas for core data
CREATE SCHEMA client_data_123;

-- 2. Partitioned logging/audit table
CREATE TABLE dbo.ClientUpdates (
    UpdateID BIGINT IDENTITY,
    ClientID INT NOT NULL,
    UpdateTime DATETIME2 DEFAULT SYSUTCDATETIME()
) ON client_part_scheme(ClientID);

-- 3. Cross-client view with security predicates
CREATE VIEW dbo.vw_secure_listings
WITH SCHEMABINDING
AS
SELECT * FROM client_data_123.listings
WHERE ClientID = CONVERT(INT, SESSION_CONTEXT(N'ClientID'));

Phase the transition over 4 weeks:

1. Week 1: Implement partitioning on logging tables
2. Week 2: Migrate top 20% active clients to schemas
3. Week 3: Implement row-level security (RLS)
4. Week 4: Finalize monitoring dashboards

Sample PowerShell migration script:

# Client schema migrator
$clients = Invoke-SqlCmd -Query "SELECT ClientID FROM dbo.Clients"
foreach ($client in $clients) {
    $schema = "client_data_$($client.ClientID)"
    Invoke-SqlCmd -Query "CREATE SCHEMA $schema"
    # Data migration logic here...
}

---

Our real estate portal platform currently hosts approximately 150 separate websites sharing a common template, each serving different clients. The current SQL Server 2008 implementation uses a single database with shared tables for all clients, which creates significant performance bottlenecks:

• Hourly data updates (affecting ~80% of 1,000 listings per site) cause cross-client performance degradation
• Data deletion operations impact unrelated client sites
• Initial architecture designed for 10-20 sites now struggles at 150+

Let's examine the technical trade-offs between different approaches:

Option 1: Multiple Databases

Pros:

-- Example of client-specific database creation
CREATE DATABASE ClientABC_Realty;
GO
USE ClientABC_Realty;
-- Standard schema implementation
CREATE SCHEMA listings;
CREATE TABLE listings.properties (
    property_id INT PRIMARY KEY,
    -- Additional columns
);

Cons:

• Complex backup/restore procedures (150+ individual databases)
• Mirroring/AlwaysOn configuration complexity
• Cross-database query challenges

Option 2: Single Database with Multiple Schemas

Implementation example:

-- Schema-per-client approach
CREATE SCHEMA client123;
CREATE TABLE client123.properties (
    property_id INT PRIMARY KEY,
    -- Standardized column structure
    last_updated DATETIME2,
    -- Additional client-specific columns if needed
);

-- Partition function for potential hybrid approach
CREATE PARTITION FUNCTION pf_clientID (INT)
AS RANGE RIGHT FOR VALUES (1, 2, 3 /* ... up to client IDs */);

Performance considerations:

• SQL Server handles schema separation at metadata level
• No automatic resource isolation (memory, IOPS still shared)
• Simpler maintenance than multiple databases

Option 3: Partitioned Tables

Hybrid approach combining schemas with partitioning:

-- Partitioned table example
CREATE PARTITION SCHEME ps_clientID
AS PARTITION pf_clientID
ALL TO ([PRIMARY]);

CREATE TABLE dbo.all_properties (
    client_id INT NOT NULL,
    property_id INT NOT NULL,
    -- Common columns
    CONSTRAINT pk_all_properties PRIMARY KEY (client_id, property_id)
) ON ps_clientID(client_id);

For our specific scenario with 150+ clients and heavy update patterns, I recommend a hybrid approach:

1. Implement schema-per-client for logical separation
2. Use filtered indexes for client-specific queries
3. Apply resource governor for critical clients

Example resource governor setup:

-- Create workload group for high-priority client
CREATE WORKLOAD GROUP ClientA_Group
WITH (
    MAX_DOP = 4,
    REQUEST_MAX_MEMORY_GRANT_PERCENT = 25
);

-- Classifier function
CREATE FUNCTION dbo.rgClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @group SYSNAME;
    IF (APP_NAME() LIKE '%ClientA_Portal%')
        SET @group = 'ClientA_Group';
    RETURN @group;
END;

Migration strategy for existing data:

-- Example migration script
BEGIN TRANSACTION;
-- Create new schema
CREATE SCHEMA client_migration;
-- Create new table structure
SELECT * INTO client_migration.properties 
FROM dbo.properties 
WHERE client_id = 123;
-- Verify data
-- Drop old records
DELETE FROM dbo.properties WHERE client_id = 123;
COMMIT;

Key performance metrics to monitor:

• Page splits/sec during hourly updates
• Lock wait times during client deletions
• TempDB contention during schema operations