Simple table-level partitioning

One of the simplest ways to think about data partitioning is based on tables. All tables in a given schema are divided into sets of tables. Each set of tables is created on a different server. Now to make all servers look uniform to the application, you need to provide access to all tables on all servers. This is where data partitioning can become an issue. One way to resolve this problem is to use synonyms. On a server, if you do not create physical tables because they belong to a set that was created on other server, then create a synonym on the local server. In this way, after schema creation is complete on all servers, the

application will be able to see all tables on all servers and can access data from the physical table from any server using the local table name. The application can connect to any server to access data and need not worry about the physical location of the data.

Let us look at an example. To simplify the test case, we consider a schema that consist of two tables: Customer and Item. In this example, we partition the data into two IDS servers. We first create two sets, with each set consisting of one table. The first set contains the table Customer and the second set contains the table Item. Each set of tables is created on one IDS instance (Figure 4-9).

Figure 4-9 Partitioning data at the table level

Figure 4-9 shows the location of the two tables after they were created on the Primary server of two IDS instances. The Customer table is created on Instance A and the Item table is created on Instance B. Because the Customer table was created on the Primary server, it was also automatically created on the

Secondary server of Instance A. The same applies to the Item table. At this time, the Item table does not exist on the Primary server or Secondary server of

Customer Item

Inst A (pri) Inst B (pri) Inst A (sec) Inst B (sec)

Item Customer

instance A, and the Customer table does not exist on the Primary or Secondary server of Instance B.

Create a synonym of Item on instance A using the following SQL command: Create synonym Item for store@g_srvB:Item

Then create a synonym Customer on Instance B using the following SQL command:

Create synonym Customer for store@g_srvA:Customer

After the two synonyms are created, the two servers will appear as shown in Figure 4-10. HDR does replicate the creation of a synonym to the Secondary server. So in Figure 4-10 you can see that the synonym Item also gets created on the Secondary server of Instance A, and it refers to the Item table of the Instance B Primary server. Similarly, the synonym of Customer is available on the Primary and Secondary server of Instance B. Now, the Primary server of both IDS instances A and B look the same to an external application. Any application can connect to either of the two Primary servers and access data, run a query, or perform any transactions, such as updates, inserts, and deletes. The

transactions will be maintained across servers because the two-phase commit is used if transactions span across servers.

Figure 4-10 Creating views for accessing distributed data

One of the important things to notice here is that the server group is used while defining a synonym. This ensures that when you access the synonym Item on Instance A, the query will be redirected to the server that is Primary in the group. It is interesting to see how a failover works in this scenario. Suppose, for

example, that the machine where the Primary server of Instance A and the Secondary server of Instance B was running fails. At that time, the only machine

Create synonym Item for:

store@g_srvB:Item Create synonym Customer for:store@g_srvA:Customer

Customer

Item Item

Customer

Inst A (pri) Inst B (pri) Inst A (sec) Inst B (sec)

Item Customer

Secondary database server of Instance B. So, the DBA can change the role of the Secondary server of Instance B and make it the Primary server, as shown in Figure 4-11.

Figure 4-11 Simple data partition - Failover

If clients are connected using a group, those that are accessing a database using the group name B, for example, will connect to the Primary server of Instance B on the first machine. In this figure, you can see that the client can access data transparently from any of the two servers on the machine.

What is interesting is that you can extend the schema design to multiple servers with very little effort. If two servers cannot handle the load, you can add a third instance of IDS and make HDR pairs. And you can decide then whether to use the HDR pairs in a Star or a Chain. You can also mix the schema design to a different topology, as discussed in 4.3, “Managing multiple instances with HDR” on page 82.