Friday, June 30, 2023

TSQL Database Deployment script

 Following things keep in mind while preparing database deployment script.

1) Include IF EXISTS THEN DROP Statement

2) Include IF NOT EXISTS THEN CREATE statement

In this way, we can avoid failure of deployment script during dpeloyment. the advantage of giving correct deployment script is deployment will be seamless otherwise we need to schedule deployment again. It may affect Production GO LIVE too. Similar scripts can be used for reverting the changes if something goes wrong after deployment. Below is the example of database script which is used for deploying database object. 

/****** Object:  StoredProcedure [dbo].[GetProductData]    Script Date: 22/01/2023 15:32:56 ******/
		FROM sys.objects
		WHERE object_id = OBJECT_ID(N'[dbo].[GetProductData]')
			AND type IN (
	DROP PROCEDURE [dbo].[GetProductData]



		FROM sys.objects
		WHERE object_id = OBJECT_ID(N'[dbo].[GetProductData]')
			AND type IN (
	EXEC dbo.sp_executesql @statement = 


	This stored procedure is used to extract product data 


Date:			Author:		Description: (CR#, Ver, Bug# etc) 
-----------		----------- ------------------- 

CREATE PROCEDURE [dbo].[GetProductData]
@ProductNumber	NVARCHAR(10), 
@Createdby		INT, 
@lReturnCode	TINYINT	= 0 OUTPUT, -- Success -0, Failure -1 
@sMessage NVARCHAR(500)	= NULL	OUTPUT -- Success/Error Message to FrontEnd 

FROM ProductCategory VC (NOLOCK)
            INNER JOIN ProductData   VD (NOLOCK) 
ON VC.ProductNumber = VD.ProductNumber 
  SELECT @lReturnCode = 0  
 DECLARE @lErrorNumber INT, 
  @lErrorSeverity INT, 
  @lErrorState INT, 
  @lErrorLine  INT, 
  @sErrorProcedure NVARCHAR(200), 
  @sErrorMessage NVARCHAR(4000); 
 --/* -- Assign variables to error-handling functions that  
 ---- capture information for RAISERROR. */ 
 SELECT @sErrorMessage = ERROR_MESSAGE(), 
  @sErrorProcedure = ISNULL(ERROR_PROCEDURE(), ''-''),  
  @lErrorNumber = ERROR_NUMBER(), 
  @lErrorSeverity = ERROR_SEVERITY(), 
  @lErrorState = ERROR_STATE(), 
  @lErrorLine  = ERROR_LINE(), 
  @lReturnCode = 1 


SQL Server - Database Deployment from QA environment to Production Environment

The objective of this document is to provide simple steps for SQL database deployment from one environment to another. For example, after completion of Testing in QA environment, it is best place to script the database and can deploy in another environment.

Following are the steps to deploy database from QA environment to Production environment:

1) Production Backup:

     a. Connect to the Production Database instance

     b. Take the backup of the MYAPPDB database

2) Scripting from QA environment

      a. Open the SQL Server Management Studio

      b. Connect to QA database server instance and script the required database object.

      c. Use if exists then DROP THEN CREATE Statement

      d. Mention the list of scripts so that same can be verify after deployment.

• [dbo].[usp_script1]   

• [dbo].[usp_script2]   

• [dbo].[usp_script3]   

• [dbo].[usp_script4]   

3) Script Deployment to Production environment

      a. Connect to the Production Database instance and deploy the script.

      b. Check the following stored procedure should be present in the Production database

• [dbo].[usp_script1]   

• [dbo].[usp_script2]   

• [dbo].[usp_script3]   

• [dbo].[usp_script4]   

4) Rollback (If required)

      a. Restore the MYAPPDB database from Backup if there is any issue.

Example of Configuration table in SQL Server

Following is an example of configuration table in T-SQL which can be used for configuring the paramter(s) and providing the values from this table. Parameter can be text, integer or date type, we have separate column for parameter values with respect to their data type




CREATE TABLE [dbo].[Configuration] (
	[Parameter_ID] [int] NOT NULL
	,[Parameter_Name] [varchar](35) NOT NULL
	,[Parameter_Date] [datetime] NULL
	,[Parameter_Number] [float] NULL
	,[Parameter_Text] [varchar](255) NULL
	,[Active] [bit] NOT NULL
	,[CreateUser] [varchar](50) NOT NULL
	,[CreateDate] [datetime] NOT NULL
	,[ModifyUser] [varchar](50) NOT NULL
	,[ModifyDate] [datetime] NOT NULL
	,CONSTRAINT [PK_dbo_Configuration] PRIMARY KEY CLUSTERED ([Parameter_ID] ASC) WITH (


Recommendations for regulating E-Waste & Emission


Directive of Digital Waste Management

 1) Now it a time to change the word “Online” to “OnTime” platform which means in place of unnecessary online system, we can fix the time to make it online.

2) We need to make understand to People that there is a mutual understanding between nature and cyber world

3) Social media platform should not be 24*7 online. We need to fix some time to get relax from this. It is like a meditation where we left everything for some time and again back with great energy.

4) Social media should put statutory message that using unnecessary social media causes global warming

5) Chatting apps like WhatsApp telegram should have statutory warning like sending unnecessary message causes great electronic waste.

6) Its a time to adopt “Ontime” platform in place of “Online” platform

7) Chatting app company should strongly recommend using text data instead of images & video. Because Images & Videos has made a new ocean in our planet which worldwide cyberocean.

8) Due to technology advancement, high performance & energy efficient hardware are available but still there is a need to review whether 24*7 online is required or not.

9) Its a time to leave the word “Online” in cyberspace. In place of online, we need “Ontime” platform which will be available for a specific time where people can avail the services. It should goes offline when no one is going to use.

10) Those portals which required 24*7 Ontime can be avoided

11) Still many websites can also go offline during late light hours for few hours.

12) Digital Sanity should be included into Swachh Bharat mission of Govt of India.

14) It has been realized that people of India loved this mission and has taken part into this mission with great excitement.

15) If we give the message of electronic waste management through Swachh Bharat mission, people of India will definitely take it seriously. Similar message will go to the other countries for improve the e waste management.

16) If company don’t allow the free storage to the users then then user will automatically start clearing the unnecessary data

17) There should be a limitation to send a mail to limited no of recipients.

18) There should be a limitation for personnel ids for sending limited no of emails per day

19) There should be policy to distribution list to have time to time review so that unnecessary mails should not distribute

20) Unnecessary notification should be avoided.

21) For Announcements, company should use portal instead of sending mails to all the employees

22) There should be limitation for daily attachment size quota

Wednesday, June 28, 2023

Benefits of SSAS Performance Monitoring and Reporting

 By consolidating SSAS performance data, we can build variety of performance monitoring reports that will help us in taking right decision in right time in terms of application performance degradation, system breakdown, need of hardware/IO resources etc. Below is list of some of the reports that will provide key performance indicators/ information to improve the performance.

Activity Reports

o Detail Activity Report for Multidimensional as well as Tabular Model 

o Long Running Query Report for MDX and DAX

o CPU/Memory/IO/Disk utilization Report

Performance Reports

o Calculated member wise Performance Report

o Measure Group wise Performance Report 

o Measure Group wise Aggregation Report 

o User Logging Report 

o ROLAP Query Performance Report

Cube Processing Report

o Measure Group wise Processing Report

o Storage Mode Disk Usage Report

Advisory Reports

o Advisory Report for Measure Group Partitioning

o Advisory Report for Design Aggregation

o Advisory Report for Design Hierarchy

o Advisory Report for Attribute Relationship

o Advisory Report for Storage Mode


o System Performance Dashboard

o SQL Serve Analysis Services Dashboard

Data Sources for SSAS Performance Monitoring

The following sources can be involved in the ETL processes to consolidate the performance data from SSAS instances for Performance Monitoring and Reporting:

SSAS instance(s) both Multi-dimensional & Tabular Model as source

SSAS logging

SQL Profiler Trace

Performance Monitor

Dynamic Management Views(DMVs)

SSAS Partition wise Disk Usage Report

Objective The objective of this report is show size of the partitions available in the cube and which storage mode is used by those partitions. This report will help to identify which partition is taking more space and help us in taking decision that which storage mode to be chosen for the partition. Choose correct storage mode will improve processing performance as well as data retrieval performance of the SSAS Cube


Business Logic

• This report shows details about all the partition(s) available in the Cube with their storage mode and partition size.

• This report helps the user in understanding how data is managed in the Cube.

SSAS Dimension Performance Report

Objective The objective of this report is monitor progressing time of all the dimensions as well as data retrieval time duration so that bottlenecks can be identified to improve the performance of SSAS Cube.


Business Logic

• This report shows dimension wise performance statistics having Duration and CPU utilization while browsing the data.

• This reports helps the users to take decision on adding or removing the attributes from the dimension in case some of the attributes are not required for business.

• It also helps in taking the decision for creating the hierarchies.

• Bases on this report, System will provide Advisory Report for creating hierarchies in dimension.

SSAS Partition wise Processing Report

SSAS Cube gets data and do pre aggregation from OLTP systems when we process the cube. We can divide the cube in various partition so that cube can be process in smaller partitions in place of one bigger cube. Partitioning the cube also help in retrieving the data faster. Sometimes processing of SSAS Cube takes lot of time to complete the processing. So it is required to troubleshoot the processing query which increases the processing performance. Below is the report layout and business logic to develop Partition wise cube processing report.


Business Logic

• This report shows total processing time taken by partitions in the cube.

• This report helps the users to understand the which partition(s) are bulky in nature.

• Bases on this report, System will provide Advisory Report for creating partitions in the report.

SSAS Measure Group wise Query performance Report

While monitoring the performance of SSAS Cube, we can drill down the performance monitoring at Measure group levels. Cube contains multiple Measure Group (s). Performance monitoring at Measure group levels can provide which particular measure group and it’s queries degrading the performance of Cube. The advantage of this report is we can target a particular measure group to fix the performance issue in place of trouble shooting enitre cube.


 Business Logic

• This report shows total aggregations available at Measure Group level for the Cube(s)

• This report helps the users to understand how aggregation has been designed in the cube. If some of the measure group(s) shows larger no. of aggregations then users can check for any unwanted aggregations.

• Bases on this report, System will generate Advisory Report for Design Aggregation.

SSAS Query Performance Detail Report

The objective of this article is provide information on how to monitor Cube performance by logging their query logs and display costly queries.

Below is the layout for Cube wise Query performance detail report which shows all the queries which degrade the performance of SSAS Cube.


Business Rule

This report will show the Query Performance Statistics for all the queries logged in the Cube(s) in an instance of SQL Server Analysis Services

In this Report, we will show SSAS Cube wise query details, Cube, CPU time taken by the query and duration.

This report helps the users to understand what are the queries that takes more time for their execution

Based on this Report, User(s) can take necessary decisions to improve the performance

System will show this report from the data comes from the SQL profilers

Features of Modern days Performance Monitoring Tool

1. This tool can be a web based tool so that it is easy to access the tool.

2. It can have windows security integrated with active directory for better security else basic authentication is also fine (if applicable).

3. It can be integrated with SharePoint.

4. It should provide a choice to select your performance data.

5. It should maintain the historical performance data that can be utilize for comparing the current performance data.

6. Email notification or SMS Alert should be there if there is severe performance degradation.

7. Data alert to users based on criteria set by user.

8. Report subscription for email/file share

9. Performance data schedule mechanism.

10. Highly interactive data visualization reports 

Summary and detail Performance reports

Performance Data Point Dashboard Reports 

Performance Advisory report so that preventive maintenance can be taken before some breakdown.

11. It should covers all the performance data points of your system.

Tuesday, June 27, 2023

Onboarding Process Document for Software / IT Projects

1. Background
The objective of this document to provide a detailed information about Onboarding process for an IT/Software project. It will include client communication, HR policies, Information Security policies, Setting up of environment, access to envrionment and compliance training(s).

2. Sending Team information to client
Once the team / team member is identified for the project then following personal information about the team member need to share to client so that client will know the project team. It is also useful when Account /Email id creation process is required to work on client's environment.

1. Full Name include middle name if any e.g. Vijaya Lakshmi Nair
2. Date of Birth
3. Employee ID
4. Email ID
5. Designation
6. Office Address/City

3. HR, Information Security Policies and Acknowledgement
Company HR will reach out to each team member via email to share information about their HR, Infrastructure & Information Security policies, confidential information protection law and emergency evacuation. (if applicable)
Project team has to physically sign some of the document(s) and acknowledge to HR Team.

1. Emergency Evacuation form
2. Safeguard of information Policy acknowledgement
3. Notice of obligation to protect Client confidential information

4. Setting up of Environment
Which may include VMWare or cloud Workspace Setup document per guidelines by Client Team.

1. Setting up of Mobile Authenticator (if applicable)
2. Client's Compliance Training
Mention the List of compliance trainings and steps to complete the trainings

5. Helpdesk
For any IT/Infrastructure/Access related issue, Team can reach out to Helpdesk team by following(s):

1. Call to Toll Free no:
2. Raise a Ticket at helpdesk url

6. Access to Environment
Client Team will provide the required access per project requirement. Prepare access matrix which may include team(or role) wise access.