Category: Uncategorized

What is MongoDB?

MongoDB is an open-source document-oriented database. MongoDB is a NoSQL database. It is written in C++. MongoDB is a free and cross-platform database. The community version of MongoDB is free and do not need to pay for the license. The paid version gives you support and access to some proprietary tools that makes it easy to work with the database.

Features of MongoDB are:

  1. Distributed database
  2. Highly available NoSQL database
  3. Supports horizontal scaling (Different parts of database table are distributed across multiple mongodb instances and load balanced)
  4. Geographic distribution is built-in and easy to use

MongoDB stores data in flexible format JSON-like documents. This means fields can vary from document to document, and data structure can be changed over time. Instead of using tables and rows like in a relational database, MongoDB architecture consists of collections and documents. Each database contains collections, each collection contains one or more documents. Each document can be different with varying number of fields.

How does a collection differ from a table?

Instead of tables, a MongoDB database stores its data in collections. A collection holds one or more BSON documents.

Documents are similar to records or rows in a relational database table. Each document has one or more fields, fields are similar to columns in a relational database table.

MongoDB licenses – Community versus Enterprise

You need to pay for the license for business use. Copyrights of source code are with the company.

Powershell – Remoting over HTTP

By default, remoting will use WMI for communications using ports 5985 and 5986

If for some reason, you cant use WMI, say server is in DMZ. You can specify to use HTTP for remoting instead of WMI, which will use ports 80 and 443.

To use these listeners, WinRM needs to be configured to listen on ports 80 and 443 using the WinRM command or group policy. Once enabled, remoting works as normal.

How do you turn ON remoting in older machines (older than windows server 2012)?

Remoting is enabled by default on Windows 2012 and above. Older versions of windows need to have remoting enabled on the server.

On the machine which will receive commands, run Enable-PSRemoting

One could also push out the enable switch via GPO, this method can be a quick and easy way to enable remoting on all servers within the Active Directory domain.

SQL Database Backup Error – File manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized.

Backups are one of the critical and primary functions as a DBA. Recently I noticed a failed backup job that runs apart from our enterprise backups. We use these individual backups to refresh non-prod environments for developers to run their queries. I have had these jobs fail with the following error:

Msg 3023, Level 16, State 2, Line 1
Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. 
Reissue the statement after the current backup or file manipulation operation is completed.

The job fails as soon as I initiated a re-run. So naturally I went to check what processes are running currently. To do this, I run sp_WhoIsActive. I noticed that a scheduled job from enterprise backup system has been running but ran a little longer than usual. This seems to be causing the error when I tried to run another backup through the SQL Agent job on the same database.

The resolution is that i just waited for the scheduled backup job to complete and re-ran the SQL Agent job and it completed successfully.

Another possible reason for this error is that if we perform a shrink operation in parallel to backup operation. In general, shrink is NOT recommended unless you know that you have deleted a large set of data releasing space that you would like to reclaim.

Parameter Sniffing in SQL Server

If you notice that one of your usual query sometimes runs fine and the same query sometimes runs slow. It may be due to a common phenomenon called parameter sniffing.

A query becomes a victim of Parameter sniffing when SQL server caches a plan for a particular parameter. The same query when run again with a different parameter runs using the cached plan instead of getting a brand new plan. In general, this is by design and is a good thing since every recompile burns more CPU cycles.

Folks usually try the following to deal with the above scenario

  1. Restart windows
  2. Restart the SQL Server Service
  3. Fail over the cluster
  4. DBCC FREEPROCCACHE
  5. Rebuild indexes, which really is just same as clearing out the cache
  6. Update your statistics, which really is just same as above
  7. DBCC FREEPROCCACHE’s for specific plans

The right way to deal with parameter sniffing emergency is:

  1. Find the ONE bad plan in cache
  2. Save the ONE bad plan to disk for troubleshooting later
  3. Free the ONE bad plan from memory

You could run the open source stored proc sp_BlitzCache to find the bad plan. This store proc lists the top 10 worst queries in the plan cache. You review each of them and pin point the query plan where the estimates versus actuals are off by more than 10x.

Can we use monitoring software to catch parameter sniffing?

Typically when we hear from developers or end users that a query is running long. We hop onto the terminal of monitoring tool and look for PLE (page life expectancy). However, the long running queries may not the root cause for flushing out the cache. It is sometimes the quick running queries with large memory grants that drop the PLE down abruptly.

When are we susceptible to parameter sniffing?

Anytime you have one piece of code that needs to handle a varying number of rows, we are probably gonna have to worry about parameter sniffing.

Changing the information for a database mail account

It happens so that sometimes your mail server gets moved from one instance to another, one platform to another. Recently we have had to move our in house exchange mail server to Office365.

Here is a script to update the mail box and account details on an existing database mail account:

EXECUTE msdb.dbo.sysmail_update_account_sp  
     @account_name = 'SMTP_database_mail'  
    ,@description = 'Mail account for administrative e-mail.'  
    ,@email_address = 'dba@Adventure-Works.com'  
    ,@display_name = 'Database Mail'  
    ,@replyto_address = 'SQLAlerts@Adventure-Works.com'  
    ,@mailserver_name = 'smtp.office365.com'  
    ,@mailserver_type = 'SMTP'  
    ,@port = 587  
    ,@timeout = 60  
    ,@username = 'SQL-Alerts@Adventure-Works.com'  
    ,@password = 'AStrongPasswordGoesHere'  
    ,@use_default_credentials = 0  
    ,@enable_ssl = 1;

Now lets verify what we configured above by sending a test email.

use master
declare @servername varchar(100)
declare @testmsg varchar(100)
set @servername = replace(@@servername,'\','_')
set @testmsg = 'Test from ' + @servername

--send a test message.
exec msdb..sp_send_dbmail
@profile_name = 'DBA_Profile',
@recipients = 'SQL-Alerts@Adventure-Works.com',
@subject = @testmsg,
@body = @testmsg