August, 2009

Support Fallen Troop Families

This holiday weekend is Patriot Golf Day. You can check it out here:
 http://sarasota.golfersguide.com/golf/sa…
Please check this out and go play some golf over the long weekend. Find a participating course near you and go play for a good cause.

Powershell execute SQL Server Jobs

I have been asked to write a script that will allow users to connect to a server, get a list of only the jobs that I want them to see, and execute only those jobs. No options to do anything other than execute the job. This is what I have come up with.

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) “Servername\instnace”
$jobserver = $s.JobServer
$jobs = $jobserver.Jobs | where {$_.Name -like “*jobname*”}
$totaljobs = $jobs.Count
$i = 1
echo “`n”
foreach ($job in $jobs)
{
echo “$i - $job.name”
$i ++
}
$exit = $i
Echo “$i - Exit”
echo “`n”
$exjob = Read-Host “What job would you like to execute`n”
if($exjob -ne $exit)
{
echo “Running Job”
$run = $exjob - 1
$jobs[$run].Invoke()
}
Echo “Exiting”

This will use integrated authentication to connect and enumerate jobs with the name criteria that you define. You will then get a numbered menu of the jobs and a prompt asking what job to run. Select the number of the job and it will execute on the server.

Please let me know if you have any questions or comments.

More of the TV Saga

Today is really the point of no return for me in this venture of leaving the dish behind. I recieved my shipping boxes for the LNB and two recievers that I had from the dish provider and I am taking them to the UPS Store today. Now I can not just call and say turn it all back on. So far I have been fairly pleased with the switch and I would still recommend it to anyone at this time.

Thus far I have found the using the computer with Vista Media Center and two external USB Tuners as a DVR is a great solution. Along with the ad on that I found to let me manage my recordings from the web and the DynDns setup I think it is very functional and useful. I have had some problems with the commercial removal tools that MCEBuddy uses and I have disabled that feature for now. That is OK though since I am streaming the files with PlayOn via my PS3 I can skip a minute or two in the file with now issue at all. Really my last thing was that I decided I wanted to get all of my DVD collection on to my computer so that I would be able to watch anything without having to get up to get the disk. Lazy I know, but my DVD collection is all upstairs. To accomidate this I am ripping the DVD to and ISO file using DVD Shrink. I only condone the use of this product to backup movies that you actually own. Do not steal the movies!! I Rip the DVD and remove all the audio tracks and subtititle tracks that I do not want. I then use the PS3 Media Server to open the ISO and stream it to my TV. It works great. Between having all of my DVR shows, and DVD’s available to me anytime I also have all of the internet stuff that PlayOn brings to the table. With the Plugins and what they provide out of the box I do not think I will ever pay for TV again.

Thanks to the guys at Media Mall for all the great work they have done with PlayOn and the new Wii support is great. They did a great job with the interface and well I just can not say enough good things about them. Well worth the $40 and definatly worth the free 14 day trail that you get. Check it out at http://www.themediamall.com/

Thanks again and let me know if oyou have any questions or comments.

Moving System Databases in MSSQL2005

Recently I had to move all system databases for one server to another physical drive. This is pretty simple for all but the Master and SystemRecource databases. For all others you can just do the following and then copy the mdf and ldf files to the new location.

alter database tempdb

Modify File (Name = tempdev, Filename = ‘c:\tempdb\tempdb.mdf’);

go

alter database tempdb

Modify File (Name = templog, Filename = ‘c:\tempdb\templog.ldf’);

go

Of course with tempdb you do not need to move the mdf and ldf files and it will be recreated in the new location when the server restarts.

So for Master it is a bit different. First I would double check the current location of the master files by doing the following.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(’master’)

GO

This will give you the current location of the Master Database. Now that we know that lets move it. For master you will have to open up the SQL Server Configuration Manager utility. Highlight the SQL server 2005 Services node on the left and then find your server service on the right. Right click on it and go to Properties. Go to the Advcanced Tab and look for the Startup Options parameter. In here you will see something like the following.

-dC:\tempdb\master.mdf;-eC:\tempdb\LOG\ERRORLOG;-lC:\tempdb\mastlog.ldf

You will notice the -d -e and -l, you must leave all of those switches in the line. They are telling SQL Server the location of the Data, ErrorLog, and Log files. Then modify the path to the path that you would like it to be now. Then click apply and ok to close out of the configuration Manager. Now stop the SQL Server Service and copy the MDF, LDF files and make sure that the folder structure to the ErrorLog file is available as well as check the security and make sure that the account that runs your SQL Server has full access to this new locataion. Once that is all done pull up a command prompt so we can start the SQL Server in Single User mode.

Net Start MSSQL$Instancename /f /T3608 (for named instance)

Net Start MSSQLSERVER /f /T3608 (for default instance)

Once that starts up then do the following to move the systemresource database as well.

ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=data, FILENAME= ‘D:\SqlData\mssqlsystemresource.mdf’);
GO
ALTER DATABASE mssqlsystemresource
MODIFY FILE (NAME=log, FILENAME= ‘ D:\SqlData\mssqlsystemresource.ldf’);

Go

now go back to the command prompt and stop the sql server by doing:

Net stop MSSQL$InstanceName (for named instance)

Net Stop MSSQLSERVER (for default instance)

Now mive the systemresource mdf and ldf files to the new location and lets start it up in single user mode again like we did above.

Now do the following:

ALTER DATABASE mssqlsystemresource SET READ_ONLY;

and now stop the service again. Now we are ready to start it up normally and if all went and goes well you should be all set. Not to big of a deal but be very careful when doing this. Make sure you have a backup of Master and make sure that you backup the original startup options just in case you need to go back. Safety first here. You do not want what should be a simple task to be a horrific event.

Thanks and let me know if you have any questions or comments.

Questions about health check script

I recieved a comment that contained a question about my previous post about writing a script to output a health check of SQL Server.

 http://itknowledgeexchange.techtarget.co…

The following is the question:

should we make anychanges for this do and what are the fields i need to provide at this columns: #Read thru the contents of the SQL_Servers.txt file
#$servers = Import-Csv “\pni-vmdbasqld$monitoringinputssqltab.txt”
$servers = Import-Csv “\ent-pocpacapcx01d$monitoringsqltab.txt”

So You should make changes to the $servers variable and point it to the input file that you are going to be using. My csv file has the following columns in it and you can adjust to yours to make it fit your needs.

Monitor,Server,Instance,TorP,ErrorLog,Ping,OS2000

Those are the things that I have found that I use in multiple scripts to deal with SQL Server. The Monitor is jsut a Y or N value to tell my monitoring scripts if I want to monitor that server at this time. Then the physical host name is the Server value, instance is NULL if default and the name of the instance if named, TorP is T if the server is Test and P if Prod, ErrorLog is the location of the errorlog on the filesystem, Ping is also a Y or N value used by my Ping Servers Script to tell it if I want it to be pinged, and OS200 tells my scripts if the server is running Server 2000 or not. That is important since OS2000 can not have Powershell installed.

Hope that helps and if you have any other questions or comments please let me know.

Dynamic DNS

nbsp;Dyndns.com is a site that I talked about in my last post. It is a free service, or premium depending on what you need, that allows someone with a dynamic ip to access there computer or site with a name like yourname.dyndns.org. It is a great way to be able to get to whatever it is you are doing. You could have a website hosted on your machine, or do what I am doing and use it to get to my DVR Schedule. It is a great site with a great service. Check them out.

More on the Free TV Move

Things are still going pretty good and I even found a nice little add on that should allow me to manage all my recordings, let me watch my video, and listen to my music from the web. It is called webguide. This is something that ties into Vista Media Center and allows you to do all this. (so they say) I have gotten it working on my LAN to connect and allow me to change my recordings and see what I have recorded but have not been able to do any streaming at this time. My plan is to get a url with dyndns.com and be able to control my recordings from anywhere. Just another feature that so many DVR’s flaunt and now I can do it for free. Check it out and let me know what you think about it. More to come.

TV Conversion Update

So I have been working on this for a few days and everything is going pretty well. I have two WINTV-HVR 950Q External USB 2.0 HD Tuners connected to my PC. These things are great. Highty recommend them if you are looking for a good easy Tuner for under $100 dollars. I got mine on sale for $70.00. The software provided is OK at best but it is functional. I am using the Vista Media Center with mine for things like recording.  The major downside to this, for me anyway, is that when the Media Center records it is in an .dvr-ms file format. PS3 does not like that format. Because of this I have another step after recording before I can stream the video to the PS3. I have to do a file conversion. I have found a tool that will scan input folders and as soon as it sees the dvr-ms file in the folder it goes to work. This is a great program. It works with the DVRMSToolbox as well. This means that as it does the file conversion it will also automatically remove comercials for you. it also has a wide variety of output file types that you can convert to. The program is MCEBuddy. Pretty lightweight except when doing the conversion, It will eat up some cycles. I think that I will have mine do all the conversions over night when I am asleep. I will always be a day behind on my DVR shows but that is OK by me. Alo with the two USB Video Tuners and the Vista Media Center I am able to record two shows at a time. Love that. The only down side is that the dvr-ms files are HUGE. You will need a big disk and I highly recommend having MCEBuddy remove the originals when done converting to conserve disk space.

Also another good media player for the PS3 is the PS3MediaServer. Very lightweight and will play some filetypes that PlayOn will not at this time. Like a DVD ISO. I am still not really happy with the ISO playing as it just selects the first audio track and the first sub track. No choices here. I am still playing with ISO files and hope to come up with or find another solution as I would love to have all my DVD library accesable at the touch of a button.

I will continue to play with this and let you know what I find and what My recommendations are to you. I think that this is going to be a great Entertainment Solution for me and it will save me money in no time at all since I am no longer shelling out $100.00 a month for service.

Powershell Profile

The Powershell Profile is a script that druns anytime you launch Powershell. This is handy so you can ste up variables, add snapins, or just do what ever you like or think is cool when launching Powershell. I was just editing mine, which you can do by:

notepad $profile

This will pull up your profile so you can edit the script. If you would like to know where the script is located then just do a $profile to get the full path. Anyway after I edited my profile I wanted to reload the profile so that all the new things I just added would be available in my current powershell session. Like most things in Powershell this is simple.

. $profile

This will re-run the profile script and now all your new goodies are available

Find how many files of any extension are in a folder

Recently I needed to find out if I had any files of a certain extension in a particular folder and if so how many. With Powershell this is no problem at all.

Say I was looking for a count of .ps1 files in the directory.

@(Dir Directorypath\*.ps1).Count

The @() makes the result go into an array object. This way no matter what you will get an accurate count without having to do a for loop on that directory.