Powershell + Reporting Services?

I have a small need to produce some data in a much easier way than I have heretofore. The process that I currently do it this. I run a query that produces the table counts of various tables in a specific database. These results I paste into Excel. I then repeat that process on another database (the destination of replication) and copy those results also into the same Excel document. I have within this Excel doc a column of fields that are functions, indicating if the totals from the left are the same as on the right. This lets me easily check if the replication has caught up, and if not, which tables are missing data. Its crude and rudimentary, but a descent check on replication.

Now I have to do this for multiple databases. Repeat those steps multiple times. I dont like repeating things, unless they are fun things. This is not a fun thing. I kinda want a portal of sorts that i can click, and get the answer I want. This is not that solution.

I think about powershell. Hum... I could have a PS script that can pull the results of the table counts, and deposit it into a table. If I do this for each side of replication, for each database, I will have a simple table of results, and a simple SQL query will then let me show off only those records that are not equal, meaning replication is off on those tables. Hum... this is promising...

I easily create the script as the following.

1. $DESTINATION="DBSERVER"
2. 
   
3. $query = "SELECT
4. @@ServerName as [ServerName],
5. DB_NAME() as [DatabaseName],
6. o.name AS [Name],
7. i.rowcnt AS [Count]
8. FROM sysobjects o
9. JOIN sysindexes i ON i.id = o.id AND indid IN(0,1)
10. WHERE xtype = 'u'
11. and o.name in ( 'Table1', 'Table2')
12. order by i.rowcnt desc"
13. 
    
14. $Data = invoke-sqlcmd -query "Delete from ReplicationReviewResults where ServerName = 'DBSERVER' and DatabaseName = 'DBName'" -ServerInstance "DBSERVER" -database "DATABASEMONITORING"
15. 
    
16. $Data = invoke-sqlcmd -query $query -ServerInstance "DBSERVER" -database DBNAME
17. 
    
18. Write-DataTable -ServerInstance $DESTINATION -database "DATABASEMONITORING" -TableName ReplicationReviewResults -Data $Data
19. 
    
20. $Data = invoke-sqlcmd -query "Delete from ReplicationReviewResults where ServerName = 'DBSERVER2' and DatabaseName = 'DBNAME'" -ServerInstance "DBSERVER" -database "DATABASEMONITORING"
21. 
    
22. $Data = invoke-sqlcmd -query $query -ServerInstance "DBSERVER2" -database DBNAME
23. 
    
24. Write-DataTable -ServerInstance $DESTINATION -database "DATABASEMONITORING" -TableName ReplicationReviewResults -Data $Data

This I repeat as many times as necessary. In my case, 3 times, with 3 different DBNames.

All this I shove into a PS script. I test it out and it works fine. It removes the data from the last execution, for a particular DBNAME and DBSERVER and then collects it again and deposits it into a central database on some db server. Simple. As long as connectivity exists, PS can execute, users exist, and all that type of config, I am able to execute this, and populate data. Woohoo.. Closer.

So now as I think about the portal idea... I think of Reporting Services. A report could easily display this data. Summed data or detail data. Something easily retrieved from the table ReplicationReviewResults. Easy. However, that data won't be accurate. It will be latent, since the last time that I ran the PS script mentioned. So... hum... What if I put that PS Script into a SQL Server Job? Could that work? I do have other PS scripts executing with jobs... that should work.

After testing the job and seeing success, I know have a way for my data to be populated. All I need to do is incorporate this into a Report with a step to call the job execution, then maybe wait a bit for it to finish, then display the data. Yeah!! However the user that can run reports doesnt have a lot of rights, and running jobs isn't one of them. He is a simple datareader...

So, after playing with granting rights to sp_start_job and even select on sysjobs, I'm still stuck. Duh...

I realize that this user needs to have Agent rights and recall that there are roles in the MSDB with something to do with Jobs. I add the role [SQLAgentOperatorRole] to this user, and they can in fact now execute a job. Woohoo. So I am now armed with a PS Script that is embedded in a job that can be executed by the user that reports run under. I create a proc, embedding the job start code along with the query to produce results, and then I create a report that calls this proc. Viola! I now have a portal that I can refresh, it retrieves new data, and displays that data to me.

Replication status updates as simple as hitting F5. No more repetitive tasks taking me minutes of my precious day...

Companies you love to love

We all know that there are companies we simply put up with. Others we hate. Some we love, but learn to put up with and ultimately degrade into a hatred. It happens. Time marches on and customer service goes down, priorities shift, things simply change and it goes from good to bad to worse, on occasion.

Back in college, I had a trusted adult in my church that ran a mechanics shop. I could call him and tell him my car was making a noise, describe the noise, and he would diagnose the issue and suggest solutions, over the phone, with no visit to his shop. As time marched on, we continued to take our vehicles to him for help and repairs. As his company grew, so did the dissatisfied service provided, until the day that we had to leave and find another mechanic. Sad. But it happens.

On the flip side, how often do you fall in love with a company, get treated well initially, but as time marches on, they treat you better and better? Think to yourself, list out the times that this has happened to you. I'm betting that it is not often that this happens. I can think of few examples myself. However, as the title of this post suggests, I have one such example.

Lets talk about "this Company"

Years ago, as a young Database Professional, I heard of a company that had a simple tool that compared databases. This wasn't rocket science, but it did prove handy from time to time. I used the tool, more and more. As the company made progress, they made the tool better, and soon branched out into other tools. I do recall thinking that this was a mistake, that doing too many apps or tools would cause them to loose focus, and the tools I had grown to love might suffer, and the company, like so many others, would go down in my eyes. I have been involved in a few different levels as an outsider with this company. I even made a little money for doing usability testing on various products. I had used the product, and they wanted to know some information from me, and I freely gave that information. The payment was a bonus, I thought. I woulda given the info to help out. But was glad to get something back from them. In fact, the motorcycle gear bag I won now, and have used for many years, was purchased using part of that money. It made it easier for me to put in the rest of the money on the bag myself. I think about them every time that I ride and enjoy using that gear bag. Ironically, its red. Not because of anything other than it was the best looking bag at the store at the time. But I find it funny that it's red.

So the days, weeks, months and years march on. I continue to try out products, continue to use the old standard apps and tools, continue to simply use what they have produced and love it. So many times one of their tools has come to my rescue and helped me out. 9 months ago I finally took the time to dig into the API and write an app (a feat which i had almost forgotten from my developer days) that generates snapshots from all the databases on a server. Simple task. However using their tool saves me tons of time from manually doing it. And using the API saves me even more time. Minutes compared to hours. Recently I purchased a monitoring tool that has given me the peace of mind that something is always watching my database, and will let me know when or if something goes awry. Being a single DBA with no NOC to watch my systems, this has greatly improved my life and as mentioned, peace of mind.

All this is praise for a job well done, and should be expected of a company providing tools and apps and simply trying to sell their wares to us. However, this company does a bit more. They interact with the community and you as an individual. I have many stories that I could tell you. Some of them involving some funny situations. Find me someday, and I will relate them to you. The best involves a bio break + phone call. But the one I wish to relate today is the personal touch that they perform that affects me and my life. They reach out and in a way no other company ever has, touches you. They make sure that you know you are important to them.

The actual Story

The story is simple and short and happened in the last couple days. A few months ago I received a package from RedGate that contained some swag. One of the items was a lanyard. My son saw this and wanted to have it. He transitioned it into a school badge holder, which he wears to school every day. Monday of this week he participated in a school play, and was proudly wearing his Red lanyard along with his play costume, which comprised of a few pieces of gold material around his wrists and head. I took a picture of him in this getup, with the proud smile. Since he was wearing the lanyard, I figured I should share this with the twitterverse, letting RedGate know via hashtag. Her is the picture.

This photo is me just showing my support for my son and by extension for RedGate.

A day later, I received a picture from RedGate that showed that at least 10 people were thinking of me and my son and wanted to touch us from across the pond. Here is that photo.

How awesome is that!?!?!? Have you ever had a company go out of their way to show their support for you in such a fun and silly manner? Simply having a good time, but at the same time, making that connection that we have both forged over time that much stronger. I owe each of these individuals a hug or a heartfelt hand shake, which ever they feel most comfortable with. I bet though, by looking at the that way they responded, they'd opt for the hug.

Way to go guys. You made quite a few of us happy today with this response, and I can't wait to show my son later today what you've done for him also.

Thanks.

You already have my loyalty and appreciation and even a bit of my and my companies money. This is simply over and above. And we love you for it.

running, working out

If you are like me, you may have noticed that you are not getting any younger. In an effort to further my existence here on planet earth, and ensure that I have time to attempt to understand all that Paul Randal says on a daily basis, I need a lot more time ahead of me. A lot of time. In order to ensure that I stick around, one thing I can do is watch my health. Inclusive of many other things I can do, working out is high on my list. It used to not be, and in the past few years has increased in its priority. Due in no small part to the constant reminder of a great influence on my life that is embodied in the person we all know as Steve Jones. Seeing his constant reminder of what he has done on a daily basis for his health has propelled me forward when I really didnt feel like it to do my own exercise. Not to keep up. Not to compete. Simply his shining example has fortified my resolve, allowing me the extra added boost on more than one occasion to go that extra mile and get off my butt and do something. It is so appreciated and I find it a daily reminder to get up and do something active. Thanks Steve.

It is at this point that if you live in Colorado, near some horses, and answer to the name Steve Jones, you should stop reading this blog post.

Thanks for reading thus far, but seriously, this is for everyone else now.

Ok, I think he's gone. If you fall outside of the above criteria, and have found yourself influenced by Steve to go a little more, dig a little deeper, I'm interested in hearing your tale. Email me at tjaybelt@yahoo.com with your story or words of thanks or whatever. This is an idea in the inception only and I simply want to do something to say thanks back to him for his influence. Especially as he approaches the landmark goal of 1000 sequential days of running.