Project Title: SQL Server Backup Strategy and Dynamic Search Query Optimization for CratesL BoatManager CRM

Client: Brian Brinks, Canada

Problem Statement: The client required a reliable backup/duplication system between two SQL Server databases hosted on separate networks (shared hosting). Additionally, the client needed an advanced full-text keyword search feature across five CRM-related tables with custom filter logic based on user input patterns.

Objective:

1. a) Develop a script-driven method for duplicating live database content from Server A to Server B.
2. b) Implement robust full-text search across multiple tables returning distinct crmClientIDs.
3. c) Support advanced keyword handling with search filters like:

  1) “a-” (admin/all records),
  2) “t-” (tag-specific search),
  3) Default (user-specific general search).

1. d) Integrate search results with existing stored procedure outputs for consistency.

Tools & Technologies:

1. a) SQL Server Management Studio (SSMS)
2. b) Microsoft SQL Server (Shared Hosting)
3. c) Visual Studio (Schema Compare, Manual Scripting)
4. d) T-SQL / Stored Procedures
5. e) VPN for Secure DB Access
6. f) Dropbox (backup transfer)

Methodology & Execution:   Phase 1: Backup & Duplication Plan

1. a) Analyzed limitations of shared hosting (no replication/mirroring).
2. b) Used Visual Studio’s schema comparison to duplicate schema.
3. c) Manually inserted 2M+ records across key tables (e.g., stats) using batch operations.
4. d) Addressed schema conflicts due to different default owners (dbo vs custom).
5. e) Created a partial backup solution for lightweight synchronization via .bak files.

Phase 2: Keyword Search Stored Procedure

1. a) Designed a flexible procedure: Get_crmClientID_By_MultiSearch
2. b) Split input string into keywords using a table-valued function.
3. c) Searched across yellow-highlighted columns in 5 tables.
4. d) Ensured all keywords must match across any columns/tables.
5. e) Added keyword weighting logic to rank more relevant results.

Phase 3: Conditional Filters and Custom Prefix Logic

1. a) Created 3 distinct stored procedures:

1) Get_crmClientID_By_ApplicationNuserID_General 2) Get_crmClientID_By_ApplicationNuserID_A (admin/all records) 3) Get_crmClientID_By_Tags (tag-only search)

1. b) Input prefix (a-, t-) detected dynamically to redirect logic.
2. c) Returned full result sets based on original CRM stored proc s_CRMGetClientListSortedByDateLastEdited.

Results:

Metric	Before	After
Redundancy Strategy	None	Manual Scheduled Script
Search Response Time	>10s	<1.5s
Supported Keyword Search Depth	1–2	Unlimited
Query Accuracy	Low	98%+
Total Development Time	~20 hours
Total Billed	$200 USD

Key Challenges:

1. a) Schema inconsistencies across shared-hosted databases.
2. b) Missing full control due to no FTP access to SQL directories.
3. c) Text-search complexity across multiple tables without full-text indexing.
4. d) Dynamic parameter handling for filtering by user role and tag conditions.

Outcome: Successfully implemented a semi-automated database backup solution and built a high-performance SQL search engine for CRM data with multiple filtering modes. The solution was modular and scalable for future enhancements. Client was satisfied and compensated the full amount with the intention of long-term collaboration.

Tags: SQL Server, T-SQL, Database Replication, Stored Procedure, CRM, Search Optimization, Backup Strategy, Shared Hosting, Keyword Matching, Data Integration