Run SQL Management Studio as different user

It is helpful sometimes that I have a documentation how to login as different user  from SSMS. It is come in handy.

Press Shift key + Right click on SSMS shortcut
Click on Run as different user

Enter domain\login and password

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {FDC3723D-1588-4BA3-92D4-42C430735D7D} and APPID {83B33982-693D-4824-B42E-7196AE61BB05}

To fix the error from Event Viewer, I refered to MS website

https://connect.microsoft.com/SQLServer/feedback/details/780569/mdw-job-on-mdw-server-generates-event-10016-distributedcom-error-in-system-log

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID 

{FDC3723D-1588-4BA3-92D4-42C430735D7D}

 and APPID 

{83B33982-693D-4824-B42E-7196AE61BB05}

1)    Run Dcomcnfg.exe 
2)    Expand Component Service -> My Computer ->DCOM Config
3)    Locate “Microsoft SQL Server Integration Services 11.0”
4)    Right click and select “Properties”
5)    Select “Security” tab
6)    In Launch and Activate Permissions, click on “Edit”
7)    Grant “Local Launch”, “Local Activation” permissions for SQL Agent Service account 
8)    Restart all Data collectors, Check Windows System Event log for any DCOM errors that you were seeing before.

DeadLock Graph from Extended Events

In SQL Server 2012, Deadlock graph is captured in system_health by default.

Under Management, right click on package0 under system_health, View target Data

Filtered by name contains xml

In SQL Server 2008 R2

Deadlock events can be queried from dynamic management views. I use the following query to get deadlock graph.

;with SystemHealth
As(
Select cast(target_data as xml)as sessionxml
from sys.dm_xe_session_targets st
inner join sys.dm_xe_sessions s on s.address = st.event_session_address
where name ='system_health'
)
Select Deadlock.value('@timestamp','datetime')as DeadlockDateTime
,cast(Deadlock.value('(data/value)[1]','varchar(max)') as xml)as DeadlockGraph
Into uat.dbo.dba3
from systemhealth
Cross apply sessionxml.nodes('//RingBufferTarget/event')as T(deadlock)
where deadlock.value('@name','nvarchar(128)') ='xml_deadlock_Report'

Enjoy my script.

MongoDB - Replication -Part I

I will be running Mongod instance in windows environment. I am planning to create one primary and 2 secondary instances.
First, I have created 3 folders in C:\
c:\MongoRepl\1
c:\MongoRepl\2
c:\MongoRepl\3

I will be using port no 27007 (primary) ,27008 ,27009. I have made sure these ports are allowed incoming/outgoing in firewall.

Now, I will create a standalone mongod instance first. Then, create a database on the instance.

mongod --dbpath c:\MongoRepl\1 --port 27007 --smallfiles --oplogSize 50

open another terminal and connect to Mongod shell and import replication database

mongo --port 27007 --shell replication.js

Now, Cloed the mongod shell. Restarted Mongod instance as a replica.

mongod --replSet Mya --dbpath c:\MongoRepl\1 --port 27007 --smallfiles --oplogSize 50

You will set EMPTYConfig error.

Next, we will start two other mongod instances (secondary), we will join them to replicaset Mya

mongod --replSet Mya --dbpath c:\MongoRepl\2 --port 27008 --smallfiles --oplogSize 50

mongod --replSet Mya --dbpath c:\MongoRepl\3 --port 27009 --smallfiles --oplogSize 50

Now, we will initiate a replica from mongo us initiate command.

rs.initiate()
rs.status()

create a configuration

cfg={
_id:"Mya",
members:[
{_id:0,host:"machinename:27007"},
{_id:1,host:"machinename:27008"},
{_id:2,host:"machinename:27009"},
]
}

Initiate Replica

rs.initiate(cfg)

Check replica status on primary 

rs.status()

Open termainal and start mongo

mongo --port 27008
mongo --port 27009

Read from secondary

I need to run rs.slaveOk() to read from secondary

rs.slaveOk()

Write to Primary

for(var i=0; i< 50000;i++){db.foo.insert({_id:i});sleep(i);}

While for statement is running, check on secondary rowcount

db.foo.count()

FailOver to Secondary

Run the command in Primary

rs.stepDown()

Now new primary is 27008

Now we will close Mongod instance on 27007.

rs.status()
27007 is unreachable since we shut down 27007

Reconfigure

On new primary replica. run config and rs.reconfig(cfg)

cfg={
_id:"Mya",
members:[
{_id:1,host:"machinename:27008"},
{_id:2,host:"machinename:27009"},
]
}

rs.reconfig(cfg)

Now, we have even number node.

Replica set information are stored in local db

use local
db.system.replset.find().pretty()

PowerShell - Automatic Reboot AlwaysOn Server if AlwaysOn Availability group is ready to failover

Recently, we have encountered Secondary replica was in Resolving State and took 3 minutes to take over primary role when we forced reboot primary server from Task scheduler.

I have read the following troubleshooting page from MS http://support.microsoft.com/kb/2833707. There were 3 cases that can cause secondary replica in RESOLVING state. In Case 3, 

"... In order to automatically fail over, all availability databases that are defined in the availability group must be in a SYNCHRONIZED state between the primary replica and the secondary replica. When an automatic failover occurs, this synchronization condition must be met in order to make sure that there is no data loss. Therefore, if one availability database in the availability group in the synchronizing or not synchronized state, automatic failover will not successfully transition the secondary replica into the primary role...."

As we decided we will continue to schedule to reboot the servers on monthly basis scheduled reboot, we will need to make sure AG group is in Synchronized or fail over ready state.

To do so, I created a powershell script based on the following query. The query will check if AG group is ready to fail over.

Select database_name, is_failover_ready from sys.dm_hadr_database_replica_cluster_states where replica_id in (select replica_id from sys.dm_hadr_availability_replica_states)

In my script, I verified Secondary SQL server status in case they are paused/stopped. 
Enjoy script....

## The following PS script check the other replica is ready for failover. If it is ready, the current replica will be rebooted.
## The script can be used for rebooting primary replica or secondary replica automaically. It can be scheduled in Task Scheduler.

##Other Replica Node

$OtherReplica ='Server2'

#Local Host/Server which is going to reboot
$CurrentReplica='Server1'
#$env:COMPUTERNAME

#First, Check Other Replica is online by Pinging the server wait for 4 replies.
if(Test-Connection -ComputerName $OtherReplica -count 4 -ea 0 -Quiet)
 {
       
       # If the other server replies ping, check the status of SQL server
             
       Write 'The Other Server is alive'

       # Get SQL server status of the other server
       $SQLserverState = Get-WmiObject win32_service -ComputerName $OtherReplica -Filter "Name = 'MSSQLSERVER'"|Select State
                   
         If($SQLserverState.State -eq 'Stopped')
         {
                   Write 'Do Not Reboot' -ea Stop
         }
         
         If($SQLserverState.State -eq 'Paused')
          {
                     Write 'Do Not Reboot' -ea Stop
          }

        #If SQL server status of the other server is running, get the failover status from database
        If($SQLserverState.State -eq 'Running')
         {
                           
                    
                   $DBconn = new-object System.Data.SqlClient.SqlConnection("server=$OtherReplica;Trusted_Connection=true");
                   $DBQry  = 'Select min(cast(is_failover_ready as int)) from sys.dm_hadr_database_replica_cluster_states where replica_id in (select replica_id from sys.dm_hadr_availability_replica_states)'
                   $DBconn.Open()
                   $DBcmd = new-object System.Data.SqlClient.SqlCommand ($DBQry, $DBconn);
                   $Reader = $DBcmd.ExecuteReader()
                   while($Reader.Read()){
                   $FailOverStatus = $Reader.GetValue($1)           
                   }
                   
                   # If $FailOverStatus is 1, it is ready to failover. If $FailOverSatus is 0, it is not ready to failover
                
                   If($FailOverStatus -eq 1)
                   {

                    Write 'Server is Ready to Reboot...Rebooting...'
                    Restart-Computer -ComputerName $CurrentReplica -Force
                   }
                     If($FailOverStatus -eq 0)
                   {

                    Write 'Do Not Reboot' -ea Stop
                   }

                   $DBconn.Close()
           }

}
  else
  {
       Write 'Server is offline'
  }

MongoDB - Dynamic_Schema/MongoImport/Cursor/Queries/Sorting

Dynamic Schema
-Polymorphism

Schemaless / Polymorphism

--add new fields easily to schema

Things

{Shape:"Rect",x:3,y:4,Area:12}

{Shape:"Circle",radius:1,Area:3.14}

Mongo Import

Open another command prompt

mongoimport.exe can import 3 types of files CSV,TSV,JSON

mongoimport --db databasename --collection tablename < filename

mongoimport --stopOnError

mongoimport --db pcat --collection products < products.json

Mongo Shell Queries

--show recordcount

db.collection.count()

--show all rows

db.collection.find()

--Show only name column

db.collection.find({},{name:1})

--show all rows nicely

db.collection.find().toArray()

--show only 10 rows nicely 

db.collection.find({}).limit(10).toArray()

--Show only 1 row

db.collection.findOne()

db.collection.find({}).limit(1)

--Skip first 2 and give next 4

db.collection.find({}).limit(4).Skip(2)   -

--Turn off Id

db.collection.find({},{name:1,_id:0})

--Get Name and Brand

db.collection.find({},{name:1,brand:1})

--All fields and 1 row

db.collection.find({}).limit(1)

--if the output is not too big

db.collection.find({}).limit(3).toArray()

--query price = 12.5

db.collection.find({price:12.5})

db.collection.find({price:12.5}).toArray()

db.collection.find({},{price:1})

--Object id is bson type, not string

-- Get one document

db.collection.findOne({"_id": ObjectId("ddadfa5464565fgafaddadffadfadfgffd")})

db.collection.find({price:200},{name:1,price:1})

--greater than

db.collection.find({price: {$gte:200}},{name:1,price:1})

$Operators

Querying

$gte

$gt

$lt

$lte

$or

$not

$nin

$in

$type

$exists

Updating

$inc

$set

$addToSet

--greater than and expression

db.collection.find({price: {$gte:200},available:true},{name:1,price:1,available:1})

--find ac3 is in array

db.collection.find({for : "ac3"}) 

Reaching into Nested Documents

dot notation

find({"x.a":1})

Quiz

Add caption

db.collection.find({for:"ac9",Type:"case"}).toArray()

Sorting

db.collection.find(_).sort(fieldname:direction)

db.collection.find({},{name:1,price:1}).sort({price:1})

-- null is less than number

--sort rows only with price

db.collection.find({price:{$exists:true}},{name:1,price:1}).sort({price:1})

db.collection.find({price:{$exists:true}},{name:1,price:1}).sort({price:-1})

Multiple Sort keys

db.collection.find().sort({lastname:1,firstname:1})

--SQL order by lastname,firstname

Cursors (different from other database)

db.test.find()

type it for more

db.test.find().skip(20).limit(5)

--skip is expensive, 20 is fine if 5000 is expensive.

--sort and skip is done at the server

Example

var cursor = db.test.find()

cursor.hasNext()

MongoDB - JSON/BSON/Documents

MongoDB - Lesson 1 

These are my notes from MongoDB university free DBA class.

MongoDB works on concept of collection and document.

MySQL   |   MongoDB  
Database = Database
Table = Collection
Row = Document
Column = Field
Table Join = Embedded Documents
Primary Key = Default key _id provide by mongodb itself

MongoDB language is based on JSON Language. No Join / No complex transaction.

JSON

Example : Document/Row
{ _id :"A11",
x :"1",
y:"2",
z:{a,b} --embedding
}

Find id = A11

Example: query

db.collection.find({_id: "A11"})

JSON Types (Java Script Object Notation)

- Pure data exchange format
- Document-Oriented

Quiz : How many data types in Json ?

Ans : 6

1. Strings
2. Numbers
3. Booleans(True\False)
4. Null
5. Arrays
6. Objects/Documents

Left hand side - Only String is a valid type

Quiz

Ans:

Person
{"name" : "John",
  "Age" : 25,
 "Address" :
  {"city" : "New York",
     "PostalCode" : 10021
 }
"Phone" :
{"Home": "212-555-1234",
"Mobile": "646-555-1234"
}
}

Binary JSON

Fast Scannability
Data types

1. Binary Data
2. Object ID

BSON Format

-- Mongodb driver

integer = 32 bit (4 bytes)

BSON and Applications

Database Partition and Filegroups

HELLO 2015...

Currently, I am working on removing Data warehouse 2011 partition and filegroup which we do not want to keep it in data warehouse. The Data warehouse uses Range Right partitioning. 

I will use the following Partition Scheme and Function examples. I have bolded the partition scheme and function which I need to remove.

Partition Scheme

CREATE PARTITION SCHEME [TestPartitionScheme] AS PARTITION [YYYYMMPartitionFunction] TO ([FG1], [FG2011], [FG2011], [FG2011], [FG2011], [FG2011], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2012], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2013], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2014], [FG2015])

GO

Partition Function

CREATE PARTITION FUNCTION [TestPartitionFunction](date) AS RANGE RIGHT FOR VALUES (N'2011-01-01T00:00:00.000', N'2011-02-01T00:00:00.000', N'2011-03-01T00:00:00.000', N'2011-04-01T00:00:00.000', N'2012-01-01T00:00:00.000', N'2012-02-01T00:00:00.000', N'2012-03-01T00:00:00.000', N'2012-04-01T00:00:00.000', N'2012-05-01T00:00:00.000', N'2012-06-01T00:00:00.000', N'2012-07-01T00:00:00.000', N'2012-08-01T00:00:00.000', N'2012-09-01T00:00:00.000', N'2012-10-01T00:00:00.000', N'2012-11-01T00:00:00.000', N'2012-12-01T00:00:00.000', N'2013-01-01T00:00:00.000', N'2013-02-01T00:00:00.000', N'2013-03-01T00:00:00.000', N'2013-04-01T00:00:00.000', N'2013-05-01T00:00:00.000', N'2013-06-01T00:00:00.000', N'2013-07-01T00:00:00.000', N'2013-08-01T00:00:00.000', N'2013-09-01T00:00:00.000', N'2013-10-01T00:00:00.000', N'2013-11-01T00:00:00.000', N'2013-12-01T00:00:00.000', N'2014-01-01T00:00:00.000', N'2014-02-01T00:00:00.000', N'2014-03-01T00:00:00.000', N'2014-04-01T00:00:00.000', N'2014-05-01T00:00:00.000', N'2014-06-01T00:00:00.000', N'2014-07-01T00:00:00.000', N'2014-08-01T00:00:00.000', N'2014-09-01T00:00:00.000', N'2014-10-01T00:00:00.000', N'2014-11-01T00:00:00.000', N'2014-12-01T00:00:00.000', N'2015-01-01T00:00:00.000')

GO

In the example, Range Right Partition Function keeps data earlier than 2011-01-01T00:00:00.000 (Lowest boundary) will go to the file group FG1 .  In Data warehouse database, I have created a view which will show me tables which are partitioned and their file groups.  I gave credit to Kendra Little from

http://www.brentozar.com/archive/2013/01/sql-server-table-partitioning-tutorial-videos-and-scripts/

CREATE VIEW [dbo].[FileGroupDetail]

AS

SELECT  pf.name AS pf_name ,

        ps.name AS partition_scheme_name ,

        p.partition_number ,

        ds.name AS partition_filegroup ,

        pf.type_desc AS pf_type_desc ,

        pf.fanout AS pf_fanout ,

        pf.boundary_value_on_right ,

        OBJECT_NAME(si.object_id) AS object_name ,

        rv.value AS range_value ,

        SUM(CASE WHEN si.index_id IN ( 1, 0 ) THEN p.rows

                    ELSE 0

            END) AS num_rows ,

        SUM(dbps.reserved_page_count) * 8 / 1024. AS reserved_mb_all_indexes ,

        SUM(CASE ISNULL(si.index_id, 0)

                WHEN 0 THEN 0

                ELSE 1

            END) AS num_indexes

FROM    sys.destination_data_spaces AS dds

        JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id

        JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id

        JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id

        LEFT JOIN sys.partition_range_values AS rv ON pf.function_id = rv.function_id

                                                        AND dds.destination_id = CASE pf.boundary_value_on_right

                                                                                    WHEN 0 THEN rv.boundary_id

                                                                                    ELSE rv.boundary_id + 1

                                                                                END

        LEFT JOIN sys.indexes AS si ON dds.partition_scheme_id = si.data_space_id

        LEFT JOIN sys.partitions AS p ON si.object_id = p.object_id

                                            AND si.index_id = p.index_id

                                            AND dds.destination_id = p.partition_number

        LEFT JOIN sys.dm_db_partition_stats AS dbps ON p.object_id = dbps.object_id

                                                        AND p.partition_id = dbps.partition_id

GROUP BY ds.name ,

        p.partition_number ,

        pf.name ,

        pf.type_desc ,

        pf.fanout ,

        pf.boundary_value_on_right ,

        ps.name ,

        si.object_id ,

        rv.value;

GO

--------------------

Now I want to know which table are partitioned and number of rows. I ran the following scripts

select 

partition_filegroup,

object_name,

sum(num_rows)

from ph.FileGroupDetail

where partition_filegroup in('FG2011')

group by partition_filegroup,object_name

To find out how many partition I need to remove from the partition.

select * from dbo.FileGroupDetail

where object_name = 'Table Name' and partition_filegroup = 'FG2011'

I am following Kendra Little video and built switch out tables for each partitioned table. In this example, we have 4 partition

Create a switch out table in the file group which you need to drop

CREATE TABLE [TEST_SWITCHOUT](

[CHTIDDD] [decimal](3, 0) NOT NULL,

[CHSEQQ] [decimal](5, 0) NOT NULL,

[CHTYPEE] [char](3) NOT NULL,

)

ON [FG2011]

GO

SWITCH OUT Script for each partition of the table

RAISERROR ('Switching out.',0,0)

ALTER TABLE TEST

SWITCH PARTITION 2 TO dbo.TEST_SWITCHOUT;

GO

Truncate table dbo.TEST_SWITCHOUT

Go

RAISERROR ('Switching out.',0,0)

ALTER TABLE TEST

SWITCH PARTITION 3 TO dbo.TEST_SWITCHOUT;

GO

Truncate table dbo.TEST_SWITCHOUT

Go

RAISERROR ('Switching out.',0,0)

ALTER TABLE TEST

SWITCH PARTITION 4 TO dbo.TEST_SWITCHOUT;

GO

Truncate table dbo.TEST_SWITCHOUT

Go

Drop table dbo.TEST_SWITCHOUT

Go

MERGE BOUNDARY POINTS

We are ditching the following months which we want to remove.

--2011

ALTER PARTITION FUNCTION TestPartitionFunction()

MERGE RANGE ('2011-01-01T00:00:00.000')

ALTER PARTITION FUNCTION TestPartitionFunction()

MERGE RANGE ('2011-02-01T00:00:00.000')

ALTER PARTITION FUNCTION TestPartitionFunction()

MERGE RANGE ('2011-03-01T00:00:00.000')

ALTER PARTITION FUNCTION TestPartitionFunction()

MERGE RANGE ('2011-04-01T00:00:00.000')

REMOVE FILE GROUP and FILE

Use YourDatabase

Alter Database YourDatabase Remove File  FG2011

go

USE [YourDatabase]

GO

ALTER DATABASE [YourDatabase] REMOVE FILEGROUP [FG2011]

GO

IF you have this error, run the script to see which object is still in the file group.

the file cannot be removed because it is not empty

select * from sys.partitions p

inner join sys.allocation_units a on a.container_id = p.hobt_id

inner join sys.filegroups f on f.data_space_id = a.data_space_id

where f.name='FG2011'

select * from sys.objects

where object_id = '949578421'