How to stretch /partition database in SQL server old version

This days their are different kind of storage and server hardware software are available in market. Many consultant and management team are believe that only Oracle and NoSql database are satisfy their requirements and they recommended costly solution for their businesses. But It depends on people exposure in real worlds. Here i am explaining some thing which help to choose powerful back-end architecture for your business application. Database designing is a hart of application. Its work like hart of business application. Your design should be scalable and powerful in context of performance and availability of your data.

Traditional database design

Traditional Database design concept
  • High configuration database server with storage( SSD /SAN /NAS)
  • High configuration server require simple steps to do maintenance but need much concentration and long processing time
  • Tough to do backup / recovery transfer kind of operations

Different concept of database design & partitioning using verity of server infrastructure

All example contains one primary and multiple partitioned databases

  1. Cloud hosting multiple database using single server
on cloud hosting single server with multiple partition databases
  • Making database partition in such a way that we can take maximum hardware benefit.
  • Consider large volume record & size / Importance of data/ key columns
  • available storage/ performance of application need to take care too.

2. On cloud hosting multiple server with Multiple database using hybrid storage

on cloud hosting multiple server with multiple partition databases
  • On Cloud multiple server stretch database using hybrid concept of storage
  • Should have one primary database and multiple child databases host as per server configuration and requirement
  • Hybrid Server have dynamic storage /processing power as per requirement and budget
  • Each database directly linked with primary database and their hosting server using linked server with respective security

3. On premises hosting multiple database using single server

on premises server with multiple partition databases
  • On Premise single server with stretch/partition database using multiple disk storage
  • Should have one primary database and multiple child database
  • Database file point to respective storage (High/ mid / low)
  • All respective table linked with primary database using view or synonyms

4. On premises hosting multiple stretch database with multiple server

on premises multiple server with multiple partition databases
  • database partition in such a way where we can use multiple server on premises.
  • organized data storage and access data using linked server and data access library
  • we can organized data storage according to require data access priority

Pros

  • Small databases easy to maintain compare to larger database
  • Easy to take backup /restore/ or recovery operations
  • Make it cost effective by moving database as per their priority into different type of storage
  • Make UAT /TEST environment faster Refresh
  • Make server more productive and performance efficient
  • Easy to configure replication, log shipping, mirroring

Cons

  • Wrong designing and configuration make it destructive.
  • Need to observe multiple task as a administration.

Conclusion

As I Discuss Database is a hart of application you should list out all your requirement, estimation of data growth, key columns, referential data etc. Point out all available solution with pros and cons. Discuss all those things with technical team before implantation. Most of all requirement will be satisfy your design using SQL Server technology but you should aware of the technology limitation and power.

Please follow and like us:
error

Leave a Comment

Your email address will not be published. Required fields are marked *

error

Enjoy this blog? Please spread the word :)