CODEWITHSUNDEEP
sql-serveralgorithmsql-server-2008design-patternsallocation
kbvishnu
kbvishnu

Reputation: 15630

Allocation algorithm for identifying the storage space

enter image description here

Refer the image

My Scenario

I1 and I2 are my SQL server instances which may be in same server or different server. Inside small boxes represents the databases inside the sql instances.

The number near to each box represent the amount of data of a person can store. ie . Database having 30 can able to store the data of 30 users. There will be n number of servers and instances and databases and this can be scale-able.

Capacity designed for I1 is 60(10+20+30).

We have another master database which the details of servers, instances and database. Using that we can prevent using a database with a bit field. So if I am stopping the database having capable of storing 20 in I1 users then the total storage of I1 will be 40

My Requirement

We need an algorithm which identifies the Server instance based on the available space. So that infrastructure team can able to load balance the traffic.

if limit of I1 is 30 , I2 is 20 and I3 is 10 Users will be allocated in the ratio 3:2:1. If 30 users are created, then I1 contains 15, I2 contains 10 and I3 contains 5.

How I can achieve this ? Any algorithm.

I need to achieve this in SQL Server 2008.

I am sorry for this long essay to describe my problem :(. Hope any one help me. Thanks in advance.

Upvotes: 3

Views: 238

Answers (2)

kbvishnu
kbvishnu

Reputation: 15630

All,

I solved this by using an percentage strategy.

It's too simple.

First time

Instance I1 (10+20+30)  -  60 (60 free spaces - 100% free)
Instance I2 (10+20+30)  -  60 (60 free spaces - 100% free)

A new user come , it will select the first one.

Instance I1 (10+20+30)  -  59(1 space is allocated) So percentage will be around 96%*
Instance I2 (10+20+30)  -  60(0 space is allocated) So percentage will be 100%

Another user 

Selects I2 because of 100% free.

Stopping a database of I2 ,which can able to store 30 users So total space for I2 will be 20+10 (30) and also the percentage free space will be reduced. So another user come, it will selects the free space available instance.

For eg: I have two instance I1 having capacity for 1000 users and I2 having capacity for 2 users.

Algorithm will allocate 1000 first (so I1 having 999 space free) and next user will be allocated to I2. So I2's percentage free will be 50%. So the next allocation happens when I1 reaches 50% (ie 500 users will be created to I1 then only I2 will use again).

Upvotes: 0

Zim-Zam O'Pootertoot
Zim-Zam O'Pootertoot

Reputation: 18148

A simple algorithm to do this is to use a random number generator and an if-else block

float db1upper = 10.0 / (10 + 20 + 30)
float db2upper = db1upper + 10.0 / (10 + 20 + 30)

float rand // [0, 1)
if(rand < db1upper)
    Use db1
else if(rand < db2upper)
    Use db2
else
    Use db3

You can update the values for dbNupper every X allocations and/or every X minutes to reflect users being deallocated or to reflect any bias in the random number generator

Upvotes: 1

Related Questions