Dedunu

Some times we need to take backups or we need to copy databases to another server for administrative purposes. But sometimes just copying files is not enough. In MongoDB Shell, they support to copy the database from remote instance to the current one with a single command. ( :D Just like Single Click in Windows )

nn

For this demo, I made alive two instances of MongoDB from the following commands.

nn

# Instance 1  nmongod --port 9990 --dbpath /data/db1 nn# Instance 2  nmongod --port 9991 --dbpath /data/db2n

nn

In instance 1 there is a database called “csampledb1”. For that, I need to connect to the first instance to create that database.

nn

mongo localhost:9990n

nn

nn

After that with the following commands, I create a database with one collection.

nn

use csampledb1  ndb.csamplecol1.save({id:1, name:"sample name"})n

nn

Then I log in to next MongoDB instance using MongoDB Shell.

nn

mongo localhost:9991n

nn

Then I use a single command to copy the database from instance 1 to instance 2.

nn

db.copyDatabase("csampledb1","csampledb2","localhost:9990")n

nn

nn

Syntax of this function is like below. There are two arguments which I didn’t use.

nn

db.copyDatabase(fromdb, todb, fromhost, username, password)n

nn

Tags

nn

n
• MongoDB

n

• MongoDB running two instances

n

• first instance

n

• MongoDB Shell

n

• administrative purposes

n

• software

n

• MongoDB copy databases

n

n

If you are new to MongoDB you may need to discover the databases and collections (basically objects) on your MongoDB instance. For this easily you can use a GUI tool like MongoVUE. But in this blog post, I’m not going to describe GUI tools. I’m going to explain about MongoShell to navigate through database objects.

nn

I can remember the first day that I used Linux. In that day I fed up with Terminal and gave it up. But now I think it’s cool!. Somehow if you want to do something easier I still recommend GUI Tools.
nFirst to execute those commands you should log into MongoShell. In windows mongo.exe.

nn

How to take database List?

nn

show dbs  n

nn

How to check the database that you are currently using?

nn

db  n

nn

or

nn

print(db);  n

nn

How to change to a new database?

nn

use <database name>  n

nn

E.g:-

nn

use AdventureWorks2012 n

nn

How to take the list of Collection in the current Database?

nn

show collections  n

nn

or

nn

db.getCollectionNames();  n

nn

How to take the list of Users in Database?

nn

show users  n

nn

or

nn

db.system.users.find();n

nn

Tags

nn

n
• MongoDB

n

• MongoShell

n

• Mongo Shell

n

n

In SQLCMD and Powershell I wanted to take the list of databases. In MySQL “show databases” command was there. But in SQLCMD I was unable to find such a command.

nn

--Stored ProcedurenEXEC sp_databases  nGO   n  n--SELECT StatementnSELECT Name FROM master.dbo.sysdatabases  nGO n

nn

You can use the above commands in SSMS (if you are lazy to move your mouse to object explorer) and SQLCMD. Also, you can use the same thing on PowerShell too.

nn

# Stored ProcedurenInvoke-SQLCMD "EXEC sp\_databases"  nn# SELECT StatementnInvoke-SQLCMD "SELECT Name FROM master.dbo.sysdatabases"n

nn

But in SQLPS you can go to your SQL Server instance’s database and just type “dir”.

nn

Tags

nn

n
• SQL

n

• SQLPS

n

• Powershell

n

• Administration

n

• SQLCMD

n

• SQL Server

n

• SQL Sever

n

n

If you have so many documents in your collection in MongoDB database. Sometimes you may want to retrieve a few documents. But if we run find() we can’t have nice output. You may need to have a nice indentation and line breaks. You may want results in a more readable way. Then you can use pretty().

nn

nn

Who can read this??

nn

nn

If you use pretty() you can take nice output with Mongo Shell.

nn

Tags

nn

n
• MongoDB

n

• Mongo Shell

n

• Pretty

n

n

At the first date I heard about NoSQL DBMS, I felt insane. Then I got eager to learn. But I didn’t pay any attention to learn. Later I got to know that MongoDB is a scalable one. Then I got a chance to follow free MongoDB course. Although I registered to both Developer and DBA courses I had no time to watch developer tutorials. But I finished DBA 1st Week. In that video tutorial, they explain how to install MongoDB on Windows as well as on UNIX. But when we are using Mongo in Windows Development Environment it’s a headache to start service again and again. Because of that, I wanted to install MongoDB as a Windows Service.

nn

In that video tutorial, they explain how to just run MongoDB on Windows Environment. First of all, you should have a data directory. For that, you should create two folders in the drive that you are planning to install MongoDB. Let’s assume that we are going to install MongoDB on D: Drive.

nn

Then you should create two directories(Folders) in D: drive

nn

n
• D:\data

n

• D:\data\db

n

nn

You can manually change the data path but for a beginner, it’s better to use the default path. And if you are trying to install it as Service you need to create a log folder.

nn

n
• D:\log

n

nn

Then open a command prompt and go to the MongoDB bin. I assume you have pasted it on D:\ drive. your directory path will look like this D:\mongodb-win32-x86\_64-2.2.0\bin.

nn

After that, you should run mongod.exe to install MongoDB as a windows service.

nn

mongod.exe --install --logpath D:\logn

nn

When you have typed and entered this command on CMD, it will create a Service for MongoDB. You can start MongoDB service with below command.

nn

net start MongoDB**n

nn

Enjoy Mongo!!!!

nn

Tags

nn

n
• MongoDB

n

• MongoDB Windows Service

n

• MongoDB Install

n

• NoSQL

n

n

Before few weeks I got a book from Oreilly Blogger Review program to review a book. I took enough time to read this book. This book covers most of the new trends and features in SQL Server 2012 with good explanation.

nn

I was addicted to the first chapter to get to know about the SQL Server Data Tools. Because previously I was studying development things like C# and Visual Studio because of that I was not able to find out about SQL Server Development Tools. With SQL Server 2012 SQL Server Data Tools were introduced. In the first chapter completely describes SSDT from the point of Developers and Dev DBAs.

nn

All over the book, I didn’t feel like reading a tech book. It is well organized and I felt that I read a storybook. But this book is not good for absolute beginners who are looking for SQL Server Development. And this book also balanced one, It doesn’t cover only XML or CLR. It’s covering most of the topics with useful things. This is like an essence for database developers who uses SQL Server 2012 or who are planning to use SQL Server 2012.

nn

Each chapter had a lot of things to learn. Actually, this is a good book. Read it if you are a dev DBA in SQL Server 2012!!! Programming Microsoft SQL Server 2012 was a pretty interesting book for me.

nn

Tags

nn

n
• SQL

n

• SQL Server 2012

n

• SQL Server

n

n

At the beginning of this week, I got a task from Susantha to modify a stored procedure. After the modification, I had to test to compare the result sets before modification and after modification. That procedure only returns a data table. So I had so many test data on another table. Then I wrote a script to go through the script one by one. Actually, I wanted to check whether those results are identical. I mean not the table structure. I wanted to perform a data comparison.

nn

DECLARE @COUNTN INT;nDECLARE @COUNTO INT;nDECLARE @COUNTU INT; nnCREATE TABLE #TEMPN ( \--Columns Here )  nCREATE TABLE #TEMPO ( \--Columns Here )  nCREATE TABLE #TEMPU ( \--Columns Here )  nnINSERT INTO #TEMPN \--Select From new table INSERT INTO #TEMPO \--Select From old table SELECT @COUNTU \= COUNT(1)FROM #TEMPUSELECT @COUNTN \= COUNT(1)FROM #TEMPNSELECT @COUNTO \= COUNT(1)FROM #TEMPO  nnIF @COUNTN <> @COUNTO BEGIN  n        SELECT \* FROM #TEMPN SELECT \* FROM #TEMPO PRINT 'ERROR' END  n  nIF @COUNTN <> @COUNTU BEGIN  n        SELECT \* FROM #TEMPN SELECT \* FROM #TEMPO PRINT 'ERROR' END  n  nIF @COUNTU <> @COUNTO BEGIN  n        SELECT \* FROM #TEMPN SELECT \* FROM #TEMPO PRINT 'ERROR' END  n  nDROP TABLE #TEMPNDROP TABLE #TEMPODROP TABLE #TEMPU n

nn

This will check whether your data in two tables are identical or not. if there’s any mismatch it will print an error and show both result sets. And there’s another issue on this script if one table returns NULL and the other table returns empty table it will take it as matched. Then I corrected it again.

nn

DECLARE @COUNTN INT;nDECLARE @COUNTO INT;nDECLARE @COUNTU INT;  nnCREATE TABLE #TEMPN ( \--Columns Here )  nCREATE TABLE #TEMPO ( \--Columns Here )  nCREATE TABLE #TEMPU ( \--Columns Here ) nnINSERT INTO #TEMPN \--Select From new table INSERT INTO #TEMPO \--Select From old table SELECT @COUNTU \= COUNT(1)FROM #TEMPUSELECT @COUNTN \= COUNT(1)FROM #TEMPNSELECT @COUNTO \= COUNT(1)FROM #TEMPO  nnIF @COUNTN <> @COUNTO BEGIN  n        SELECT \* FROM #TEMPN SELECT \* FROM #TEMPO PRINT 'ERROR' END  n  nIF @COUNTN <> @COUNTU BEGIN  n        SELECT \* FROM #TEMPN SELECT \* FROM #TEMPO PRINT 'ERROR' END  n  nIF @COUNTU <> @COUNTO BEGIN  n        SELECT \* FROM #TEMPN SELECT \* FROM #TEMPO PRINT 'ERROR' END  n\--New Code Block here  n  nIF EXISTS(#TEMPO) BEGIN  n        IF NOT EXISTS(#TEMPN) BEGIN  n            SELECT \* FROM #TEMPN SELECT \* FROM #TEMPO PRINT 'ERROR' END  n    END   n  n  nIF EXISTS(#TEMPN) BEGIN  n        IF NOT EXISTS(#TEMPO) BEGIN  n            SELECT \* FROM #TEMPN SELECT \* FROM #TEMPO PRINT 'ERROR' END  n    END nnDROP TABLE #TEMPNDROP TABLE #TEMPODROP TABLE #TEMPUn

nn

Tags

nn

n
• SQL

n

• Comparing tables

n

• Table

n

• Identical Tables

n

• T-SQL

n

• TSQL

n

• Table Compare

n

• SQL Server

n

n

I got a few questions to answer quickly in last week just like a test. Then I suddenly answered then after that I tried to find correct answers to them. I found answers for most of them. And one of them was there as it was.

nn

n

A Windows server has 32 GB of memory and dedicated for SQL Server database. Every time you start the server the memory utilization of SQL Server gradually increases, until it takes almost all the memory and remains there for days even when there is no database activity. What will you do ?

n

nn

Actually, I answered

nn

n

We can set maximum memory limit. Some how gradually SQL Server uses available memory.

n

nn

I knew that SQL Server uses and usually doesn’t release memory, although it doesn’t need to use it. But I didn’t know why is that. Then I got to know that Not releasing memory is a feature of SQL Server. On servers, we don’t run our day to day applications and most of the times we have allocated separate box for SQL Server. Then nobody will use that memory. If nobody uses that why should we release and allocate again and again? When we are discussing this problem our production server had reached its maximum memory too. And this is not our production servers.

nn

Preethi told that this happens often. People who don’t know about SQL Server behaviour when saw “task manager” like above they are suggesting to double the memory of the server. Then until it takes a few days they are happy. Again they are having the same issue. Actually, it’s not a problem with SQL Server.

nn

If you really want to release that memory you can easily restart SQL Server. Then it begins everything from the beginning. Otherwise, you can execute those commands.

nn

n
• DBCC FREEPROCCACHE

n

• DBCC FREESYSTEMCACHE

n

• DBCC FREESESSIONCACHE

n

nn

But even in the TechNet, they haven’t mentioned that it is recommended running those commands against production servers. Somehow as I think theirs no need to flush memory manually. Because if you are using SQL Server on your laptop, every time that you restart your laptop it will flush again and again. In production servers, we don’t need to flush it manually. Let SQL Server use that memory as it wants.

nn

If you are using one box to install SQL Server and Application both you can set maximum memory limit to SQL Server. Then it will not exceed that limit. But I found this thread from SQLServerCentral.

nn

http://www.sqlservercentral.com/Forums/Topic982342-1550-1.aspx

nn

In that thread he is saying that SQL Server exceeds that maximum memory limit. But technically it should not happen. And I haven’t tested it also. But there may be a reason for that too. If you are setting maximum memory limit you should restart SQL Server. If you don’t restart it, it uses previous memory limit. Somehow below link says another thing. I have to learn about it further.

nn

n

Yup. Perfectly normal.
nMax server memory is the max size of the buffer pool, the memory area that contains the data cache, plan cache and a whole bunch of other caches. SQL also uses memory outside the buffer pool for things like backup buffers, thread stack, linked server drivers, CLR and a few other things. This is outside of the buffer pool, so it’s not part of ‘max server memory’
nOn 32-bit SQL, that’s referred to as MemToLeave (memory to leave unallocated when assigning the buffer pool). On 64 bit that term has no meaning.
nhttp://www.sqlservercentral.com/Forums/Topic1197388-1550-1.aspx

n

nn

Tags

nn

n
• Memory

n

• Architecture

n

• SQL Server

n

n

I have seen in many video tutorials they have different tabs for Editor, Results, Messages and Execution Plans. And they are stretched over the complete screen. Most of the times need to resize the result area to see our results. Actually, it’s annoying me. Then I noticed in one of Paul Randal’s video he uses Separate tabs for Results editor and messages in SQL Server Management Studio (SSMS). Then I wanted to find how to get those tabs to my SSMS (Often I call it SMSS don’t know why Winking smile).

nn

n
1. n

   Open you SQL Server Management Studio (SSMS)

   n

n

2. n

   Go to Tools –> Option

   n

n

3. n

   Check “Display results in a separate tab” and “Switch to results tab after the query executes” both.

   n

n

4. n

   Click ok and enjoy Results like this.

   n

n

nn

If you don’t like to use it always you can use this option to a single script by click on Query option on the toolbar and follow 3rd step.

nn

Tags

nn

n
• SSMS

n

• SQL Server Management Studio

n

• SQL Server

n

n

Previously I wrote a post about CTE (Common Table Expressions). And Common Table Expressions are valid for a single statement only. But a single statement can use many CTEs. And statement also should be within the same batch.

nn

USE AdventureWorks2012nGOnn--Defining CTEnWITH ctePerson AS(n    SELECT * n    FROM Person.Person)n    --First Statement will run n    SELECT FirstName FROM ctePerson n    --Second statement will occur an errorn    SELECT FirstName FROM ctePerson;n

nn

And in my previous post, I have mentioned about many CTEs for one statement. And there is another important note that is to be careful when you are naming CTEs. Take a look on below example.

nn

USE tempdbnGOnn--Creating two tablesnCREATE TABLE cteSampletbl1(id int,name varchar(50)n)nGOnnCREATE TABLE cteSampletbl2(id int,name varchar(50)n)nGOnn--Populating tablesnINSERT INTO cteSampletbl1 VALUES(1,'Satheeq'),n(2,'Preethi'),n(3,'Dinesh'),n(4,'Angelo')nGOnnINSERT INTO cteSampletbl2 VALUES(1,'Shamil'),n(2,'Hasitha'),n(3,'Abhinandana'),n(4,'Susantha')nGOnn/*n    This CTE is defined with existing table name without any errorn*/nWITH cteSampletbl2AS(n    SELECT * FROM cteSampletbl1)n    --This statement uses CTEn    SELECT * FROM cteSampletbl2n    --This statement uses existing tablen    SELECT * FROM cteSampletbl2n--Dropping tablesnDROP TABLE cteSampletbl1nDROP TABLE cteSampletbl2nGOn

nn

Below example it lets us create a CTE with a name of an existing table without any warnings or errors. And it gives priority to CTE in the first statement. Then it uses the existing table in the second statement. So it would be a best practice to use a separate prefix for CTEs. Then take a look into the next sample which is taken from Dinesh’s presentation for SQL Server Universe Group.

nn

USE tempdbnGOnn--Creating two tablesnCREATE TABLE cteSampletbl1(id int,name varchar(50))nGOnn--Populating tablesnINSERT INTO cteSampletbl1 VALUES(1,'Satheeq'),n(2,'Preethi'),n(3,'Dinesh'),n(4,'Angelo')nGOnn--This CTE got errornWITH cteSampletbl1AS(n    --Same name could not be used as name and in CTEn    SELECT * FROM cteSampletbl1) nSELECT * FROM cteSampletbl1n    --This CTE don’t have any errornnWITH cteSampletbl1AS(n    --full name of object should providen    SELECT * FROM dbo.cteSampletbl1)nSELECT * FROM cteSampletbl1nn--Dropping tablesnDROP TABLE cteSampletbl1GOn

nn

You can’t use the same table name as the name of the CTE and inside the CTE.

nn

Tags

nn

n
• SQL

n

• CTE

n

• T-SQL

n

• TSQL

n

• SQL Server

n

n