How to stretch /partition database in SQL server old version

 How to stretch /partition a database in SQL server old version

Choosing the Right Database Architecture

These days, there are many types of storage systems, server hardware, and database technologies available on the market. Often, consultants and management teams believe that only high-end solutions like Oracle or NoSQL databases can meet their business needs — leading to expensive and sometimes unnecessary implementations.

However, the best solution truly depends on real-world exposure, experience, and actual business requirements. In this section, we’ll explore how to design a powerful and scalable database architecture that supports high performance and data availability. Database design is the heart of any business application — it must be done right.

Traditional Database Design

• High-end database server with SSD/SAN/NAS storage.
• Requires advanced configuration and high-maintenance, often demanding more attention and longer processing times.
• Backup and recovery tasks can be complex and time-consuming.

Alternative Database Design & Partitioning Concepts Using Diverse Infrastructure

All examples below are based on a primary database with multiple partitioned or distributed databases:

1. Cloud Hosting: Multiple Databases on a Single Server

• Partition databases to maximize hardware usage.
• Design based on record volume, data importance, and key columns.
• Consider storage capacity and application performance requirements.

2. Cloud Hosting: Multiple Servers with Hybrid Storage

• Utilize multiple cloud servers using a hybrid storage model.
• Designate one primary and multiple child databases based on capacity and requirements.
• Leverage dynamic processing and storage scalability.
• Use linked servers with appropriate security to connect child databases to the primary.

3. On-Premises Hosting: Multiple Databases on a Single Server

• Single on-premises server with partitioned databases across multiple disks.
• One primary database and several child databases.
• Map database files to specific storage tiers (high/mid/low).
• Link tables via views or synonyms for unified access.

4. On-Premises Hosting: Multiple Stretch Databases Across Multiple Servers

• Partition databases to leverage multiple on-premises servers efficiently.
• Organize and access data through linked servers and data access libraries.
• Distribute data according to access priority for optimal performance.

Pros

• Small databases are easier to maintain than a single large database.
• Simplifies backup, recovery, and restore operations.
• Cost-effective — databases can be allocated to different storage tiers based on importance.
• Speeds up UAT/TEST environment refresh cycles.
• Improves server productivity and overall system performance.
• Facilitates easier configuration of replication, log shipping, and mirroring.

Cons

• Improper design and misconfiguration can be detrimental.
• Requires careful monitoring and more administrative effort.

Conclusion

As discussed, the database is the core of your application. Start by identifying requirements such as expected data growth, key entities, and referential relationships. Evaluate all possible solutions with their pros and cons. Collaborate closely with your technical team before implementation. In many cases, SQL Server technology alone can satisfy your needs — provided you understand its capabilities and limitations.