Friday, April 15, 2016

Replace or Restore MS SQLSERVER database from backup

Sometimes you want to revert a database from an earlier backup.

Here is a quick way without using the gui.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH REPLACE
GO

If you get an error stating the database is busy. For example 'Exclusive access could not be obtained because the database is in use', try the following.

EXEC sp_who2

Look for the database in the list. It's possible that a connection was not terminated. If you find any connections to the database, run

KILL <SPID>

where <SPID> is the SPID for the sessions that are connected to the database.

Try the command again after all connections to the database are removed.

see the example below

Processed 528288 pages for database 'AdventureWorks', file 'AdventureWorks' on file 1.
Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks_log' on file 1.
RESTORE DATABASE successfully processed 528290 pages in 9.067 seconds (455.195 MB/sec).

Thursday, August 13, 2015

SCCM Software Update Summarization is bad for you son

Our sql server was getting hit hard by a query. Initially I wrote a script to email me when queries were taxing the server. I saw the query but thought it was a one off. Looking into ssd wear levels this same query was writing A LOT so I decided to use another tool to find all of the heavy hitters.

Right click your sqlserver then select reports -> standard reports -> Performance - Top Queries by Total IO

Look at the the Ave. Logical IO bar column and the once that spike are the queries with heavy writes.

click the + next to each query and it will tell you whats it's doing.

merge Update_ComplianceSummary dst           using (select * from v_Update_ComplianceSummary_Live where CI_ID in (select CI_ID from @ci) and NumUnknown>=0) src               on dst.CI_ID=src.CI_ID              when matched                    then update set LastSummaryTime=src.LastSummaryTime, NumTotal=src.NumTotal, NumUnknown=src.NumUnknown,                                    NumNotApplicable=src.NumNotApplicable, NumMissing=src.NumMissing, NumPresent=src.NumPresent, NumInstalled=src.NumInstalled, NumFailed=src.NumFailed               when not matched                   then insert (CI_ID, LastSummaryTime, NumTotal, NumUnknown, NumNotApplicable, NumMissing, NumPresent, NumInstalled, NumFailed)                        values(src.CI_ID, src.LastSummaryTime, src.NumTotal, src.NumUnknown, src.NumNotApplicable, src.NumMissing, src.NumPresent, src.NumInstalled, src.NumFailed)               ;

There will be a table like the following:




Considering that the only other query of note performed 50k writes once a night vs 1,000K hourly I know this was the problem.

Googling led me to this these threads

https://social.technet.microsoft.com/Forums/en-US/5a8adf02-7e9f-40e2-b849-f7c6135d7312/sum-update-status-summarizer-consumes-100-cpu-every-hour-for-15-minutes?forum=configmanagergeneral

 https://social.technet.microsoft.com/Forums/en-US/84c58af9-b340-4e96-876b-e7b7d3ddb144/software-update-summarization-process-uses-90-of-cpu?forum=configmanagergeneral

Which state that there is a bug in the Software Update Summarization report. And you essentially need to change it's schedule.

Using the SCCM console click on Administration -> Sites -> right click -> Status Summarizer
select Site System Status Summarizer and change that to weekly

Under Software Library -> All Software Updates -> Schedule summarization
change to Recur every 7 days

Here is the cpu after the changes were made:



Thursday, July 9, 2015

powershell to list old dns records and then delete them

#list_old_records
$records = Get-DnsServerResourceRecord -ZoneName example.com
$records = $records | ? Timestamp -ne $null
$records = $records | ? Timestamp -lt 3/01/2015
Write-Output $records

copy the output to a file then regex below in notepad++ to just get the hosts

\s*\w\s*\d*\/\d*\/+\d{4}\s.*\r\n

The use the file as an import for the following powershell

#delete_old_records
$DNSServer = "dns1.example.com"
$DNSZone = "example.com"
$InputFile = "hosts.txt"
import-module DnsServer
$recordnames = Get-Content $InputFile

# Now we loop through the file to delete the records
ForEach ($recordname in $recordnames) {
Remove-DnsServerResourceRecord -Name $recordname -RRType A -ZoneName $DNSZone -ComputerName $DNSServer -Force
}

                               

Tuesday, June 23, 2015

setting proper web root permissions

# chown -R www-data:www-data /var/www/vhost/example.com/
NOTE!: The www-data user is used by nginx and php5-fpm. If you are running php as a different user then change ownership as per that.
# Correct Directory Permissions
# find /var/www/vhost/example.com/ -type d -exec chmod 0755 {} \;
# Correct Files Permissions
#$ find /var/www/vhost/example.com/ -type f -exec chmod 0644 {} \;

Thursday, June 18, 2015

using rancid with password from secretserver

Rancid, http://www.shrubbery.net/rancid/ , is awesome for switch config automation, but keeping the passwords in plain text isn't a good practice. I could use local encryption, but I just wrote a python script to get the passwords from the our password vault (secret server), http://thycotic.com/, then run rancid and then clear the config.

crontab for everything
0 22 * * * root /usr/bin/python /usr/local/rancid/bin/getpass.py > /usr/local/rancid/.cloginrc
1 22 * * * rancid  /usr/local/rancid/bin/rancid-run
2 22 * * * root /usr/bin/echo > /usr/local/rancid/.cloginrc


#------------------------------------------------------------------------------
# Imports
#------------------------------------------------------------------------------

import sys
import suds

client = suds.client.Client("http://example.com/SecretServer/webservices/SSWebservice.asmx?wsdl")
#Org code is not necessary for installed edition and can be represented by ""
token = client.service.Authenticate("username", "password", "", "domain"                                                                                  )
#the token will verify that you can login
#print token

#This is how to find the client ids
#searchSecret=client.service.SearchSecrets(token.Token, "core1")
#searchSecret2=client.service.SearchSecrets(token.Token, "core2")
#searchSecret3=client.service.SearchSecrets(token.Token, "switch1")

#print searchSecret
#print searchSecret2
#print searchSecret3

secret = client.service.GetSecret(token.Token, "123")
secret2 = client.service.GetSecret(token.Token, "124")
secret3 = client.service.GetSecret(token.Token, "131")

#this is will print the password
#print secret

pass1 = secret.Secret.Items.SecretItem[2].Value
pass2 = secret2.Secret.Items.SecretItem[2].Value
pass3 = secret3.Secret.Items.SecretItem[2].Value

#fix stuff that should be escaped
passa = pass1.replace("#", "#\\")
passb = pass2.replace("#", "#\\")
passc = pass3.replace("#", "#\\")

print "add user 192.168.0.1             "+"manager"
print "add password 192.168.0.1         " + '"' + passa + '"'
print "add method 192.168.0.1           "+"ssh"
print "add autoenable 192.168.0.1       "+"1"
print "add user 192.168.0.2             "+"manager"
print "add password 192.168.0.2         " + '"' + passb + '"'
print "add method 192.168.0.2           "+"ssh"
print "add autoenable 192.168.0.2       "+"1"
print "add user 192.168.0.*             "+"manager"
print "add password 192.168.0.*         "+ '"' + passc + '"'
print "add method 192.168.0.*           "+"ssh"
print "add autoenable 192.168.0.*       "+"1"
print "add noenable route-server*      "+"1"
print "add cyphertype *                "+"{3des}"

memory is ram!

I had an issue where a webserver should have been running smoothly using normal disk cache. After pulling my hair out for a while I finally gave up and just moved the cache folder to tmpfs. That cleared everything up.

edit your fstab and add the following:

tmpfs                       65536      284     65252   1% /var/www/vhosts/example.com/cache/


#ls /var/www/vhosts/example.com/cache/
285ea04ebb783e7c264410b1923e156b-cache-1b382f33e05a11af6e176df12538375d.php
285ea04ebb783e7c264410b1923e156b-cache-21adf12cddf084aff54d581b7a245bee.php
285ea04ebb783e7c264410b1923e156b-cache-3165fb29750dc77e8b1c72c5c6ab701d.php
285ea04ebb783e7c264410b1923e156b-cache-6c80b5246d1d6a5a733ecf21ec8f5c45.php
285ea04ebb783e7c264410b1923e156b-cache-de57dad4255b001ebe0a55d7d2bcdefd.php

#df -h
tmpfs                     64M  284K   64M   1% /var/www/vhosts/example.com/cache/

Tuesday, April 14, 2015

What is time really?

Setting your virtual machine's time by syncing with the host is usually a no no. Here is a powercli script to look up what VMs are syncing time with the host.

get-view -viewtype virtualmachine -Filter @{'Config.Tools.SyncTimeWithHost'='True'} | select name


Name ---- fileserver1 domaincontroller2 proxy remotedesktop printserver1