Database Replication

Database Replication can kill production if right technique was not selected. Identify the purpose/need for database replication. Put a scope around it and identify right technique and tools to achieve the goal. Setting authorization on data is very important. Always treat data with most respect as cash. Visit compliance / audit issues.

Why we need database replication?
1. To re-produce production issues in other environment.
2. To analyze production data
3. To run reports / business intelligence
4. For failover purpose
5. For back up
6. To achieve high availability
7. To improve performance for end user by putting db servers across continents and sync them up.
And many more

Types of Replication

Transactional replication.
Merge replication.
Snapshot replication.
Good pointer from Microsoft: http://msdn.microsoft.com/en-IN/library/ms152531.aspx
Oracle GoldenGate is costly product and reliable. But consultant costs around $2000/- per day.

Hire an Architect to give complete roadmap for given problem.
-o-

Database – Naming Standardization (Oracle)

Problem Statement: In tables can we use employee_name or emp_name?

Solution: This depends on organization. Many organizations follow emp_name.

Full Name Advantages:

  1. Easy to Read
  2. No Confusion

Disadvantages:

  1. Oracle supports only 30 char length column names
  2. Difficult to write queries at command prompt

Abbreviation Usage:

  1. Needs Abbreviation map to understand all column Names.
  2. Column Comments should help in this process.
  3. Easy to write queries
  4. Looks simple and easy to write and talk

Here with attached document from Oracle Site for your reference.

http://www.oracle.com/technetwork/developer-tools/datamodeler/datamodelernamingstandards-167685.pdf

datamodelernamingstandards-167685

Reference:

https://wiki.kuali.org/display/KULRICE/Database+Table+and+Column+Name+Standards

https://forums.oracle.com/forums/thread.jspa?threadID=1001609

-o-

MySQL – Pain with port 3306 error message

While trying to setup MySQL 5.x on windows getting following error.
Running zone alarm. Later frustrated with Zone Alarm Free Edition and moved to comodo fire wall. It looks good and having many options to configure.

Problem:

Error Nr. 1045…. Please make sure you have opened the TCP port 3306.

mysql port 3306 error

Solution: Port 3306 is opened. Still getting error
solution

First first time setup dont enter root password. This is causing problem and mysql is giving wrong error message.

-o-

Problem 2: MySQL Browser is wrong name. It permits to do database schema creation, table creation, enter data in editable grid and similar. But Coming from MySQL CC to this environment is painful. Also it is little bit confusing for first few minutes.

Default user name is: root
mysql admin

Default Schema is : test
mysql browser

To edit data in grid, we need to do following steps
Step 1:
mysql enter data - Step 1

Step 2:
mysql enter data - Step 2

Now ready to play with MySQL….

-o-