Shulou(Shulou.com)06/01 Report--

Using Powershell to realize automatic Operation and maintenance of Database

At present, there are only a few good original books and many translated books for SQLServer in the market, but they are only limited to the development, management and business intelligence of SQLServer database itself.

For PowerShell books, it can be said that most of them are learning notes in MSDN, mainly for the management of the Windows operating system.

Microsoft is pushing the cloud platform in terms of large layout and technology trends. The foundation of platform is automation and high availability. So refined to the most basic technical focus, Microsoft is vigorously developing PowerShell support for all its server-side products. For SQL Server, make good use of PowerShell to automate management tasks in order to achieve further platform, which is the cornerstone of the cloud platform.

Foreign vigorously promote DEVOPS, development-oriented operation and maintenance, not only let the database administrator or system administrator limited to manual operation of complicated daily work, which is extremely risky. It is a general trend to learn PowerShell to improve script development ability and simplify daily work.

I translated a copy of Microsoft MVP's "PowerShell V3-SQL Server 2012 authoritative Guide to Database Automation Operation and maintenance", which covers a very comprehensive and practical book. For SQL Server DBA, it is a sharp tool to improve database management skills.

Below I will explain how to use PowerShell to automate the operation and maintenance of SQL Server and MongoDB through two examples.

1. Restore SQL Server database to a point in time

In this scenario, we will restore to a point in time using different backup files.

Prepare for

In this scenario, we will use the AdventureWorks2008R2 database. You can also choose the database you prefer in your development environment.

The AdventureWorks2008R2 database has a filegroup that contains a single database file. We will use three different backup files from the following three different backup types to restore the database to another SQL Server instance on a point-in-time basis:

Full backup

Differential backup

Transaction log backup

We can use PowerShell, as described in the previous scenario, to create these three types of backups on the AdventureWorks2008R2 database. If you are familiar with T-SQL, you can also use the T-SQL backup command.

To help us verify that the result of a point-in-time recovery is what we expect, create a timestamp-identified table before making any type of backup. Accordingly, create a table and insert a record identified by a timestamp into the table before the backup.

Place these backups in the C:\ Backup\ directory.

You can use the script below to create your file, 6464-Ch06-10-Restore a database to a point in time-Prep.ps1, which is included in the downloadable file for this book. When the script is completed, you should have a timestamped Student table in the AdventureWorks2008R2 database and create it at one-minute intervals, similar to the following screenshot:

(translator's note: you can download the code for the book from https://www.packtpub.com/books/content/support/10233. )

For our scenario, we will restore the AdventureWorks2008R2 database to another instance, KERRIGAN\ SQL01, to 2015-07-27 02:51:59. This means that after the point-in-time recovery is complete, we will only have four timestamped Student tables on KERRIGAN\ SQL01 on the restored database:

StudentFull_201507270247

StudentDiff_201507270249

StudentTxn_201507270250

StudentTxn_201507270251

How to do...

To recover to a point in time using full, differential, and some transaction log files, follow these steps:

1. Open the PowerShell console via "Start | Accessories | Windows PowerShell | Windows PowerShell ISE".

two。 Import the SQLPS module:

# import SQL Server module Import-Module SQLPS-DisableNameChecking

3. Add the following script and run it:

$instanceName = "KERRIGAN\ SQL01" $server = New-Object-TypeName Microsoft.SqlServer.Management.Smo.Server-ArgumentList $instanceName # backupfilefolder $backupfilefolder = "C:\ Backup\" # look for the last full backupfile # you can be more specific and specify filename $fullBackupFile = Get-ChildItem $backupfilefolder-Filter "* Full*" | Sort-Property LastWriteTime-Descending | Select-Last 1 # read the filelist info within the backupfile # so that we know which other files we need to restore $smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore $smoRestore.Devices.AddDevice ($fullBackupFile.FullName [Microsoft.SqlServer.Management.Smo.DeviceType]:: File) $filelist = $smoRestore.ReadFileList ($server) # read headers of the full backup file, # because we are restoring to a default instance We will # need to specify we want to move the files # to the default data directory of our KERRIGAN\ SQL01 instance $relocateFileList = @ () $relocatePath = "C:\ Program Files\ Microsoft SQL Server\ MSSQL11.SQL01\ MSSQL\ DATA" # we are putting this in an array in case we have # multiple data and logfiles associated with the database foreach ($file in $fileList) {# restore to different instance # replace default directory path for both $relocateFile = Join-Path $relocatePath (Split-Path $file.PhysicalName-Leaf) $relocateFileList + = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ($file.LogicalName $relocateFile)} # let's timestamp our restored databasename # this is strictly for testing our recipe $timestamp = Get-Date-Format yyyyMMddHHmmss $restoredDBName = "AWRestored_$ ($timestamp)" # = = # restore the full backup to the new instance name # = = # note we have a NoRecovery option Because we have # additional files to restore Restore-SqlDatabase `- ReplaceDatabase`-ServerInstance $instanceName `- Database $restoredDBName`-BackupFile $fullBackupFile.FullName `- RelocateFile $relocateFileList`-NoRecovery # = = # restore last differential # note the database is still in Restoring State # = = # using PowerShell V2 Where syntax $diffBackupFile = Get-ChildItem $backupfilefolder-Filter "* Diff*" | Where {$_ .LastWriteTime-ge $fullBackupFile.LastWriteTime} | Sort-Property LastWriteTime- Descending | Select-Last 1 Restore-SqlDatabase `- ReplaceDatabase`-ServerInstance $instanceName `- Database $restoreddbname`-BackupFile $diffBackupFile.FullName `- NoRecovery # = = # restore all transaction log backups from last # differential up to 2015-07-2702: 51:59 # = = # identify the last txn log backup file we need to restore # we need this so we can specify point in time $lastTxnFileName = "AdventureWorks2008R2_Txn_201507270251" $lastTxnBackupFile = Get- ChildItem $backupfilefolder-Filter "* $lastTxnFileName*" # restore all transaction log backups after the # last differential Except the last transaction # backup that requires the point-in-time restore foreach ($txnBackup in Get-ChildItem $backupfilefolder-Filter "* Txn*" | Where {$_ .LastWriteTime-ge $diffBackupFile.LastWriteTime-and $_. LastWriteTime-lt $lastTxnBackupFile.LastWriteTime} | Sort-Property LastWriteTime) {Restore-SqlDatabase `- ReplaceDatabase`-ServerInstance $instanceName `- Database $restoreddbname`-BackupFile $txnBackup.FullName `- NoRecovery} # restore last txn backupfile To point in time # restore only up to 2015-07-27 02:51:59 # this time we are going to restore using with recovery Restore-SqlDatabase `- ReplaceDatabase`-ServerInstance $instanceName `- Database $restoreddbname`-BackupFile $lastTxnBackupFile.FullName `- ToPointInTime "2015-07-27 02:51:59"

How to realize...

In this scenario, we use Restore-SqlDatabase cmdlet, and the cmdlet as opposed to Backup-SqlDatabase is introduced in SQL Server 2012.

Let's start with a high-level overview of how to implement point-in-time recovery, and then we can refine and explain the fragments included in this scenario:

1. Collect your backup files.

Identify the last transaction log backup file that contains the point in time you want to recover.

two。 Restore the last good full backup using NORECOVERY.

3. Restore the last differential backup after the full backup using NORECOVERY.

4. Restore transaction log backups after differential backups:

Use NORECOVERY to restore until the log file backup contains the point in time you want to restore. You need to restore the last log file backup to a point in time, that is, you need to specify the time you need to restore. Finally, use WITH RECOVERY to restore the database, making it accessible and ready for use.

Alternatively, you can use NORECOVERY to restore all transaction log backup files before the log backup contains the point in time you want to restore. Next, use WITH RECOVERY to restore the last log backup to a point in time, that is, you need to specify when to restore.

Step 1-collect your backup files

You need to collect your backup files. They don't have to be in the same directory or driver, but ideally, it will simplify your recovery script, and you will have a unified directory or driver to refer to. You also need permission to read these files.

In our scheme, we simplify this step. We collected the full, differential and transaction log backup files and stored them in the C:\ Backup\ directory for easy access. If your backup files are in a different location, you only need to adjust the reference directory of your script appropriately.

Once you have these backup files, assuming you follow the file naming convention, you can filter all full backups in your directory. In our example, we use the naming convention databasename_type_timestamp.bak. For this scenario, we extract the full backup file by specifying a keyword or matching pattern in the file name. We use Get-ChildItem to filter the latest full backup files:

# look for the last full backupfile#you can be more specific and specify filename$fullBackupFile = Get-ChildItem $backupfilefolder-Filter "* Full*" | Sort-Property LastWriteTime-Descending | Select-Last 1

Once you have a handle to the full backup, you can read the list of files stored in the backup file. You can use the ReadFileList method of the SMO Restore object. Reading the file list can help you automate by extracting the file names of the data and log files you need to recover.

# read the filelist info within the backup file#so that we know which other files we need to restore$smoRestore = New-Object Microsoft.SqlServer.Management.Smo.Restore$smoRestore.Devices.AddDevice ($fullBackupFile.FullName, [Microsoft.SqlServer.Management.Smo.DeviceType]:: File) $filelist = $smoRestore.ReadFileList ($server)

When reading a list of files, one of the attributes you can extract is the type of file stored:

The different types are:

L stands for log file

D stands for data file

F stands for full-text catalogue

Step 2-restore the last good full backup using NORECOVERY

The first step in the restore operation is to restore the last known good full backup. This provides you with a baseline based on which you can recover additional files. The NORECOVERY option is important because it maintains (or does not roll back) uncommitted transactions and allows additional files to be restored. We will use the NORECOVERY option during our real recovery process.

Because a full backup is always the first file to be restored, all preparations need to be ready, and moving files begins.

For our scenario, we want to restore the database from the source default instance KERRIGAN to another instance KERRIGAN\ SQL01. Therefore, we need to move our files, from the path where our backup files are stored to the new path we want to use. In this example, we just want to move from the default data directory of the default instance to the data directory of the named instance KERRIGAN\ SQL01. We get the full path from the original data and log files of the file list and replace the full path with the new location we want to restore to. The highlighted code in the following snippet shows how to modify the location:

$relocateFileList = @ ()

$relocatePath = "C:\ Program Files\ Microsoft SQL Server\ MSSQL11.SQL01\

MSSQL\ DATA "

# we are putting this in an array in case we have

# multiple data and logfiles associated with the database

Foreach ($file in $fileList)

{

# restore to different instance

# replace default directory path for both

$relocateFile = Join-Path $relocatePath (Split-Path $file.

PhysicalName-Leaf)

$relocateFileList + = New-Object Microsoft.SqlServer.Management.

Smo.RelocateFile ($file.LogicalName, $relocateFile)

}

Notice that our array contains Microsoft.SqlServer.Management.Smo.RelocateFile objects that will contain the logical and (relocated) physical file names of our database files.

$relocateFileList + = New-Object Microsoft.SqlServer.Management.Smo.RelocateFile ($file.LogicalName, $relocateFile)

To restore our database, we only use Backup-SqlDatabase cmdlet. Here are a pair of important options, such as RelocateFile and NoRecovery.

# restore the full backup to the new instance name#note we have a NoRecovery option, because we have#additional files to restoreRestore-SqlDatabase `- ReplaceDatabase`-ServerInstance $instanceName `- Database $restoredDBName`-BackupFile $fullBackupFile.FullName `- RelocateFile $relocateFileList`-NoRecovery

Step 3-after the full backup is restored, use NORECOVERY to restore the last good differential backup

Once the full backup is restored, you can add the last good differential backup to follow the full backup. It is not an integrated process, because at this point we have restored the underlying database and relocated our files. We need to use NORECOVERY to restore differential backups to prevent uncommitted transactions from being rolled back:

# using PowerShell V2 Where syntax

$diffBackupFile =

Get-ChildItem $backupfilefolder-Filter "* Diff*" |

Where {$_ .LastWriteTime-ge $fullBackupFile.LastWriteTime} |

Sort-Property LastWriteTime-Descending |

Select-Last 1

Restore-SqlDatabase `

-ReplaceDatabase `

-ServerInstance $instanceName `

-Database $restoreddbname `

-BackupFile $diffBackupFile.FullName `

-NoRecovery

Note that in your environment, you may or may not have a differential backup file. If not, don't worry, it won't affect your recoverability, as long as all transaction log files are available for recovery.

Step 4-restore the transaction log after restoring a differential backup

After we recovered the differential backup files, we began to restore our transaction log backup files. These transaction log backup files should follow your differential backups. You may or may not need a complete collection of log files that follow differential backups. If you need to restore to the point of database failure, you will need to restore all transaction log backups, including tail log backups. If not, you only need to know the backup files at the event point you want to recover.

For our scheme, we identified the last log backup file we wanted to recover. This is important because we need to know how to use the PointInTime parameter when we use this particular transaction log to back up files.

# identify the last txn log backupfile we need to restore#we need this so we can specify point in time$lastTxnFileName = "AdventureWorks2008R2_Txn_201507270252" $lastTxnBackupFile = Get-ChildItem $backupfilefolder-Filter "* $lastTxnFileName*"

For all other transaction log backup files, we traverse all backup directories and restore all .txn files after the last differential backup and before the last transaction log backup files we want to restore. We also need to sort these files through the WriteTime parameter so that we can restore them to the database in turn. Note that we need to recover all of these files using NORECOVERY.

Foreach ($txnBackup in Get-ChildItem $backupfilefolder-Filter "* Txn*"

| |

Where {$_ .LastWriteTime-ge $diffBackupFile.LastWriteTime-and

$_ .LastWriteTime-lt $lastTxnBackupFile.LastWriteTime} |

Sort-Property LastWriteTime)

{

Restore-SqlDatabase `

-ReplaceDatabase `

-ServerInstance $instanceName `

-Database $restoreddbname `

-BackupFile $txnBackup.FullName `

-NoRecovery

}

Once all these files are restored, then we are ready to restore the final transaction log files. Once this file is restored, the database needs to be accessible and all uncommitted transactions need to be rolled back.

There are two ways to do this. The first method, which we use in this scenario, is to use the ToPointInTime parameter to restore the last file, and not to use the NoRecovery parameter.

Restore-SqlDatabase `- ReplaceDatabase`-ServerInstance $instanceName `- Database $restoreddbname`-BackupFile $lastTxnBackupFile.FullName `- ToPointInTime "2015-07-27 02:51:59"

Another way is to restore the last transaction log file, also using NoRecovery, but add another command at the end to restore the database using WITH RECOVERY. In fact, it has always been safer to use NORECOVERY to recover all the transaction log backup files you need. It is safer because when we suddenly use WITH RECOVERY to recover a file, the only way to correct it is to redo the entire recovery process. This may not matter much for small databases, but it can be very time-consuming for large databases.

Once we have confirmed that all the required files have been restored, we can use WITH RECOVERY to restore the database. In our scenario, one way is to use the T-SQL statement and pass it to Invoke-Sqlcmd:

# get the database out of Restoring state#make the database accessible$sql = "RESTORE DATABASE $restoreddbname WITH RECOVERY" Invoke-Sqlcmd-ServerInstance $instanceName-Query $sql

RESTORE DATABASE naming takes our database from a state under recovery to an accessible and ready-to-use state. RESTORE naming rolls back all outstanding transactions and makes the database ready for use.

Second, use PowerShell to call MTools to analyze MongoDB performance and send email

In the daily operation and maintenance of MongoDB, you often need to check the trend chart of the number of connections, slow queries, Overflow statements, connection sources. The DBA of any database should inspect the database regularly in order to clearly understand the operation of the database, health status, hidden dangers and so on. The MTools tool arises at the historic moment, and it is a great help to DBA.

Introduction to Mtools

Mtools is written by the official engineer of MongoDB Inc. It was designed to facilitate your work, but with the increase of MongoDB users, more and more friends also began to use Mtools, and more and more feel the convenience brought by Mtools.

The Github address is as follows:

Mtools github address

Mtools has the following main components:

Mlogfilter

Mloginfo

Mplotqueries

Mlogvis

Mlaunch

Mgenerate

First, let's briefly introduce mlogfilter,mloginfo and mplotqueries.

Mlogfileter can be simply understood as a log filter. The parameters are as follows:

Mlogfilter [- h] [--version] logfile [logfile...]

[--verbose] [--shorten [LENGTH]]

[--human] [--exclude] [--json]

[--timestamp-format {ctime-pre2.4, ctime, iso8601-utc, iso8601-local}]

[--markers MARKERS [MARKERS...]] [--timezone N [N...]]

[--namespace NS] [--operation OP] [--thread THREAD]

[- slow [SLOW]] [--fast [FAST]] [--scan]

[--word WORD [WORD...]]

[--from FROM [FROM...]] [--to TO [TO...]]

Example:

Query the slow log of a table in the log through mlogfilter (more than 100ms)

Mlogfilter-namespace xxx.xx-slow 100 mongod.log-20160611

Mloginfo can filter and summarize the situation of slow query and make statistics for the most common situations in the log. The parameters are as follows:

Mloginfo [- h] [--version] logfile

[--verbose]

[--queries] [--restarts] [--distinct] [--connections] [--rsstate]

Example:

Use mloginfo to count the source of connections in the log

Mloginfo mongod.log-20160611-connections

Mplotqueries is relatively complex, and the function is to draw drawings according to requirements, so as to find out the problems or hidden dangers more intuitively. The parameters are as follows:

Mplotqueries [- h] [--version] logfile [logfile...]

[--group GROUP]

[--logscale]

[--type {nscanned/n,rsstate,connchurn,durline,histogram,range,scatter,event}]

[--overlay [{add,list,reset}]]

[additional plot type parameters]

Example:

Analyze the connection through mplotqueries, time block unit 1800 (30min)

Mplotqueries mongod.log-20160611-- type connchurn-- bucketsize 1800-- output-file 01-9.png

Solution

The author will install the MTools tool on Windows to analyze the mongod.log log and then send an email via Powershell.

1. Mount the Windows backup directory to the MongoDB local directory and copy the latest log after LogRotate switching to the backup directory.

Reference blog post: "Mount the shared directory of Windows system under Linux"

two。 Install Mtools on the Windows server.

Refer to the blog post: "install mtools on 64-bit Windows Server 2008 R2"

3. Write PowerShell scripts, analyze log files through Mtools, and send messages.

Github source code address: https://github.com/UltraSQL/MongoDBDailyReport.git

How to use it:

A) put the DBA module in the appropriate Modules\ DBA directory.

B) load the module in the configuration file: Import-Module DBA-Force.

C) create a task plan and execute the MTools.ps1 script regularly.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.