Data Migration Service from Google

 According to Gartner, 75% of databases will be in the Cloud by 2023. Since we are not very far away from 2023, as organizations we need to look at the possibilities of Cloud Databases. One of the greater challenges will be data migrations. As we know, organizations have a lot of data already. To Cloud Databases to become a success story, you need mechanisms and techniques to migrate your data to cloud databases.

In that view, Google Cloud has launched new Data Migration Service (DMS). Currently DMS available in Preview in which customers can migrate MySQL, PostgreSQL, and SQL Server databases to Cloud SQL from on-premises environments or other clouds.

Customers can start migrating with DMS at no additional charge for native like-to-like migrations to Cloud SQL. Support for PostgreSQL is currently available for limited customers in Preview, with SQL Server coming soon.

You can read more details at

https://www.zdnet.com/article/google-cloud-launches-data-migration-service-to-land-database-workloads/

https://datacenternews.asia/story/google-cloud-launches-new-data-migration-service

What is the Best Database

If someone asks you "What is the best database", what is your answer. Answers might be different depending on your job role, whether you are a developer or a database administrator or client.

However, there is a ranking done for the database every month and the following is the latest ranking of databases. 

You can look at the rank of all the 359 databases here. According to this ranking, first six databases, Oracle, MySQL, Microsoft SQL Server, PostgreSQL, MongoDB, IBM BD2 are remaining the same compare to the last year.  If you look at the entire list you will find that Azure SQL Server Database is a major improvement. Azure SQL Server Database ranked 17 this year where it was 25th in last year.  

Let us look at the trending of these databases. 

You can see that MySQL and Oracle are running closers whereas PostgreSQL and MongoDB are running a close encounter. 

This does not say one database is better than others. This ranking based on various factors, such as Number of mentions of the system on websites, Frequency of technical discussions about the system, Number of job offers, in which the system is mentioned, Number of profiles in professional networks, in which the system is mentioned, and Relevance in social networks etc. You can look at the complete ranking parameters here.

SQL Server Migration Assistant (SSMA) v8.1

SQL Server Migration Assistant (SSMA) for Oracle, MySQL, SAP ASE (formerly SAP Sybase ASE), DB2, and Access allows users to convert a database schema to a Microsoft SQL Server schema, upload the schema, and then migrate data to the target SQL Server (see below for supported versions).

• SSMA for Access
• SSMA for DB2
• SSMA for MySQL
• SSMA for Oracle
• SSMA for SAP ASE

DB-Engines Ranking

It is very common to compare the databases for marketing or for fun. http://db-engines.com/en/ranking has done the ranking for the database engine.

SQL Server has the major gain while Oracle has the major slip though it is still gaining the number one position. Following is the trend for the ranking of Database Engine. 

Which relational DBMS is best for your company?

There are lot of databases around you. What should I select from them. Lot of parameters you need to think. Database expert Craig S. Mullins examines the top relational database management system software to determine which best fits your organization.

Read more at http://searchdatamanagement.techtarget.com/feature/Which-relational-DBMS-is-best-for-your-company

MySQL's creator on why the future belongs to MariaDB

It's fair to say that MySQL creator Michael "Monty" Widenius is not a fan of Oracle. When the company announced in April 2009 that it was purchasing Sun, Widenius saw a bleak future ahead for the (still) wildly popular open source database, which Sun had snapped up in 2008.

The day the Sun purchase was announced, Widenius responded in the tried and true open source fashion — he forked MySQL, launching MariaDB, and took a swathe of MySQL developers with him.

"Many of the original MySQL core developers, including me, didn't believe that Oracle would be a good owner of MySQL and we wanted to ensure that the MySQL code base would be free forever," Widenius explains.

Read more http://www.computerworld.com.au/article/457551/dead_database_walking_mysql_creator_why_future_belongs_mariadb/

GROUP_CONCAT or LISTAGG

 

Well, this is not some thing SQL Server has. This is a basic function you get in ORACLE or in mySQL.

What it does.

Let us look at this data set. It has Color and ProductNumber where ProductNumber is the business unique key,

What I need is following output.

In  mySQL you can use simple function GROUP_CONCAT

in Oracle you have LISTAGG function

Source :http://oracleabc.com/b/archives/2100

In both cases it is one table scan which means cost is low,

Let’s move to SQL Server.

You don’t have a out-of-box function. However there is a workaround for this. Honestly I don’t know how this is working.

and the query plan is,

In case of SQL Server, you need to use DISTINCT and WHERE IS NOT NULL and more importantly there is two table scans, which means you might not use this for large tables. 

So has Microsoft SQL Server team has reserved this for future versions.

Database Implications if IBM Acquires Sun

Reported or rumored merger discussions between IBM and Sun are generating huge amounts of discussion today (some links below). Here are some quick thoughts around the subject of how the IBM/Sun deal — if it happens — might affect the database management system industry.

• IBM is already serious about supporting multiple database management systems. DB2 on open systems is IBM’s flagship DBMS. But DB2 on mainframes and at least one flavor of Informix seem to be getting maintained and enhanced fairly seriously as well. And IBM has further DBMS products as well (e.g., DB/2 on the AS/400). There’s little reason to think IBM would orphan MySQL or any other DBMS product.
• IBM is very open-source-friendly. For a company that grew up for decades on proprietary software — and still is a huge software products vendor — IBM is very serious about open source. If you doubt that, I have two words for you: “Linux” and “Eclipse”.
• MySQL might finally get its industrial-strength act together. IBM is good at database management and good at open source. MySQL becoming a no-apologies transactional DBMS would obviously put pressure on Ingres, PostgreSQL, and EnterpriseDB, although there surely would be lots of happy talk about the open source DBMS market being validated, lifting all the vendors and so on. Also, a better MySQL could be bad news for Microsoft SQL Server too.
• Sun has a lot DBMS partnerships right now. Obviously, Sun owns MySQL, and has partnerships with MySQL storage engine vendors such as Infobright and Kickfire. Sun also has a substantial partnership with Greenplum, and a Barneyesque* one with ParAccel. And of course Sun has strong working relationships with major database vendors such as Oracle and Sybase. What’s more, on a case-by-case basis, Sun may cooperate in the field with yet other DBMS sellers. E.g., I’ve confirmed at least one instance of a Sun sales rep recommending a Kognitio DBMS.
• IBM partners with outside DBMS vendors too. You’d think IBM’s gazillion DBMS product lines would be enough. But nooooo. I frequently hear rumblings of IBM’s hardware or services operations working with other DBMS products as well. (This is, of course, actually to their credit.)
• Short-term, there probably would be little effect on partnerships. Greenplum runs on Sun’s Thumper/Thor line of boxes. DB2 doesn’t, and certainly isn’t optimized for same. In the short term, to sell Thors, Sun would presumably continue to sell Greenplum.
• Longer-term, there could be a DBMS rationalization. DB2, Informix, MySQL + storage engines, and big independent vendors such as Oracle and Sybase would surely always get attention. That’s a lot. There might not be room for much mind share for many database products and vendors beyond that list.

*A Barney partnership is one in which two or more vendors get on stage and do a song and dance about how much they love each other, with little substance beyond that.

Related links

• Larry Dignan thinks the IBM/Sun deal is sensible and ripe to happen.
• Dana Gardner thinks otherwise.
• Matt Asay seems to agree that IBM understands the open source business.
• Before IBM acquired it, solidDB was scheduled to provide a serious MySQL transaction processing engine.

From http://www.dbms2.com/2009/03/18/database-implications-if-ibm-acquires-sun/

How to create a Link Server for mySQL in SQL Server Management Studio (SSMS) in SQL

1. Download the MySQL ODBC driver from mysql site.
2. Install MySQL ODBC driver on Server where SQL Server installed.
3. Creating Linked server at SSMS
i. Expand Server Objects Node
ii. Right click Linked Servers and select New Linked Server
iii. Default page will be General Page,
iv. At Link Server , type the name of your link server
v. Select Other data source option
vi. At Provider select Microsoft OLE DB Provider for ODBC Drivers
vii. At Product Name type any label
viii. If you want to link MySQL with a DSN type the name of the previously created at the Data Source
Driver={MySQL ODBC 3.51 driver};Server=localhost; DataBase=DBNAME; Root=3306;UID = root;PWD = password
Make sure that you don't have spaces before ;

ix. At the Security Page, Map a login to the Remote User and provide the Remote Users’ password
x. At the Server Options page, set RPC and RPC Out to true

4. Modify Properties of the MSDASQL Provider
i. Expand Providers Node
ii. Right Click MSDASQL and Select Properties
iii. Enable
a. Nested Queries
b. Level zero only

c. Allow inprocess
d. Supports “Like” Operator

5. Modify settings in SQL Server Surface Area Configuration (SSSAC)
i. Enable OPENROWSET and OPENDATASOURSE support from SSSAC for features
ii. Enable Local and Remote connections via TCP/IP and named Pipes from SSSAC for Services

6. Restart SQL Server and SQL Server Agent Services
7. Accessing Data from MySQL
You can access MySQL linked server data from simple Select command as well as by using OPENQUERY method

SELECT * FROM [LINKEDSERVERNAME]...[tblname]

Note the triple dot notation. That tells the SQL SERVER to use database owner and default catalog.

SELECT * FROM OPENQUERY(LINKEDLUCISION, 'select * from mydatabase.tablename')