Description
Scaling Out SQL Server are the things that built into SQL Server and these features are useful for the user to scale out SQL Server that are available right in the product itself. The first one is always on the availability group replicas and transnational replication that occurs in peer-to-peer replication. The distribution partition views are the concepts that's not actually a product feature called as service-oriented database architecture.
Description
Availability Groups Replicas are the new features that was added back in SQL Server 2012 and enhanced in SQL Server 2014 that are basically a hybrid between traditional fail over clustering and database mirroring.The availability groups scale out perspective up to eight read only secondary replicas are available in SQL Server 2014.Which allows segregation of read only workload to different servers that requires application changes to fully leverages and contain additional core licenses.
In availability group replicas, which doesn't allows a secondary replicas schema in which it will create temporary statistics that are created in tempdb. And this available group replicas may replace transnational replication for reporting usage because no schema changes are allowed on secondary replicas and it will creates additional stress on the network and storage infrastructure to keep secondary replicas that are synchronized.
- SQL Server Instance - Contain its own copy of the server files.
- Node - Is a network connection point for data transformation.
- Primary Replica - Is the primary sending transaction log records.
- Secondary Replica - Include support for read only access to one or more secondary files.
Description
Another build-in feature in scale-out purposes is transactional replication. But basically data is copied between multiple servers and synchronized and the source database is said to be publisher. And that are read only copies of getting pushed out from that publisher. And there are different ways of performing transnational replication based on push subscription or pull subscriptions. And contains a distribution database in the middle, but if thee user set this up correctly then it is good for scalability.
The publisher database is the only one that's writable and then user have many subscribers databases on different servers and each one of those has to be licensed, but spreading the workload out among multiple servers and as only one read only portion that gives some scale out capability built into the product. There transactional replication is typically setup based on the application of the publisher database. The publisher database is read and write and can be replicated based on the entire database tables and columns that are even replicated when subscribers. And all the replicated data gets moved there temporarily by the log reader agent and distribution agent will pick up the changes from that and pushes them out to the subscribers from the distribution database.
Description
Peer-to-peer replication is another feature that built into SQL Server that gives some scale-out capability. Peer-to-peer replication gives the read/write capability on each node. And this is the feature that really describes the SQL Server.
It's based on transactional replication but it requires Enterprise edition that are not actually required for regular transactional replication. And it's considerably more difficult to set up and maintain the basic transactional replication. And generally gives more scale-out for read operations rather then write operations. And all the replication will gradually synchronize and will probably have a few seconds lag depending on the network infrastructure and storage infrastructure that contain these copies of the database will gradually be in sync with each other within a few second in most cases.
Description
DDR is an engineering technique used for horizontal partition on the database.It is also called as "sharding".
- Monolithic database splits into multiple databases on different servers.
- Application knows how to find data if needed.
- Resources must be balance as load changes and grows.
- This engineering and development are more difficult than soda,and once it is performed the database server
- will choose another.
- Use a hashing mechanism to allocate data between servers.
- Scaling out SQL Server with Data Dependent Routing.
Key Points
- Scaling Out SQL Server - Are the built-in features of SQL Server database.
- Availability groups - Newer features in SQL Server.
- Transactional replication - Built in features for scale out.
- Peer-to-peer - Gives out the scale out capabilities of the server.