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'