Database Torque

Mount Additional Disks To CentOS/Linux

2020-07-31T20:09:00.000-07:00

When you create CentOS VM with additional disks, you need to do additional steps to mount them to the file system. This blog post describes the steps on how to do it.

First step is to identify the unmounted disks. You can use lsblk -l command as below:

You can see sdc device is not mounted to the file system. The MOUNTPOINT column is blank for sdc device. Let's see how do we mount it to the file system.

First, you need to format the disk using mkfs command as below: The ext4 is the filesystem type.

The device name you have to give it as /dev/sdc. Formatting the disk is now complete and let's mount the disk. I want to mount the disk as pg_wal name. This VM is going to host EDB Postgres instance and I want this 15 GB disk to keep the WAL files.

Let's create the mount point.

sudo mkdir /pg_wal

The final step is to mount the disk. See below command.

sudo mount /dev/sdc /pg_wal

The new device is now successfully mounted.

The OS I used in this example is CentOS 8.0

Hope this helps.

PostgreSql: How to setup Pgbouncer

2020-07-01T21:27:00.003-07:00

PostgreSql needs a middleware program to handle application connections also known as connection pooling. One of the most common such middleware programs is called, Pgbouncer. This blog post will highlight the changes you need to make to setup Pgbouncer so that application can connect to PostgreSql via Pgbouncer.

I tested this with Postgres 12 and Pgbouncer 1.13.0 version on CentOS 7. In this scenario, pgbouncer and Postgres are running on the same server.

Once you installed Pgbouncer, the configuration files are available on /etc/pgbouncer dir. Pgbouncer config file is, pgbouncer.ini.

Get username and password

Before you start changing the config file, you need to find out Postgres user(s) and the database(s) which you need for the connection pooling. In Postgres cmd prompt(psql), execute the code below to get those info.

select usename, passwd
from pg_shadow;

You need both username and the passwd (please note, this password is based on md5) If you need more secure password, you'll have to follow few additional steps. I will get back to that later in this post.

Edit Pgbouncer.ini

Open Pgbouncer.ini file in a text editor (vi). Make the changes as shown below; Assume the database name is testdb and the username is testuser.

[databases]

testdb = host=127.0.0.1 port=5432 dbname=testdb user=testuser

auth_type = md5

listen_addr = *

listen_port = 6432

auth_file = /etc/pgbouncer/userlist.txt

Note: 6432 is the default port of pgbouncer.

Adding Users

Create userlist.txt file and add username and password in the file as stated below;

"testuser" "md590828d6f73b12acc3866c26968bde150"

Add all the users in this file which connects from the applications.
At this point, the very basic setup of Pgbouncer is completed. You can restart pgbouncer service and probably the Postgres service too.

systemctl restart pgbouncer
systemctl restart postgresql-12.service
How To Test
You can use psql to test pgbouncer setup like below;
psql -h 127.0.0.1 -p 6432 -U testuser testdb

SCRAM Secrets

Pgbouncer 1.14 version (Jun-11,2020) supports encrypted SCRAM secrets for enhanced security. So that you can avoid use of md5 or plain text passwords. I found below link very useful to implement encrypted passwords.

How to Upgrade Your PostgreSQL Passwords to SCRAM

This is just one method of configuring pgbouncer. There are few more ways to configure pgbouncer.

SQL Server AlwaysOn: How to promote a secondary replica to primary role?

2020-06-04T11:40:00.000-07:00

Think about a situation where you got a SQL Server Always On cluster with three nodes where one is the primary node and the other two are secondaries. The failover mode is set to manual for all three nodes. Now suddenly your primary has gone down and you need to make one of the secondaries a primary node.

You can immediately notice that all the databases which are part of the AG are in the "Not Synchronizing" state in all the sync secondaries. This is expected because the primary node is not available.

Let's see how to manage this situation.

You need to connect to a secondary node where you want it to be the primary and then execute the following T-SQL command:

ALTER DATABASE [db_name] SET HADR RESUME

Apply this command for each database in the AG. You will notice the databases are now showing as "Synchronized" in SSMS. Yet, there is no primary.

While being connected to the same secondary, issue the following command to make it the primary.

ALTER AVAILABILITY GROUP [AG-NAME] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Note: Just replace the AG-NAME with the actual AG name.

Now, the cluster has a primary node and your application can resume working.

I just explained here a hypothetical situation but the same thing can be applied in a real situation where you're primary node had failed or it could not be failed over due to some reason. The most appropriate solution at this point is to promote one of the secondaries to a primary role, as described in the blog post.

PostgreSQL: How to find the location of Postgres error log?

2020-05-14T14:05:00.000-07:00

I'm kind of new to Postgres. However, I'd like to work on Postgres as often as I could because that is how you can learn.
Recently, I had this opportunity to troubleshoot an issue in Postgres. I wanted to check the Postgres error log as a starting point. I spent some time to figure out where the error log is. This was in CentOS 7.0. I initially thought it would be in /var/log directory because it is the most common location of the error logs. The messages (CentOS) error log was in /var/log but nothing was related to Postgres logs in there.
After that, I did some Google search and found out you can get the Postgres configuration data from psql command prompt itself.

For example, if you want to check the Postgres config file location you can just type "SHOW CONFIG_FILE" in psql and it gives you the absolute path of the config file. See the below screenshot.

Correspondingly, you're able to check the configuration options specified in the conf file using the SHOW command.

In order to see the error log, you can use, "SHOW log_directory" command which gives you the log file location. See below screenshot;

However, it did not give me the absolute path in this case rather I'd think it gave the relative path. Of course, these are the values you've given at the time of installation or afterward in Postgres and then they are stored in the config file. IMO, it is always a good habit to specify the absolute path wherever possible.

So the question remains the same, how do we find out the error log path?

Next, I checked the data directory location by using the command, SHOW data_directory and it gave me this;

Good starting point. It is an absolute path. I thought, maybe, the "log" in the log_directory output could be the relative path to the data directory and with that in mind, I checked this path, /var/lib/pgsql/12/data/log. There you go. Finally, that is where the error logs are located in Postgres.

The Postgres version of this experiment was done against, PostgreSQL 12.1.

Cheers!

SQL Server query hints which are not much popular but very powerful

2020-04-12T17:30:00.000-07:00

I learned some new query hints which we can use when troubleshooting query performance issues. Those are mentioned below;

Option (force order, hash join) - forces the query to use follow the join order as it specifies in the query and also makes all the joins as hash joins

Option (Use hint ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')) - to generate a query plan using the simple containment

Option ( Hash join) - to eliminate the merge join

Option ( force_Legacy_cardinality_estimation) - Force to use the legacy cardinality estimator

Option (Use hint ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS')) and (Force order ) - combine two query hints

References
Join containment assumption in the New Cardinality Estimator degrades query performance in SQL Server 2014 and later

Update introduces USE HINT query hint argument in SQL Server 2016

Why use AWS auto scaling group to deploy Couchbase clusters

2020-04-11T18:29:00.000-07:00

Initially, it may sound odd and strange idea because someone can argue autoscaling group concept is
for stateless applications like application servers, web servers, etc. Maybe that was the initial idea when introducing the concept, however, modern distributed databases are too, a good fit for autoscaling groups and we can get multiple advantages by using this concept. Like in stateless applications, we do not have to use autoscaling policies to kick off autoscaling activities for databases instead we can manage the autoscaling groups manually to trigger autoscaling activities in much more controlled fashion though we can use autoscaling policies too. For example, if we want to add a node to a Couchbase cluster, we can increase the desired capacity of the relevant autoscaling group by one so that an identical EC2 instance will be spun-up and join the cluster and rebalance automatically. (The user data section of the launch configuration has all the logic to install, configure, join and rebalance the new node to the cluster.) We can do this during a low usage time of the
system so that data streaming will have minimal impact on the application performance.

Another advantage is, the new instance(s) will be balanced across availability zones automatically because of the autoscaling group thus no additional logic required. Please note that balancing the nodes across availability zones (AZs) is crucial for distributed databases for many valid reasons like rack awareness and recovery from disaster recovery in case of an AZ failure.

In case of a node failure, due to hardware or software issues, a new node will spin-up to match the minimum number of nodes of the autoscaling group, thus no human intervention is necessary at all: - auto-healing.

However, downsizing a database cluster may not be a good idea and will not happen so frequently but
there are certain situations that we need to shrink the database cluster. So we can still have the downsizing functionality so that we can use it for the non-prod environment to adjust the AWS cost or to properly size the cluster. In a nutshell, the use of the autoscaling group is a very good idea for highly distributed databases like Couchbase and Cassandra and it gives us excellent features, otherwise, we need to program for all these logic- reinventing the wheel.

Connect and query PostgreSQL using Azure Data Studio

2020-01-16T14:59:00.000-08:00

Azure Data Studio is a new cross-platform database tool developed by Microsoft. It runs on Windows, macOS, and Linux. You can use the same tool to connect to PostgreSQL servers as well.

If you're new to this tool, you can download and install it. You need to install the Postgres extension for the Azure Data Studio to connect to PostgreSQL servers.

Figure-1: Connection Page

Figure-2: Server details

Please note that if the PostgreSQL is the remote server then you need to make sure postgres is accepting the remote connection and also it allows your machine IP. You can change the listen_address property of the postgresql.conf file to '*' to allow remote connections and also you need to add your machine's IP address in pg_hba.conf file. When adding the IP address it has to be according to the CIDR notation, for example, 192.168.0.15/24.

If you do not perform the above config changes the errors below will be typical.

could not connect to server: Connection timed out (0x0000274C/10060)
Is the server running on host "172.99.234.99" and accepting
TCP/IP connections on port 5432?

FATAL: no pg_hba.conf entry for host "10.52.99.99", user "postgres", database "postgres", SSL off

References:
What is Azure Data Studio
Quickstart: Connect and query PostgreSQL using Azure Data Studio

Challenges with very large databases

2019-12-11T17:27:00.003-08:00

We as DBAs always like to work with very large databases (VLDB) because it gives us lot of experience and our resume looks great too. :) But in reality, managing of VLDBs have tremendous challenges. Recently I've been asked to provide some points alone with this topic.

As of this writing, the latest version of SQL Server released to RTM is SQL Server 2019.

These points are given for SQL Server but in general they applies to any database systems.

We need to scale the databases when it gets large amount of data in order to server the applications efficiently. There are two ways to scale a database system. They are;

Horizontal scaling also call it scaling out
Vertical scaling

SQL Server natively support vertical scaling only, meaning we have to put more CPU, memory and make powerful machines that is why large databases require powerful hardware to run to meet its performance expectations. However, there is a limit for vertical scaling. That is the reason modern databases are being built to support scale out (horizontal scaling) methods. Even in Azure SQL Database supports horizontal scaling but not SQL Server.

What are the challenges with large databases?

Increased recovery time. In the event of a disaster, it takes more time to recover the database using backup restore.
Db maintenance tasks become more difficult. Such as index rebuild, index reorganization, update statistics, etc. These db maintenance tasks are necessary to keep the database performance at higher level.
Increased database backup times. As part of Disaster Recovery (DR) plan, any database system needs to be backed up. SQL Server has three different backup types, full, diff and t-log backups. With database growth, it increases the time it takes to take a full / diff backups. Taking backups will not block database activities but it consumes resources specially CPU.
Index rebuild becomes almost impossible for very large tables because of the time it takes and the blocking it creates. SQL Server has online rebuild option but that has a penalty at tempdb usage. Therefore we can notice poor query performance.
Even update statistics with full scan takes hours for very large tables. Update statistics is less resource intensive operation but it creates blocking and therefore it impacts the application performance. Due to this reason we have to find a suitable maintenance window to this operation. Maintenance windows are becoming very strict due to business reasons so we can’t just have several hours of maintenance window for the DB stuff.
If the database in question participates in transactional replication, in the event of replication rebuild, it takes ages to transfer initial data set to the subscriber. We can use backup / restore method at the subscriber as an alternative option to initialize, however it takes sometime days to copy the backup to the destination server and then restore takes hours. Due to this reason, there will be a high business impact during a replication rebuilding event.
Creation of new indexes takes time and resource intensive in very large tables. There are certain situation that we need to introduce new indexes to improve query performance but it requires careful planning and certain time window to make that change to minimize the impact for the applications.
Table level schema changes, such as adding a new column, data type change, etc takes lot of time and need careful planning for very large tables. We also need to manage the transaction log during such an event which needs lot of effort and attention from DBAs.

All the points stated about are the challenges that I see with very large databases.

Some people argue that we can use table partitioning to manage VLDBs. However that does not go away the challenges stated above.

Here is my answer to the table partitioning is not the solution. Table partitioning at the storage level only. But still the compute and memory used for a partitioned table is the same. It has to use same machine’s (host) memory and cpu plus other resources such as network to process the portioned data. Partitioning data will help to improve query performance because then it will access only a subset of data for the query, assuming portioning is done accurately but that does not solve the maintenance nightmare we face with the data growth.

However, table portioning is not a scaling methodology. In actual scale out solution, it will partition data as well as other resources including cpu , memory, network, etc by adding additional nodes. Very good example is the Cassandra cluster. We can keep on adding nodes to the cluster as data grows and each node of the cluster is assigned a portion of data. So that data portion has its own compute, memory and other resources. Hope its clear.

SQL Server has another feature called Stretch Database. This feature helps here greatly because you can offload subset of data (cold data) to the Azure cloud. As long as you're not frequently using the data offloaded to cloud, you can deal only with the operational data set which is great.

Cheers!

SQLSaturday #908 - Denver

2019-10-15T13:24:00.001-07:00

I completed another SQLSat session this week at Denver. It was pleasure to see about 20 folks for my presentation about SQL Server on Kubernetes. I’m really happy to see several sessions around containers which is really good. DBAs are not stepping into this new technology era of virtualization to run SQL Server or any other database workload on kuberneres.

https://twitter.com/denversql/status/1183116130073894913?s=09

How to setup AWS Application Load Balancer for Couchbase

2018-04-04T10:39:00.001-07:00

Introduction

You might be wondering why we really need a load balancer for Couchbase. Well, that is a reasonable question to ask and I thought of clarifying this matter before we move further into the topic.

The objective here is NOT to use a load balancer to perform its noble task which is to distribute application traffic. The load balancer uses here as a tool to help manage connection string of the Couchbase. At the end of this article you will understand how to manage the Couchbase connection string in fully automated fashion.

How client applications work with Couchbase

Let's go through a brief description on how client applications work with Couchbase server.

Any application that connects to Couchbase has a connection string which consists of one or more cluster nodes. Since Couchbase is a distributed database, a typical production deployment of Couchbase has multiple nodes in a cluster. As a result you need to provide one or more such cluster node IP addresses or host names to the connection string. It is not required to provide all the cluster nodes to the connection string, however it is often best practice to specify at least two host names because if the application could not connect to one, then it will try the next node.

After establishing the connection to Couchbase, the smart client receives a cluster map from the Couchbase. In nutshell, cluster map has the information about Couchbase nodes, data distribution of vBuckets, etc. From that point, smart client knows where to read/write data precisely. This process is called client bootstrap.

However, overtime, Couchbase cluster may shrinks or grows depending on the application load. If that happens, the cluster nodes will be changed. So then what will happen to the connection string you created at initial cluster setup? Those information is not fully accurate now? Of course you could update the connection string manually but in automated environment any manual work would be considered a cheap thing.

To resolve this or let the client application knows the latest cluster nodes with no human intervention, you could use AWS application load balancer.

Typical role of application load balancer

AWS Application Load Balancer-ALB, is one of the new services of their service arsenal. Load balancer typically used as a single point of contact for clients and it distributes incoming application traffic across multiple targets, such as EC2 instances. Well, as we know how applications connecting to Couchbase cluster, we understand the typical use of the load balancer will not exactly fits in this context to distribute the database traffic because to distribute the database traffic, the load balancer knows nothing about the Couchbase.

Use of Application Load Balancer in Couchbase

The following diagram illustrates the basic architecture of ALB used in Couchbase.

Create ALB for Couchbase cluster

It is now common that Couchbase cluster deploys on AWS autoscaling groups which provides lot of benefits and automation friendly environment. Let's go through step by step process on how to create an ALB for Couchbase.

Assume you already have a Couchbase cluster deployed on AWS autoscaling group.

1. Create an ALB

I'm not going to repeat the steps in AWS docs here. Please follow the link given below to start creating the ALB and I will highlight the things we need to know for Couchbase context while creating it.

Getting Started with Application Load Balancers

In Basic Configuration there are two schemes you can select as seen below;

It's your choice to expose Couchbase admin UI to internet or internal. Depending on that select the Scheme. Let's assume we selected "internet-facing" scheme.

In Availability zones, select your VPC and the subnets that you need to place the ALB. For internet facing scheme, you need to place your ALB in subnets where you have internet access (Internet Gateway). You need to select more than one subnets for high-availability purposes.

2. Create security group

Follow the steps as it is in the page. Recommended rule are given in below link;

Recommended Rules

Please note, since our ALB is internet-facing, the Inbound Source of the security group should be "0.0.0.0/0".

3. Create target group

In Configure Routing section, please make sure you change the highlighted section as below;

4. Register target

Remember that our Couchbase cluster runs on AWS autoscaling group. So that we need to register autoscaling group as one of the targets for ALB. In this section you could register individual instances too but that is not what we need here. So just skip this section for now and click on Review.

Review all the settings and if accurate click on Create.

At this point, you have your ALB created. However still your target group is empty because we have not yet registered any EC2 instances to the target group. We will do that in the next step.

5. Register autoscaling group as the target

Select the autoscaling group which has Couchbase server deployed.

On Details tab, select Edit.

Select the target group which you have just created in above. See screenshot below;

Then click on Save.

6. Verify target group

As soon as you finished with step #5, please switch over to target groups under Load Balancing and select the target group. e.x: test-tg-couchb

On Targets tab you can now notice the Couchbase cluster EC2 instances are registered. See below screenshot.

Since the Couchbase cluster as single instance (in this example), it shows just one instance. In the same section you also can see health check is passed which means ALB is able to communicate with the Couchbase server.

7. Access Couchbase server admin UI

Remember that we created internet-facing ALB so that we can now access the admin UI using the ALB DNS name.

Just copy and paste this URL into the browser to access the Couchbase UI. Make sure the security group you created for the ALB is accepting traffic from internet.

See below that the security group is open to internet traffic.

8. Enable sticky session

If you are having trouble accessing the Web UI then do one more change in the target group to enable the sticky session. This is specially required when you have multiple nodes in the Couchbase cluster.

Select the desired target group. Under Attributes on Details tab, Edit attributes to enable the sticky sessions as shown below;

Then try the Couchbase Web UI once again.

Setting up Route 53

Earlier we just tried Amazon internally generated URL of the load balancer to access the Couchbase Web UI. Most often when we design things we need to do it neatly. It is always better to have customized URL for each Couchbase cluster so that it is easier to remember as well. You could achieve this by creating a public or private Route53 entry and then point that to the ALB you just created above. You can refer the link below on how to setup Route53 for the load balancer.

Routing Traffic to an ELB Load Balancer

How many load balancers you need?

Well, the load balancer has a cost associated with it. Whatever the cloud design you come up with should also need to consider the cost aspect of it too. Having a load balancer for each Couchbase cluster is definitely expensive but thanks to the new ALB, now you could use one ALB to connect to multiple Couchbase clusters using path-based or host-based routing strategy. Below section describes the routing strategy and how to configure it.

Routing strategy

In Amazon web console, select the ALB that you created above. Select the Listeners tab of the load balancer's properties. You could see something like below;

Which means, the load balancer we created has just one listener. Notice the Rules section. Just click on View/edit rules. You could see the Rule Editor like below;

Look at that, we can define rules for ALB. Even though we did not create any explicit rules, there is a default rule created and it is associated to the target group (test-tg-couchb) we just created.

In here, you can add/modify/delete rules. Let's assume you have another target group which is pointing to a different Couchbase cluster that runs on another autoscaling group. Let's call that target group as test-tg-couchb2. Assume that you also created a Route53 entry which pointing to the same ALB that we created above. Let's call that Route 53 entry as couchbase.couchb2.com.

By utilizing a listener rules as shown above, you can tell the listener to divert all the incoming calls to couchbase.couchb2.com to the target group, test-tg-couchb2. Refer the screenshot below;

Let's backup a little bit and try to understand what we just did.

We just added a new listener rule for the same ALB. The new rule is host-based one and it diverts all the incoming traffic to couchbase.couchb2.com Route 53 DNS name to the new target group called, test-tg-couchb2 which essentially pointing to a different Couchbase cluster runs on autoscaling group. That is amazing right.

Refer the links below for more details about Listener Rules.

Listener Rules for Your Application Load Balancer

Couchbase connection string

Now you have setup stable Route 53 DNS name for your Couchbase cluster. The next step is to use that DNS name to connect to your Couchbase cluster just to be clear, we are not going to use the DNS directly to bootstrap the client SDK. We have already seen how it can be used to connect to the Couchbase admin UI.

Below mentioned is the python code snippet that you could use to get latest Couchbase nodes and then use those nodes to build the connection string as you would do it normally. Now you do not have to worry about adding or removing nodes to your Couchbase cluster because there will be no change in the connection string. Everything will be taken care by the ALB.

url='http://couchbase-couchb2.com/pools/nodes'

u = 'admin'

p = 'Ch@ng3m3!'

response = requests.get(url, auth=(u, p))

nodes = json.loads(response.text)['nodes']

hosts=""

for n in nodes:

hosts = hosts + ',' + n['hostname']

hosts = hosts.lstrip(',')

print(hosts)

couchbase://hosts

Summary

In this article we just went through how to use Amazon Application Load Balancer to manage connection string of the Couchbase cluster. We also learnt, how to use single ALB to connect to multiple Couchbase clusters to reduce the cost. While there are many solutions available to handle connection string, you would find this solution as fully automated and easy implementation. Hope you learnt something new.

However Couchbase recommended method is to use DNS SRV record which is described below;

Managing Connections

While the DNS SRV method is efficient but it has some management overhead because you need to do some extra programming to manage the DNS SRV records as you add / remove nodes in the cluster. I still believe Couchbase has no developer friendly method to manage connection string in Cloud environment. In my opinion this is something Couchbase needs to implement very soon rather than later.

Couchbase: Bucket (Database) Architecture

2017-11-16T14:17:00.001-08:00

Introduction

In Couchbase, a bucket is similar to a database in other DB systems. Application inserts data directly into a bucket(s) and there is no any other schema or objects inside it. A bucket contains documents.

Rack awareness feature in Couchbase

2017-11-08T10:44:00.001-08:00

In distributed database systems, data is distributed across many nodes. If you consider Cassandra, it is not uncommon to have a cluster (in Cassandra terms, a ring) with 1000 nodes or even more. These nodes are then grouped into different racks, in cloud terms, different availability zones. The reason is, in the event that a whole rack (availability zone) goes down since the replica partitions are on separate racks, data will remain available.

When it comes to Couchbase, the so-called rack awareness feature is controlled by using Groups. You can assign Couchbase servers into different Groups to achieve the rack awareness capability.

If you are provisioning a Couchbase cluster on AWS, you can create the server Groups analogous to the availability zones on AWS. This logical grouping in Couchbase allows administrators to specify that active and replica partitions be created on servers that are part of a separate rack zone.

See below figure-1 and notice the Couchbase cluster deployed on AWS has two server groups similar to the availability zone.

Figure-1 - Multi-dimensional Cochbase cluster deployed on AWS.

This cluster has two nodes for each service offering for data, index, and query. The servers are logically grouped into two groups, rack-1a and rack-1b which is similar to availability zone 1a and 1b on AWS respectively. As a result, servers are physically arranged in two racks.

It is recommended to have the number of servers same between the server groups. If there is an unequal number of servers in one server group, the rebalance operation performs the best effort to evenly distribute replica vBuckets across the cluster.

The rack awareness feature is available only in Enterprise Edition of Couchbase.

Cheers!

Couchbase - the engagement database system

2017-10-20T10:16:00.000-07:00

Today, I just wrapped up the four-day Couchbase administration course from Couchbase. Since I'm still new to this NoSQL world, it is tons of new stuff. Couchbase refers themselves as beyond just a NoSQL database, they call it engagement database system.

Couchbase also has SQL style query language which they call it as N1QL. Using N1QL you can query the JSON documents stored in Couchbase buckets.

What is so special about Couchbase with opposed to the other NoSQL databases like Cassandra and MongoDB is, they provide multi-dimensional scalability which is essentially, different database components like data service, query service, index service and full-text services can be scaled in/out independently. This concept makes distributed system even more complex in my opinion. The main advantage of multi-dimensional scaling is, you can select different hardware resources for each service to best suit its workload which is a very good thing.

Couchbase provides very good free online courses if you want to get familiar with the database system.

https://training.couchbase.com/

So next few months I'll be working on Couchbase closely with automation on AWS platform.

Cheers!

Creating test data for Apache Cassandra cluster

2017-09-21T12:22:00.001-07:00

In many cases you are required to generate test data for Cassandra to do various type of testing. Read the rest of the blogpost if this is something you need and at the end, you should be able to create some test data very quickly.

I use the Ubuntu 16.04.2 LTS and Apache Cassandra 3.0 for this testing. The Cassandra cluster that I'm using here is deployed on AWS but this should not be a consideration factor for test data creation.

Use the steps mentioned below to create the test data.

1. Download the csv file

curl -O http://samplecsvs.s3.amazonaws.com/Sacramentorealestatetransactions.csv

Note. You can download this file directly on to the EC2 but due to some formatting issue it did not work correctly for me. I downloaded the file to my local machine (Mac) first and then opened the file using TextEditor then copied the contents to the EC2.

    2. Create the csv file on EC2

I just used the vi editor to open a new file called, realstatesdata.csv and then pasted the file contents (including headers) that I copied in step #1. Save the file. (You should be familiar with the VI editor to perform this step.)

After completing this step, you now have "realstatesdata.csv" on EC2.

    3. Connect to the Cassandra cluster using cqlsh

    4. Create a Keyspace

You need to create a Keyspace (in general terms, a database) which is a high level hierarchical object to contain Cassandra tables. The keyspace name I've chosen is "kp_realstate" but feel free to have any name which you want.

    5. Create a table

You also need to have a table which is the actual object which contains your real data. Unlike MongoDB, you need to have a the table schema created before you insert any data. I already analyzed the data set in csv file we just downloaded and decided the columns based on that. The table name is "realestate_data" again its your choice.

Note that, the order of the columns in CREATE TABLE statement should be the same as the order it appears in the csv file.

    6. Load the data

You use the COPY command to load data from a file to Cassandra.

COPY kp_realstate.realestate_data (street,city,zip,state,beds,baths,sq__ft,type,sale_date,price,latitude,longitude) FROM 'realstatesdata.csv' WITH HEADER = TRUE;

It has to be executed at CQL prompt.

If the import is successful you will see the messages like below.

Processed: 985 rows; Rate: 1151 rows/s; Avg. rate: 1865 rows/s
985 rows imported from 1 files in 0.528 seconds (0 skipped).

7. Make sure the data is imported successfully

At CQL prompt, you can execute any of the statements below.

select count(*) from kp_realstate.realestate_data;
select * from kp_realstate.realestate_data limit 30;

At the end, you've full data set for your testing.

Cheers!

JEMalloc and Cassandra

2017-09-13T15:02:00.001-07:00

Memory management in Cassandra

Cassandra depends on JVM-Java Virtual Machine, to accomplish Cassandra's memory management requirement. The JVM mainly divided into two areas as follows;

Heap - data area which contains the runtime structures.
Internal data structures - Java methods, thread stack and native methods.

Cassandra uses its memory in four ways which are mentioned below. This includes OS memory too.

Java heap
Offheap memory (OS memory that is not managed by JVM G.C-Garbage Collector)
OS page cache
OS TCP/IP stack I/O cache

Since Cassandra uses JVM for its memory management, tuning of JVM is necessary to get optimal performance in Cassandra. The tuning of JVM includes the changing the settings in cassandra-env.sh as mentioned below;

MAX_HEAP_SIZE
HEAP_NEWSIZE

What is JEMalloc?

JEMalloc is an enhanced memory allocator in Linux based platforms. With JEMalloc, the memory allocation for multithreaded applications scales well as the no.of processors' increases. The previously used memory allocator, malloc(3) suffered scalability bottleneck for some multithreaded applications that caused JEMalloc to emerged.

Use of JEMalloc has been introduced in Cassandra after 2.0.

Ensure JNA-Java Native Access and JEMalloc are installed on Linux AMI. If you're creating an Amazon AMI for Cassandra, then you want to install both of these.

yum install -y jna
yum install -y jemalloc

Cassandra.yaml configuration requires the change mentioned below in order to use JEMalloc.

memtable_allocation_type: offheap_objects

Note. The above setting is set to "heap_buffers" by default.

What is the benefit of using JEMalloc in Cassandra

By enabling JEMalloc in Cassandra, it reduces the amount of Java heap space that Cassandra uses. Data written to Cassandra is first stored in memtables in heap memory. Memtables are then flushed to SStables on disk when they get full. The garbage collection process of JVM is used to clear the heap memory. Sometimes, this garbage collection process causes issues in Cassandra due to garbage collection pause.

The benefit of JEMalloc is, it reduces the pressure of garbage collection because Cassandra uses off-heap memory allocation with JEMalloc.

References

JEMalloc support for off-heap allocation

A Scalable Concurrent malloc(3) Implementation for FreeBSD

Cloud Spanner - Google's mission critical and massively scale RDBMS with NoSQL features

2017-04-26T08:38:00.000-07:00

Isn't this amazing?

A database system which blends most important relational components and highly scalable NoSQL capabilities in one system. Google call it, Cloud Spanner.

As per Google, it can scale massively to hundreds of data centers around the globe. Most importantly it can speak SQL which is the strongest database language so far.

In a nutshell, Cloud Spanner has the following features.

Data consistency
Scales horizontally across data centers
Speaks SQL
Strong consistency and availability at global scale
Massively distributed
ACID compliance
Automatic sharding and synchronous replication with low latency
Schema updates without downtime
Auto-scales up and down as needed
Simple pricing model

This proves the point that eventually both relational and non-relational databases will merge!

Cheers!

Vertical scaling vs Horizontal scaling: Which one is more challenging?

2016-10-14T13:45:00.000-07:00

I think this topic is more appropriate when it comes to deciding the database system for your application. Vertical scaling refers to scale the database system by providing more hardware resources to the host machine. In the other hand, horizontal scaling refers to scale out the database systems by adding more nodes. Either way the objective is to have better performing database system so that it can serve users fast.

I opened up a discussion with my fellow DBAs and DB enthusiasts and below are some of the views they expressed about the topic.

"I think Horizontal scaling (by adding node/machines) is less challenging. Any way in vertical scaling you will hit a ceiling with CPU memory etc depending on the capacity of the machines used." [Bhanu]

"Vertical scaling is less challenging to implement as it is just to increase the resources of a given machine.in a virtual environment VS becomes more and more easier to implement. However, as Bhanu pointed out, there is a limit where u can reach with VS.

Horizontal Scaling will require more expertise (hence costly), additional software and in some cases, even additional hardware features. Therefore, HS will be more challenging to implement" [Shamil]

"Horizontal scaling is much more align with NoSQL Technologies where as in Relational aren't.

Relational does this through partitioning and there should be some routing mechanism implemented in some where to cater.

My guess is, it is based on the technologies we are trying to implement." [Abi]

"I would say, irrespective of the technology still Vertical Scaling is less challenging to implement. for example, adding more RAM and processor power to a mongoDB box is much easier than implementing mongo sharding.

In the other hand, even relational DB technologies support horizontal scaling. SQL server has AlwaysOn and Oracle has Real Application Clusters. both can do Horizontal Scaling." [Shamil]

My view

Regardless of the DB system, the core concept is to process small chunks of data to achieve better performance. If you consider SQL Server we always try to deal with small chunks of data, when scanning, deleting, updating, inserting, etc. That is because no matter you increase the processing power, the underneath algorithms are not salable. As a result you're forced to deal with small chunks of data to achieve better performance. By adding multi-cores does not resolve this mathematical limitation of algorithms. Then there is additional overhead when it comes to threads management.

In relational db systems, they are designed to handle large monolith databases in a single server. That is the reason we've many TBs of databases in relational systems and we're facing many limitations and challenges with those databases.

When it comes to SQL Server, it has some horizontal scaling solutions like table partitioning and replicas. If you consider table partitioning, it is still within a single machine and you can scale out IO operations within a single machine. (There is an exception to this with the newly released version of SQL Server 2016. A feature called stretched database allows you to partition a table to keep frequently working data set in on-prem and less frequently used data set in cloud.) You can not distribute partitions among many nodes. So still it has a limitation.

If you consider replicas in SQL Server, those can be distributed in many nodes. However replicas are read only so that you can scale out applications reads but still writes are not scalable. In AlwaysOn, writes can be accepted by the primary node only. Consequently SQL Server does not provides true horizontal scaling capability just because of the limitations that I've mentioned above.

In true horizontal scaling, data should be able to partitions and assign to different nodes/machines. This technique is called sharding. Database systems like, MongoDB, Cassandra and DynamoDB all provides this capability. In these database systems, the data can be partitioned and also these partitions can be placed in many nodes which gives the horizontal scaling capability backed by additional CPU and memory power. In other words, CPU and memory can be scale out to other nodes.

The specialty of Cassandra and DynamoDB is, they have masterless architecture which means any nodes of the cluster can accepts reads as well as writes. So there is no single hot spot when it comes to writes as in SQL Server. However, in this architecture has numerous challenges since data is fully distributed among many nodes by using some kind of hashing technique. You can imagine how complex is to handle the concurrency related issues when the your data is distributed and the complexity becomes increase when any nodes can accept the write. These challenges are simply because those DB systems are designed for horizontal scaling.

So in my opinion horizontal scaling is more challenging than vertical scaling. Actually in vertical scaling there is no distinctive challenge.

Hope this is interesting discussion.

Cheers!

Comparison of Cloud Databases

2016-09-07T08:11:00.000-07:00

Interesting reading..

AWS vs Azure vs Google Cloud Platform – Database

DBA role transformation

2016-08-17T10:33:00.000-07:00

Traditionally, being a database administrator (DBA) meant minding the store. In an on-premises environment, a lot of a DBA's time is spent attending to mundane administrative, maintenance, and performance optimization tasks, aimed at keeping systems running properly. Such tasks include keeping servers up to date, adding new disk space when capacity is exceeded, and tuning disk I/O. At Microsoft, our DBAs spent about 50 percent of their time on maintenance work.

In Azure, the DBA role has fundamentally changed. Routine administrative, maintenance, and performance management tasks are automatically managed by Azure. This means that our DBA team has free time to deliver more strategic value to the company. The team can invest in more high-value and time-intensive activities, such as data modeling, design, architecture, and code reviews. For example, we have been able to reconstruct our NoSQL data model, and perform more graph modeling.

Read the full article: Microsoft focuses on strategic database administration by moving to Azure

How To Create a MongoDB Replica Set

2016-08-04T13:20:00.001-07:00

Recently I did setup a MongoDB replica set in a VM hosted in AWS cloud environment. Since I'm totally into SQL Server and Windows, setting up MongoDB replica set was quite challenging and at the same time it was interesting. The biggest challenge was to use the terminal for scripting work. It looks to me, writing commands in terminal was bit cumbersome. Writing some commands in mongo shell with JSON format adds more complexity to the work. I'm not a fan of JSON format. JSON use to be the developer friendly format and not definitely a friend of DBAs.

The concept of MongoDB replica set is quite interesting. You can use replica set configuration to achieve both High Availability (HA) and Disaster Recovery (DR) objectives. It has automatic failover feature too. In replica set, one node is capable to perform write and read operations and it is called a primary node. The secondary nodes can be used for multiple purposes such as DR, analytics, reads, backups, etc. There is a third type of node known as Arbiter. This node is optional and it is used as a tie breaker in election process. Election process is carried out in events which needs to select a new primary. Arbiter is just to participate for the election and it does not hold any data.

Replica set architecture

Replica set has many different architectures depending on the business need. The replica set I created has three nodes, 1 primary and 2 secondaries. Node is also knows as a member. The simplicity of the replica set architecture in MongoDB is, it can be created in a single machine as well. In the below example, I created all three members in the same VM. Each node would reside in separate VMs in typical production deployment. Below diagram illustrate three members replica set in a single VM.

Steps

Below are the steps that I carried out to setup the replica set;

Node Provisioning

1. Spin up a VM in AWS cloud. This is quite simple and its free. You can sign up for 1 year free subscription in AWS so that you can mock around it. Use this URL to sign up to AWS cloud. https://aws.amazon.com/

Connect to the VM

2. Use the public IP to connect to the VM you just created in step #1. You can SSH to the node using below command in Terminal.

> sudo ssh -i "aws/test.pem" ubuntu@ip

The test.pem file contains the private key that is used to authenticate into the cloud VM. You can create this key pair in AWS console and then download the file. In my experiment, I placed the pem file in folder called, aws.

Furthermore, you need to check the pem file permissions. The pem file needs be given the minimum permission (read-only) in order to authenticate with the cloud VM. Follow the below mentioned commands to manage the pem file permissions.

> ls -l test.pem # check the permission of the file.
> chmod 400 test.pem # set the file permission to read only.

Install MongoDB

4. Since this is a brand new VM, I had to install the MongoDB using the below command.

> sudo apt-get install -y mongodb

Please note the command given in mongodb site does not work. (apt-get install -y mongodb-org)

Configure Replica Set

3. Create three separate folders to maintain replica set files for each node.

> mkdir -p /srv/mongodb/rs0-0 /srv/mongodb/rs0-1 /srv/mongodb/rs0-2 /srv/mongodb/rs0-3

rs0-0, rs0-1, and rs0-2 are the folder names. You can use -p switch with the above command to create these three folders in a single statement. Its awesome, huh!

4. Create three separate mongod instances.

> sudo mongod --port 27017 --dbpath /srv/mongodb/rs0-0 --replSet rs0 --smallfiles --oplogSize 128

> sudo mongod --port 27018 --dbpath /srv/mongodb/rs0-1 --replSet rs0 --smallfiles --oplogSize 128

> sudo mongod --port 27019 --dbpath /srv/mongodb/rs0-2 --replSet rs0 --smallfiles --oplogSize 128

Please note, each mongod service/process has given unique port numbers. These port numbers are later use to connect to each mongod instance. Functional point of view, these mongod instances are somewhat analogous to SQL Server instances because it represent individual server instance which can hold databases. In this example, all three server instances are to be run on a single VM. However in a typical production environment these instances run in separate VMs.

You can also notice --dbpath parameter is given the specific folder name for the each instance. These are the folders that we created in step #3. Additionally it has the replica set name, rs0 which is the same in all three instances because these three instance are for one replica set. The replica set structure can be considered as a cluster environment in SQL Server.

Oplog is the transaction log in MongoDB. So you can specify the oplog size when creating the mongod instances. Creating replica set has lot more parameters other than the ones mentioned above. (Please refer the links given in references section for more detail)

Connecting to mongod instance

5. Once the step #4 complete successfully, you now have three member MongoDB replica set. Next thing is to connect to an instance. For example, we want to connect to 27107 instance.

> mongo --port 27017

Then you will get a prompt specific to the instance you connected. With the prompt itself you can identify whether it is Primary or Secondary. Something like below;

rs0: PRIMARY>

But as of now, we just created mongod processes. We have not yet completed the replica set creation.

Add members to the Replica Set

6. After connecting to the 27017 mongod instance, you can run the below command in mongo shell to add one member to the Replica set.

> rsconf = {
           _id: "rs0",
           members: [
                      {
                       _id: 0,
                       host: "localhost:27017"
                      }
                    ]
         }

The preceding JSON document has the minimum information required to add a member to the replica set. You can specify all the members to the same document in members array. What I'm going to do is, to create one member and then add the other two members later.

Below JSON document shows how you add all members at once with some additional parameters.

> conf = {
   _id:"mySet",
   members:[
       {_id:0,hots:"A",priority:3},
       {_id:1,hots:"B",priority:2},
       {_id:2,hots:"C"},
       {_id:3,hots:"D",hidden:true},
       {_id:4,hots:"E",hidden:true,slaveDelay:3600},
   ]
}

I had some issues when copy and paste the above JSON style document to the mongo shell. This is the reason I said typing these scripts in mongo shell is very challenging, at least for me. Alternatively you can create a file with above document and then execute the file. Probably that is the easy route to take.

7. Initiate the Replica set

> rs.initiate( rsconf )

This will initiate the Replica set and primary node will be created.

8. Add other two members to the replica set, rs0.

> rs.add("localhost:27018")
> rs.add("localhost:27019")

Note that the port numbers are different for each member.
Now we've completed of adding all three members to the replica set, rs0. You can notice the mongo shell that is connected, indicates as PRIMARY. At this point, the replica set we created is up and running.

Monitoring

9. Below are some useful commands that you can use to manage the Replica set.

rs.help()	Returns a basic help for all of the replication related shell functions
rs.conf()	Returns a document that contains the current replica set configuration
rs.status()	Returns a document with current status information of the replica set
rs.slaveOk()	Allows the current connection to allow read operations to run on secondary members

Testing

10. Connect to the primary node and insert a record.

> db.people.insert({'name':'Andrew'})
> db.people.find()

You can see the new record exists in primary. Now connect to a secondary node and issue the following command. If your querying the secondary at very first time, then by default you can't view records. You need to run rs.slaveOk() to make it readable.

> db.people.find()

References

https://docs.mongodb.com/manual/tutorial/deploy-replica-set-for-testing/

Hope this helps for a beginner who is willing to learn MongoDB.

Cheers.

Future versions of SQL Server?

2012-11-25T09:40:00.001-08:00

It is publicly known fact that RDBMSs have well known limitations when it comes to unstructured data management. This is the reason to emerged new database technologies like NoSQL. It is true that NoSQL is not a replacement for RDBMS. However the time has come to include NoSQL features into RDBMS products (then it can’t be called it as RDBMS) or introduce brand new NoSQL product from RDBMS vendors. We need to wait and see how Microsoft react to these new technology trends and how SQL Server change accordingly. Oracle has already announced their NoSQL version called Oracle NoSQL Database. Will Microsoft come up with new NoSQL product?

24x7: A pipe dream or reality? And at what cost? Can a business afford not to be 24/7?

2012-11-17T23:20:00.001-08:00

Executive Summary

What is 24x7? In simple terms, it refers to “Always On” or “100 % availability”. Meaning the information system is available to users, at any time, without interruption. No matter what the day is. No matter from where users access the system is.
Web definition of 24x7 is stated below;

“24/7 is an abbreviation which stands for "24 hours a day, 7 days a week", usually referring to a business or service available at all times without interruption.” [1]

The most popular and the technical term used for the same concept is “availability”. As we learn in our software engineering subject at graduate studies, “availability” is one of the non-functional requirements of an information system. To achieve 24x7 availability, an enterprise has to spend money on it. It does not come free, as many end users may think; in long run enterprises get the return on 24x7. [1]

How 24x7 relates to Information Systems

Today’s information systems are very competitive. For example, consider social networking media systems available today. No doubt the most popular system is Facebook. There are various other similar systems available to compete with Facebook. Some are older than Facebook while others are still emerging, e.g.: GooglePlus. The most critical factor and also the most contributing factor towards the success of the business is the availability of the system. At any time system should be available to users as they want to use it, at times convenient for them. Imagine a situation where the Facebook is down or unavailable for a couple hours or even couple of minutes. What do you think the impact would be on Facebook? Furthermore imagine about this type of availability issues happen on Facebook on regular basis. That would be the point that users begin to move away from Facebook to more stable systems. Users may slowly switch to another similar system like GooglePlus or Hi 5 or to another one. This situation is slowly built-up due to the availability of the system. No matter how many elegant features you delivered to users. Facebook is one example which 24x7 plays a major role.

Consider Airline reservation system. This could be considered as a mission critical system where the system availability is top most priority. Failure of such of such systems will result failure of business operations. That is critical to the organizations mission. It is not hard to imagine the impact to the passengers in case of failure in Airline reservation system. The impact would be not limited to a country, it definitely a global impact. This emphasizes the importance of system availability. The following literature survey found in internet gives an evidence of such airline system failure which lasted about five hours. [2] The below is an excerpt of the same article.

“United said that the system failure affected "departures, airport processing, and reservations" which caused flights not to be able to take off from some locations including its hub in San Francisco International Airport (SFO)
The airline cited "a network connectivity issues" as the reason for the computer systems failure which were fixed through "troubleshooting procedures"

Similarly there are lots of other information systems available which 24x7 plays a major role. E.g.: defense system, stock exchange systems and many other financial applications, etc.
More importantly mission critical systems need to have 24x7 availability. Those systems cannot afford to have even a second downtime.

Challenges of 24x7

Why is it difficult to provide 24x7 availability continuously? To answer that, need to think about the maintenance of information systems. Any information system runs on top of an operating system. The operating system would be Windows, Linux, etc. Information systems communicate with other systems through communication channels which is essentially network systems. The following bullet points discuss several challenges facing on when providing 24x7 supports.

• OS patching - It requires updating the operating system in regular basis to apply important security patches and other OS components. After applying security patches the necessary requirement is to restart the machine. During the restart time, the systems hosted in those servers are not accessible to users. Thus it breaches the concept of 24x7. This would be one and most challenging factors for 24x7.

• System changes - Any software system evolves with the time and it accommodates changes due to the changes on business. The changes may be modifications to existing code base or implementing new requirements. Once code is developed and tested it has to push to production environment for the customers to use. During the release the system may need a downtime, which may be a feature downtime or entire system downtime. This is another challenge for 24x7.

• Database modifications - The main challenge would be the schema modifications on database systems. Database is a central component which store all the enterprise data and retrieves based on customer requests. Doing such schema changes on production requires a downtime for the systems which accesses the databases which are being modified.

• Database patching – Similarly OS patching, database systems also need to apply patches time to time, which requires the database system shutdown. During this period, the system which access data from the database system will not function.

• Administrative work - Like any other product or system, software systems also have to go through certain administrative work, specially rebuilding of database indexes, etc. All sort of these work may need a downtime which results unavailability of the enterprise systems.

• Hardware changes - Like you replace parts in your automobile due to wear-out, it is necessary to replace parts of the computers which host enterprise applications. You may need to upgrade the processing power of the servers to newer models, you may increase memory capacity, or you also may want to replace or increase hard disk capacities. This can cause interruptions for enterprise applications. There can be network outages also due to various reasons which cause interruptions to systems.

• Natural threats - The most important challenge would be to protect enterprise systems from natural disasters or terrorists activities. We have plethora of instances on these two categories. Example, terrorist attack on World Trade Center (WTC) USA, various floods happens on many countries in the world, etc.

Consider the WTC attack. What happened to the systems and data onsite at WTC? It is no doubt that everything could have been destroyed. Then what happen to the systems data? Were they not available to users? Perhaps that could be the case for some systems, but at the same time there could be systems which still continued operating.

• Cyber threats - Protecting data in enterprise applications is the most difficult task today. All the world’s super powers are preparing for a cyber-war which will be the next phase of war between countries. Already some countries have blamed other countries about illegal access to their valuable data. The security aspect of data is most important factor for 24x7. If the system is hacked by a professional hacker availability of the system is in jeopardy.

24x7: a dream or reality?

With all the above mentioned challenges, can enterprise applications achieve 24x7? Or it’s merely a theoretical concept. Information technology is evolving, with the latest new concepts and implementations. With the advance technologies available today 24x7 is not a dream any more. It is achievable and many enterprise applications are using them. That’s why systems like Google, Facebook, stock exchange systems, Amazon, eBay are available at any time.

How to overcome challenges?

To overcome the above challenges it needs to understand various aspects of the enterprise systems in terms of how they develop. Any enterprise system is being built by using hardware components, software components, communication components, etc. The challenges listed under “Challenges of 24x7” are directly connected to the different components of an enterprise system.
The main hardware components of any system are physical servers, CPUs, memory, UPS (back power), network interface cards, storage, etc.

Data center concept

Large enterprise systems probably have data centers. Data center is a place where all the physical servers and other hardware components are connected each other to install systems and provide services to end users. A data center consists of many physical servers. In case of extremely large system, there can be thousands of physical servers. There can be several data centers available depending on how large the system is. A data center communicates another data center through communication channels. Each data system should have redundant power systems, so that in case of single power system failure will not result entire data center failure. Most of the hardware related components are being used redundantly to make sure one failure not fails the whole system.

Data center security

Data center is secured in many ways. The location of the data center is obviously a place where there are no known natural disasters. E.g.: earthquakes, floods, Tsunami, etc. There are special security protection mechanisms are built it to data enters. Surveillance security cameras and access keep track of access events. Unauthorized logins are prevented. There are software and hardware systems are there to prevent those unauthorized accesses.
Still there can be threats to data centers regardless of how much measures being taken to secure them. A terrorist attack or unexpected naturel disaster could potentially destroy any data center resulting systems hosted in data centers not going to be accessed anymore.
As the author discussed in one of the earlier sections, the solution would be to apply the redundancy to the data centers as well. Another data center could be added a passive data center while active data center is providing the services. In a situation where active data center is unavailable completely, the passive data center could be taken all the responsibilities done by active data center. As a result it will not break any systems, it provides the business continuity. [3]

Data communications

There has to be a communication channels between data centers. Through the data communication channels, enterprise applications would be able to communicate with data centers and then connects to the World Wide Web so that it can reach out to many different geographical locations.

System Security

System security is a top most priority which essentially needs attention and continuous monitoring. There hackers in the world who tries to break the systems security with or without a purpose. There has to be measures take to prevent such security breaches. If not business continuity is an issue. The following is an excerpt from internet which it states how to make sure systems security.

“Under the direction of the Nippon Express Corporate Compliance Committee program and decision, Nippon Express system security best practice is based on the ISO-27001, SOX, J-SOX, HIPPA, ISO-9001 and SAS 70 Type-2 compliances with ISO-17991 and CoBit controls. IT provides robust systems with enhanced security for internal networks, at all network perimeters, and in data exchanges with customers on external networks. All network access that traverses between internal and external networks, such as EDI with clients, Web, FTP and VPN are routed through redundant enterprise-class firewalls managed and monitored by IBM at the e-Business Hosting Center. Perimeter security is controlled using a sophisticated design of multiple DMZ. Routine security tests (external hacking) and IDS (Intrusion Detection Systems) are provided by IBM. Any production change is reviewed by the weekly change control committee meeting and weekly IT management meeting to review, justify, approve, implement and trace. System security incidents and issues are tracked and escalated for prevention and quick resolution. Our IT best practices are being validated by the combination of annual audits by Deloitte and quarterly technical audits through special technical auditors including password management and patch management. Customer data is secured and protected with multiple levels of security access control (internet, operating system, application, database, transmission) including personal information privacy data protection. [4]”

Mitigation planning

Mitigation plan comes into play if something goes wrong. Even if there is a mitigation plan in place there might be a situation that plan does not work as expected or mitigation plan will fail. If such a situation happened there should be a plan to take the business back on track as quickly as possible. The following is an excerpt from internet on how business continuity make sure in a company called Nippon Express.

“Nippon Express Management has developed a Business Continuity Plan on how we will respond to events that significantly disrupt our business situations including, but not limited to, power outages, major water leaks, fire, loss of water, severe weather, and any facilities failures that may cause business interruptions. Since the timing and impact of disasters and disruptions is unpredictable for the global logistics business, we will have to be flexible with business partners in responding to actual events as they occur. Our business continuity plan is to quickly recover and resume business operations after a significant business disruption and respond by safeguarding our employees and property, making a financial and operational assessment, protecting the company assets, and allowing our customers to transact business. In short, our business continuity plan is designed to permit our company to resume operations as quickly as possible, given the scope and severity of the significant business disruption. Our business continuity plan addresses annual reviews of the following: data backup and recovery; all mission critical systems; financial and operational assessments; alternative communications with customers, employees, and regulators; alternative physical location of employees; critical supplier, contractor, bank and counter-party impact; regulator reporting; and assuring our customers prompt access to their business operational data. Other business partners with whom we do business are required to maintain business continuity plans also. Along with all proven redundant systems with multiple business continuity rehearsals at Nippon Express, our current IT Infrastructure including MPLS WAN with two IBM data center solutions in NJ and IL provide an average of 99.99% technical service level ratio results to support Nippon Express business continuity. Nippon Express continues to improve for the worst system disaster scenarios with a new RTO (recovery time objective) target of zero to 24 minutes while RPO (recovery point objective) is zero to 24 seconds. Furthermore, Nippon Express makes daily data backup on tapes to store at the Iron Mountain offsite storage per corporate compliance record retention policy. Also our IT capacity planning and on-going monitoring allows to provide adequate system capability for today and future business requirements.” [4]

Tool support for 24 x 7

Use of tools is an essence factor when supporting 24x7. By using tools, potential failures could be identified in advance thus system administrators could take remedial actions to prevent the failures.
System administrators need to implement monitoring mechanisms to the enterprise system to check the health of all the systems in a regular basis. The below is an excerpt from internet which describes the tool supports for 24x7.

“24x7 Event Server is a flexible and relentless watchdog. It can detect occurrences of single point-in-time events such as error record or security alert written to system event log, CPU usage or free disk space reaching pre-defined threshold level, startup or shutdown of a specific service or application, and many other. It can also detect persistent or recurring events such as applications consuming too much CPU for a certain period of time, database or web server slow responses to user requests, hanged backup processes and many others. 24x7 Event Server supports a number of event response processing methods, so once it detects an event and alerts you to potentially damaging situations it can then take automated corrective actions like restarting failed application or service, running a fix-it user-defined script and more. 24x7 Event Server provides straightforward wizard-driven user interface that is easy to use and quick to learn. No special training is required.” [4]

Conclusion

The author has experience of supporting 24 x 7 for the SQL Server databases. As per the author’s view achieving 24x7 is very much achievable in today’s context with the support of advanced technological implementations. Author has given much evidence to support the idea. However going for 24x7 for every enterprise is not a wise idea. 24x7 does not come free. It has a cost factor more than that the enterprise should realize the requirement for going to 24x7. Then it needs to look at the financial and technical feasibility.

References

[1] "Wikipedia," [Online]. Available: http://en.wikipedia.org/wiki/24x7. [Accessed 24 March 2012].
[2] "United Airlines Computer System Failure," IBTimes, 18 June 2011. [Online]. Available: http://sanfrancisco.ibtimes.com/articles/165269/20110618/united-airlines-computer-systems-failure-merger-related.htm. [Accessed 15 April 2012].
[3] "Data Centers," [Online]. Available: http://www.secure-24.com/data-centers/. [Accessed 15 April 2012].
[4] "NIPPON EXPRESS," [Online]. Available: http://www.nipponexpressusa.com/information-systems/overview. [Accessed 18 April 2012].

Database Sharding

2011-10-11T06:05:00.000-07:00

Database sharding is a concept of dividing large databases to small data sets to scale out applications. Following article gives the in-out detail about sharding.

http://www.codefutures.com/database-sharding/

Dimensions to use to compare NoSQL data stores

2011-10-11T05:12:00.000-07:00

Good article to read if you want to decide which NoSQL product that you're going to be used.

http://huanliu.wordpress.com/2011/01/21/dimensions-to-use-to-compare-nosql-data-stores/

A Co-Relational Model of Data for Large Shared Data Banks

2011-04-11T04:39:00.000-07:00

This is very interesting research paper about NoSQL and other database related matters.

http://queue.acm.org/detail.cfm?id=1961297