Starting MONGODB - NoSQL

I heard about MongoDB from a friend and wanted to try out open source database.

Installing Mongo DB

http://docs.mongodb.org/manual/tutorial/install-mongodb-on-windows/

I am installing MongoDB on windows 7. I have downloaded windows version from Mogodb website and unpacked msi.

• move bin folder to C:\MogoDB\
• create a directory for data - c:\data\db
• Setting data path
• c:\mongodb\bin\mongod.exe --dbpath c:\data\db

Starting MongoDB service

Open a command prompt

C:\MongoDB\Bin\Mongod.exe

Waiting for connections on port 27017 means MongoDB service is successfully started.

Open another command Prompt to connect to Mongo Database

Type

C:\mongodb\bin\Mongo.exe

You will see the error that cannot find the file mongorc.js

So you tell the shell to ignore looking for .mongorc.js

C:\MongoDB\Bin\Mongo.exe --norc --shell

Now you are successfully connected to database.

type 

db

it will return test, connecting to test database

use mydb

show collections

Generate test data in a loop

Read inserted data

It was fun to play with MongoDB. More posts will be coming soon...

AlwaysOn DMV

select * from sys.dm_hadr_cluster
select * from sys.dm_hadr_cluster_members
select * from sys.dm_hadr_cluster_networks
select * from sys.availability_groups
select * from sys.availability_groups_cluster
select * from sys.dm_hadr_availability_group_states
select * from sys.availability_replicas
select * from sys.dm_hadr_availability_replica_cluster_nodes
select * from sys.dm_hadr_availability_replica_cluster_states
select * from sys.dm_hadr_availability_replica_states
select * from sys.dm_hadr_auto_page_repair
select * from sys.dm_hadr_database_replica_states
select * from sys.dm_hadr_database_replica_cluster_states
select * from sys.availability_group_listener_ip_addresses
select * from sys.availability_group_listeners
select * from sys.dm_tcp_listener_states
select object_name,counter_name,instance_name,cntr_value
from sys.dm_os_performance_counters
 where object_name like '%replica%'

Run a Sql Agent job from SSRS and check the status of the job

I am working on a DBA Dashboard with SSRS 2012 which displays useful reports of all our 80 + SQL Servers environment.

Most of the reports use powershell to retrieve information from SQL servers. ETL is used to load data to DBA data warehouse and SSRS is used to display data. In the backend, Powershell script and ETL packages are scheduled in SQL Agent job.

In the process of learning how to call a SQL agent job from SSRS, I have learnt how to pass parameters to a new SSRS window and passing a parameter to a new report.

In DBA Reporting Dashboard, I  want to refresh a specific report. I added runaJobtorefresh column in DBA Dashboard table and populated the column with "Refresh". So, Refresh link will appear beside the report.

A refresh link will call a job from SQL agent and run the job. Then, I will link that to another report which will show refresh status of the report.

The DashBoard dataset is retrieve from a Table which is already populated with ReportName, JobName, ReportURL and Refresh. In RunaJobToRefresh column display Refresh word.

Right Click on RunaJobTORefresh textbox and open properties.
Go to Action tab, Check Go To URL and we will type expression in Select URL.

="javascript:void(window.open('http://ServerName/ReportServer?/DBA_Reports/RunAgentJob&rc:stylesheet=hideToolBar&JobName="+Fields!JObName.Value+"','_blank','resizeable=1,toolbar=0,status=0,menu=0,top=20,left=20,width=740,height=350'))"

In the expression, rc:stylesheet=hideToolBar is passed to hide report server toolbar of the linked report (RunAgentJob). I have created a stylesheet hideToolbar.css and saved it under C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\Styles\
The content of the file is .ToolBarButtonsCell{display: none;}

If I click Refresh, a new window will open and pass a JobName from DashBoard report and call the following linked report - RunAgentJob.

The screenshot below is RunAJob report which display notification "Refreshing this report will take 2 mins. "  and a hyperlink "Click here to check refresh status"

RunAgentJob report receive a paramenter = JobName from Dashboard report and call a stored procedure which is sp_startjob @jobName

CallAjob Dataset will run a storedprocedure

DataSource will be created on the server where agent job is created. 

And then create a parameter Job Name, visibility is set to hidden

On RunAgentJob report, I have created another link "Click here to check refresh status". By clicking on the link, you will be able to see the status of refresh report.Basically, the link open another linked report called RefreshedJobStatus which pulls the status of the job running at SQL Agent.

Click on Text Box Properties, 

In Action tab, It will open RefreshedJobStatus report and passed a parameter JobName to the report.

RefreshedJobStatus report will call a stored Procedure - usp_RefreshJobStatus. In the stored procedure, 

CREATE PROCEDURE [dbo].[usp_RefreshJobStatus]

-- Add the parameters for the stored procedure here

(@JobName varchar(256))

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

    -- Insert statements for procedure here

create table #run_job ( 

Job_ID uniqueidentifier, 

Last_Run_Date int, 

Last_Run_Time int, 

Next_Run_Date int, 

Next_Run_Time int, 

Next_Run_Schedule_ID int, 

Requested_To_Run int, 

Request_Source int, 

Request_Source_ID varchar(100), 

Running int, 

Current_Step int, 

Current_Retry_Attempt int, 

State int 

)       

insert into #run_job 

     exec master.dbo.xp_sqlagent_enum_jobs 1,garbage  

select 

sj.job_id,

sj.name,

'Enabled' = 

CASE 

WHEN sj.enabled = 0 THEN 'NO'

WHEN sj.enabled = 1 THEN 'YES'

END

,sja.sed As RunningJobStartDateTime,

'Current_execution_status'= CASE

WHEN #run_job.State = 0 THEN 'Not idle or suspended'

WHEN #run_job.State = 1 THEN 'Executing'

WHEN #run_job.State = 2 THEN 'Waiting For Thread'

WHEN #run_job.State = 3 THEN 'Between Retries'

WHEN #run_job.State = 4 THEN 'Idle'

WHEN #run_job.State = 5 THEN 'Suspended'

WHEN #run_job.State = 6 THEN 'WaitingForStepToFinish'

WHEN #run_job.State = 7 THEN 'PerformingCompletionActions'

END

,#run_job.Current_Step,

sjs.step_id,

sjs.step_name,

CASE SjSe.[Last_Run_Date]

        WHEN 0 THEN NULL

        ELSE CAST(

                CAST(#run_job.[Last_Run_Date] AS CHAR(8))

                + ' ' 

                + STUFF(

                    STUFF(RIGHT('000000' + CAST(SjSe.[Last_Run_Time] AS VARCHAR(6)),  6)

                        , 3, 0, ':')

                    , 6, 0, ':')

                AS DATETIME)

      END As LastRunDateTime,

   sjse.last_outcome_message,

STUFF(

            STUFF(RIGHT('000000' + CAST([SjSe].[last_run_duration] AS VARCHAR(6)),  6)

                , 3, 0, ':')

            , 6, 0, ':') 

        AS [JobLastRunDuration (HH:MM:SS)],

'Notify_Email'=CASE

WHEN sj.notify_level_email= 0 THEN 'NEVER'

WHEN sj.notify_level_email= 1 THEN 'When the job succeeds'

WHEN sj.notify_level_email= 2 THEN 'When the job fails'

WHEN sj.notify_level_email= 3 THEN 'Whenever the job completes (regardless of the job outcome)'

END

from #run_job

join [msdb].[dbo].[sysjobsteps] As SJS on #run_job.job_id= SJS.job_id

left join  [msdb].[dbo].[sysjobs] sj on sj.job_id = sjs.job_id

left Join msdb.dbo.SysJobServers SjSe on sjse.job_id = sj.job_id

left join(

select sysjobactivity.job_id,max(sysjobactivity.start_Execution_date)sed from msdb.dbo.sysjobactivity

where sysjobactivity.start_Execution_Date is not null and sysjobactivity.stop_execution_date is null

group by sysjobactivity.job_id) as SJA  on

sj.job_id = sja.job_id

where sj.name = @JobName

Order by sj.Name,sjs.Step_id

drop table #run_job

END

In DataSet tab,  Call the stored Procedure.

In Parameter , JobName is set up as a Parameter and visibility set to Hidden.

In report Propertites, AutoRefresh = 10 sec.

Now, we are succesfully create a report which can be refreshed on Demand from SSRS.

My references are

http://www.sqlservercentral.com/blogs/dknight/2009/11/11/using-a-ssrs-report-to-run-sql-agent-jobs/
http://nishantrana.me/2012/10/20/hiding-tool-bar-from-the-ssrs-report-while-using-them-in-dashboard-crm-2011/
http://thebakingdba.blogspot.com/2011/04/jobs-quick-dirty-is-job-running-another.html

Enjoy Scripting...

TFS - Building Database Project - PART III

Deploying a Build Definition

....Part II Continue........

45. Right click on the definition and Queue New Build

46. Click on Queue

 47. The build succeeded and a new table is added to existing database.

Now, we have created a build for database warehouse successfully.

TFS - Building Database Project - PART II

Updating and Creating a Build for an existing solution

....Part I continue......

32. Add a new table to an existing solution

33. Check in the table.

 34. Save the project

 35. Write comment in Check In

36. Then, Check In Database solution level.

 37. Under Builds, Choose new build definition

 38. Click on Source Settings and Delete all existing folders.

 39. Then, choose solution file from TFS

40. Click on Build Defaults. Choose yellow highlighted option

 41. Click on Process

 42. Click on Advanced

In MSBuild Arguments, paste the following with correct xml profile name.

/t:Build /t:Publish /p:SqlPublishProfilePath=DBA_ServerDW.publish.xml

44. In Builds, the new build definition is created and listed