MongoDB to the Windows Azure cloud

In my previous post, it is mentioned the need of NoSQL product from Microsoft. I did some Googling and found several interesting posts where Microsoft and 10gen (one of the famous document oriented database vendors who invented MongoDB) has come to a collaboration to bring MongoDB to the Windows Azure cloud. Below are the links found related to Microsoft and MongoDB.

MongoDB on Azure Cloud Services

Going NoSQL with MongoDB

Future versions of SQL Server?

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?

Know about your SQL Server memory

Knowing about computer memory management is the basis to learn SQL Server memory architecture. Troubleshooting SQL Server memory is the topic which is not being discussed more often. Last couple of days I spent more time on reading SQL Server memory related articles, blogs and books. In this I got to know about deprecated memory feature used in SQL Server 2008 R2 and previous versions. That is “AWE Enable” option. This feature is no more in SQL Server 2012. I found below posts on SQL Server memory and thought of sharing.

SQL Server 2012 Memory Manager KB articles
Pushing the Limits of Windows: Virtual Memory
Pushing the Limits of Windows: Physical Memory

Test data generator using T-SQL

Some times DBAs want to generate test data to test some applications designed and developed by themselves. I have faced many situations like this in past. Recently also I had to generate some test data to test one of my research projects. Below is the ERD of the application. It is just four tables.

1. Customer ( master data)
2. Item (master data)
3. SalesOrder (Sales order header data)
4. SalesOrderDetail (Sales order detail data)

Currently executing queries

It is important to know what is currently executing in SQL Server at any given time. You may need this for troubleshooting purposes. The below query is useful in such occasions;

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time,
s.login_name,
s.host_name,
req.wait_type,
req.wait_resource,
SUBSTRING(sqltext.text, (req.statement_start_offset/2)+1,
        ((CASE req.statement_end_offset
          WHEN -1 THEN DATALENGTH(sqltext.text)
         ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) AS statement_text,
req.plan_handle,
sqltext.text
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_sessions s
    ON req.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
ORDER BY req.cpu_time DESC

Meaning of auto generated STATISTIC name

Use sys.stats catalog to see all the STATISTICS available for the database. To see the statistics in AdventureWorks2012 database, use the following T-SQL statement.

USE AdventureWorks2012
GO
SELECT * FROM sys.stats

You would see the output something like below; (only the values of name column appeared)

_WA_Sys_00000009_00000005
_WA_Sys_00000005_00000005
_WA_Sys_00000003_00000005
_WA_Sys_00000004_00000005

How do you understand above names.

_WA - Washington, the state of the US where SQL Server development team is located.
All automatically generated statistics have the name starting with _WA_Sys. The first number is the column id of the column which these statistics are based on. The next number is the hexadecimal number of the object id of the table.
Source: Inside the SQL Server Query Optimaztion by Benjamin Nevarez

Replication framework between relational database and document oriented (NOSQL) database

I’m working on a research project of building a replication framework from relational database to document oriented (non-relational/nosql) database system. This is to fulfill the research requirement of the MSc degree. The POC would be developed to demonstrate the solution by using MS SQL Server (relational) and mongoDB (document oriented).  I would like to hear any comments/thoughts about this from the community.


I started a discussion on this in LinkedIn mongoDB user group and received couple of valuable comments. Many thanks for those who given the comments. 


You can see them here.