SCDPM – Protecting SQL 2012 & above.

To protect SQL Server 2012 or SQL Server 2014, the following must be done.

• Login to SQL Server Management Studio

• Add the system account NTAuthority\System to the Sysadmin group on SQL Server
• DPM cannot protect databases that are stored on remote SMB stores
• Availability group replicas are configured as read-only
• When you perform an alternate location recovery for a partially contained database, you must ensure that the target SQL instance has the Contained Databases feature enabled

Hope the above illustration helps

 

 

Azure–Powershell (Updated)

Recently the PowerShell for Azure, there’s an update that you can download from the GitHub

The Link is located in here.

You need to have the Azure SDK installed first.  The installation file can be download from this link.

Hope this helps.

keywords : Azure, powershell, azure command-line,

Hyper-V VSS Writer State : [7] Failed Last error : Timed out

Had an issue of the Hyper-V VSS when I typed to check.

vssadmin list writers

In the event viewer I got as the following :

\

I want to get the VSS back to be stabilize but not wanting to restart the server, therefore I restart a service.

Service to restart : Microsoft Virtual Machine Management

Keyword :

Volume Shadow Copy Service error: Unexpected error querying for the IVssWriterCallback interface. hr = 0x80070005, Access is denied.. This is often caused by incorrect security settings in either the writer or requestor process.Operation:  Gathering Writer Data Context:  Writer Class Id: {e8132975-6f93-4464-a53e-1050253ae220}  Writer Name: System Writer  Writer Instance ID: {bc497fa6-6627-47fb-a005-ac94fa83904e}Error : 8194 vssadmin list writer Hyper-V State 7 timed out

SCDPM–Job Failed to Cancel or Hung or Stuck BUT Cannot be remove

Environment
SCDPM 2012 SP1

It’s been hours the job has been cancelled but couldn’t be remove from the DPM job.  Can’t execute the next job due to the job is still in progress to cancel.   However, found the issue is with the SQL Database that is holding the job from being release. 

In the SCDPM console, it displays as below :

The following steps were carried out :

• Launch the SQL Server Management Studio (<right-click> run as Administrator)

Once it’s launched, ensure DPM DB is connected

Copy the script below :

USE DPMDB BEGIN TRAN -- mark replica as invalid if there was some operation happening on that replica UPDATE tbl_PRM_LogicalREplica SET Validity = 1 -- Invalid WHERE OwnerTaskIdLock IS NOT NULL AND Validity <> 5 AND -- ProtectionStopped Validity <> 6 -- Inactive -- Release all the locks held UPDATE tbl_PRM_LogicalREplica SET OwnerTaskIdLock = null, Status=8 if (select COUNT(name) from tbl_AM_Agent where Name like 'DPM RA v2%') > 0 begin exec sp_executesql N'UPDATE tbl_RM_ShadowCopy SET ArchivetaskId = NULL, RecoveryJobId = NULL' end UPDATE tbl_ARM_Datasource SET Status = 0, OwnerLockId = NULL DELETE tbl_RM_DatasourceServerlock DELETE tbl_RM_ShadowCopyLocks -- Set All running tasks and jobs to failed UPDATE tbl_TE_TaskTrail SET ExecutionState = 3, LastStateName = 'Failure', StoppedDateTime = GetUtcDate() WHERE ExecutionState NOT IN (2,3) UPDATE tbl_JM_JobTrail SET JobState= 'Failed', EndDateTime = GetUtcDate() WHERE jobstate= 'Execute' OR jobstate= 'Retire' -- unreserve resources held UPDATE tbl_MM_Global_Media SET ReservationLevel = 0, ReservationOwnerMMId = null UPDATE tbl_MM_Global_Drive SET ReservationLevel = 0, ReservationOwnerMMId = null UPDATE tbl_MM_Global_IEPortResource SET ReservationLevel = 0, ReservationOwnerMMId = null COMMIT TRAN

 

• Click on New Query
• Paste the Script into the Query Column
• Click Execute

• Check on the OUTPUT column below
• Close the SCDPM console and open it back.  It should clear the status and be able to execute whatever job that is outstanding.

Hope this helps.   Works on SCDPM 2012 R2 too. 

Resource of the script is from here.

Thank you.

keywords : SCDPM, Data protection manager 2012 SP1, SCDPM 2012 SP1, SCDPM 2012 R2, Job cancel but hang, cancel job failed

PowerShell–Get-VM command is not recognize or missing

Currently I’m supporting one of the customer, found out that the PowerShell is in version 1.0 in the SCVMM.  Therefore, I tried to execute a script using the get-vm but it says as not recognize.

The message was :

PS C:\Users\Administrator\Desktop> Get-VM The term 'Get-VM' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.

As a workaround, I’ve execute the command as below :

Import-Module virtualmachinemanager

Then the get-vm command works.

keywords : SCVMM, get-vm missing, get-vm command not recognize, powershell command not recognize.

PowerShell–Listing of Folder Permission, export to CSV

Today would like to share with you about a way to list down all the Folder and subfolder permission into a CSV format.  This is good especially for internal or external audits.

There are two options that you can run.

• Run in the File Server (provided you have PowerShell installed)
• Run from your Computer (Map the File Server Drive, and also provided you have PowerShell installed in your computer)

The coding is as below :

# This is the file specified for the Output of CSV. Make the necessary change $OutFile = "C:\Download\Permission.csv" $Header = "Folder Path,IdentityReference,AccessControlType,IsInherited,InheritanceFlags,PropagationFlags" Del $OutFile Add-Content -Value $Header -Path $OutFile # This is the top path of where want to scan the permission from.  This will include the sub folders $RootPath = "C:\Download\Driver" $Folders = dir $RootPath -recurse | where {$_.psiscontainer -eq $true} foreach ($Folder in $Folders){     $ACLs = get-acl $Folder.fullname | ForEach-Object { $_.Access  }     Foreach ($ACL in $ACLs){     $OutInfo = $Folder.Fullname + "," + $ACL.IdentityReference  + "," + $ACL.AccessControlType + "," + $ACL.IsInherited + "," + $ACL.InheritanceFlags + "," + $ACL.PropagationFlags     Add-Content -Value $OutInfo -Path $OutFile     }}

Note : Make changes to the Pink in colour according to your CSV file and which path you want to scan your File Server and export out the permission listing.

The output will be something like below when open with Excel :

I’m executing on my PC, that’s the reason I have the output as above.

Thank you and hope this helps.

keywords : ACL, folder permission, powershell, File Server, fileserver, Access list, access control list, permission to CSV

Azure – Removing / Deleting Resource Group with PowerShell

Since Azure introduced the new portal, the recommendation to create is as below :

1. Create Resource Group
2. Create Azure Storage Account
3. Create Azure VNET

That is the 3 basic items that MUST be created first before creating any VMs.

So now I face the “horror of cleaning up” after conducting some demos. The cleaning up is quite a challenge through the GUI Management Portal.  Therefore I need to clear the entire Resource Group.  Before I can delete that, I need to delete all the contents in the resource group. It’s very tedious.

Now I’ve found a way that I would like to share.

Step 1

Update your local PowerShell with the Azure SDK.   If not download it from here.

Step 2

Once it’s installed, then you should have the Azure commands added into your PowerShell.

Launch the Run ISE as Administrator

Minimize the PowerShell Window.

Step 3

• Launch IE (Internet Explorer)
• Go to https://portal.azure.com
• Login with your Azure Account
• Open another IE tab.  Enter the https://resources.azure.com/ to access to Azure Resource Explorer.

• Expand the left panel
• Browse to the Resource Group

• Take note of the PowerShell command (which is going to help later).

Step 4

• Maximize the PowerShell that was launch earlier (ISE) in Step 2
• Login using the command of Login-AzureRMAccount

• Take note of the SubscriptionId
• The command to remove the entire Resource Group is as follow :

Remove-AzureRMResource –ResourceId /subscriptions/SubscriptionId/resourceGroups/resourcegroupname/ –ApiVersion 2014-04-01 –Force

Note : Only make changes to the pink colour syntax.  The first is your subscription ID information that you get from the top when you login to Azure and the second one is the resource group name. 

• If you’re not so sure of the command, then refer to the command that you noted earlier in Step 3 from the Azure Resource Explorer.
• As example I would like to delete off the entire Resource Group of RG-CANADA-CENTRAL-01 in my subscription then I type the following :

• Once it’s completed removing the Resource Group, a True word is indicated.

So hopefully the above helps you as it has helped me in cleaning up the Resource Group.

keywords : azure, resource explorer, manage azure, azure portal , resource group, housekeeping resource group, new azure portal, manage azure portal

How to Remove Azure Account (Cached) using PowerShell

Recently I’ve done quite a lot of testing using various accounts to Azure.  All the commands are executed in the PowerShell ISE (Run ISE are Administrator).

The PowerShell should have the Azure Commands in it already.  If not, can download from here.

Select the Install from the Command-line tools

When I tried to Get-AzureAccount, it displayed more than one account and subscriptions

I need to remove the other subscriptions.  Therefore I perform as follow :

Execute the command as below :

• Get-AzureAccount | format-table id

Once displayed, I execute to remove the account using

• Remove-AzureAccount account-name

Note : Just replace the account-name with the account that needs to be deleted.

Another way is to remove all the accounts then re-add it back. 

To do this execute it as below to remove all the accounts :

• Get-AzureAccount | ForEach-Object {Remove-AzureAccount $_.ID -Force}

To add the account that I’m working on, use :

• Add-AzureAccount

 

keys : add azure account, remove azure account in cache, Azure powershell command line, command-line, azure subscription, AzureSubscription, get-azureaccount, remove-azureaccount, Azure SDK

Reset Local Administrator Password for Azure VM

Recently I had set up quite a couple of VMs in Azure and one of the VMs local administrator’s password I’ve keyed in wrongly and totally forgotten what is it anymore.

So in the Azure new portal there’s a feature to reset the password.

Local Administrator username : bkhoo

The steps taken are as below :

• Launch the new portal : https://portal.azure.com

• On the left menu bar click on the Virtual Machines
• In the Virtual Machines, select the specific Virtual Machine that want to reset the local administrator password.

• In the settings menu on the right, click Reset password .

• Key in the new password and confirm password, then click on Reset 

Then I test it, I can now RDP into the VM using the new reset password .

keywords : forgot password,local administrator password, Azure VM, Virtual machine password, how to reset local administrator password in virtual machine, VM, VMs, virtual machines

Killing a Hung VM in Task Manager (Another PowerShell command)

This is another command of how to kill a Hung VM by identify the Process ID using PowerShell command :

For Windows 2012

Get-WmiObject -Namespace root\virtualization -class msvm_computersystem | select elementname, operationalstatus, processid, name| ft –auto

For Window 2012 R2

Get-WmiObject -Namespace root\virtualization\v2 -class msvm_computersystem | select elementname, operationalstatus, processid, name| ft –auto

Once the Process ID is obtain, launch the Task Manager and kill the VMWP.exe process with the correspondence Process  ID to that particular VM.

Hope this helps.

keywords : hang VM, hung VM, stuck VM, Hyper-V virtualization stuck Server 2012 R2, vmwp.exe, killing vwmp.exe

Killing a Hung VM in Task Manager

Sometimes the VM we tried to stop hangs.  So in order to kill the process using the Task Manager, we can end process of VMWP.exe.  But the next question is which one.  Each VM has a unique Process ID.

Therefore, I launch the elevated Powershell by executing the following command below

Get-WmiObject win32_process -Filter "Name like '%vmwp%'" | Select-Object ProcessID, @{Label="VMName";expression={(Get-VM -Id $_.commandline.split("")[1]|Select-Object VMName).VMName}}|ft –AutoSize

So I go back to the Task Manager and Right-Click on it and end task,base on the Process ID of that particular VM.

Hope this command helps you as it helped me.

keywords : hang VM, hung VM, stuck VM, Hyper-V virtualization stuck Server 2012 R2, vmwp.exe, killing vwmp.exe

DPM 2012 Reporting Issue (ID : 220)

After upgrading from SCDPM 2012 SP1 to SCDPM 2012 R2, I hit the issue when I tried to configure the Reporting to be send out periodically in the Report Module of SCDPM 2012 R2.

The steps taken was the following :

• Launch the Reporting Services Manager (in my example is SQL 2008 R2, since it’s upgraded gradually from SCDPM 2012 SP1 to SCDPM 2012 R2)
• Ensure to the correct database that I’m pointing to  (e.g. MSDPM2012)
• Navigate to E-Mail Settings.
• Key in the valid Sender Address and SMTP Server.
• Click Apply

• Launch the SCDPM 2012 R2 – Reporting
• Configure the schedule reporting with emails, tested with no issues.keywords : SCDPM 2012, DPM cannot enable e-mail subscription for reports unless SMTP details are configured in the remote SQL Server Reporting Service Installation. SMTP details need to be configured manually when an existing SQL Server is chosen to be used for DPM during DPM setup.  Please enter the SMTP server name, SMTP port number and SMTP From address manually in the SQL Server Reporting Services configuration File.

Configuration file path : c:\Program Files\Microsoft DPM\SQL\MSRS10.MSDPM2010\Reporting Services\ReportServer\RSReportServer.config

The SMTP Server  details on the SMTP Servers tab in the Options dialog enables only DPM alert notifications. For more details, see DPM Help (ID : 220)

SCDPM Report error, Systems Center Data Protection Manager Report Error, cannot configure email distribution in DPM report

Systems Center Data Protection Manager 2012 R2 Keeps Crashing – ID:917

Have managed to upgrade from SCDPM 2012 SP1 to SCDPM 2012 R2.  The DPM agents and the Protection Groups are still intact.

However, when I tried to edit or remove the Protection Group, the SCDPM 2012 R2 Administrator console keeps crashing and restart the DPM Administrator Console

After finding out, the issue is NOT the Data Protection Manager, but it’s the SQL that is storing the DPM Database.  The SCDPM 2012 R2 has also been patched with UR6, however it still crash whenever I tried to edit or remove the Data Protection.

Therefore these are the steps taken.

• Take a backup of the DPM Database
• Right-click on the SQL Management Studio and select “Run as administrator”

• Once the SQL Management launched, right-click on the DPMDB and select “New Query”
• Copy and paste the following SQL Script

update tbl_IM_ProtectedGroup SET IMCatalogXML = replace(cast(IMCatalogXML as nvarchar(max)), '<AllowedBackupType>FullBackup', '<AllowedBackupType>Regular') from tbl_IM_ProtectedGroup where IMCatalogXML like '%<AllowedBackupType>FullBackup%'

• Click Execute and it should show the results.  The example is as above picture. 
• Launch the SCDPM 2012 R2 Administrator Console, and it’s successful can edit or remove the Protection Group.

keywords : SCDPM 2012 R2, upgrade of SCDPM 2010 to SCDPM 2012 R2, Error : 917, Systems Center Data Protection keeps crashing after upgrade in place, in-place, inplace, Restarting Administrator Console.

"Cannot connect to WMI provider. You do not have permission or the server is unreachable" in SQL Server

 

When trying to open MS SQL Server Configuration Manager, a dialog may appear..

Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]

Cause :
An issue with the compiled SQL Configuration Manager GUI’s communication with WMI.

A work around is as follow :

Open a command prompt (elevated, run as administrator)

Then Navigate to your SQL version’s shared directory (cd <version path>):

 

SQL 2008	C:\Program Files (x86)\Microsoft SQL Server\90\Shared\
SQL 2008 R2	C:\Program Files (x86)\Microsoft SQL Server\100\Shared\
SQL 2012	C:\Program Files (x86)\Microsoft SQL Server\110\Shared\
SQL 2014	C:\Program Files (x86)\Microsoft SQL Server\120\Shared\

After that execute the following command :
mofcomp sqlmgmproviderxpsp2up.mof

After that reboot the server to get all related services to start.

keywords : Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]; SQL,

How to Access or Connect to DPM 2010 SQL

Current Operating System : Windows 2008 R2
Data Protection Version : DPM 2010

Here’s a simple step that I access to my DPM 2010 SQL.  I need to do a backup.

• <Right-Click> on the SQL Server Management Studio
• Select Run as administrator

• In the Server Name column, key in as
  • .\msdpm2010

or

• dpmservername\msdpm2010
  replace the dpmservername with your server that the DPM is installed on.

• Expand the Databases, then navigate to DPMDB

• <Right-Click> on the DPMDB – select Tasks – then select Back Up …

• Click OK to backup the database
• Default path is : C:\Program Files\Microsoft DPM\SQL\MSSQL10DPM\MSSQL\Backup

keywords : SQL, DPM 2010, SCDPM 2010, Data Protection 2010, SQL for DPM 2010, open SQL for DPM 2010