SQL Server security best practice

Security! This is the word comes in mind of every concerned person when it come...

Change the Collation Settings in MS SQL Server

This post will show you how to change the collation settings in MS SQL Server for specific database...

Resolve collation conflict

In this post I will show you how you can resolve collation conflict error...

Book: SQL Server 2008 High Availability

In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...

Why to recompile Stored Procedure

Generally, we create views and stored procedures (proc here after) ...

11/20/2016

New home for SQL Server Citation



Change is inevitable, it's natural. I would like to quote few lines from Shrimad Bhagavad Gita says     Change is the law of the the universe.  In my opinion, change will bring good to your life, always. 

In 2004, I have started this blog, like others, this blog was started to make notes for myself so that it's available for me whenever and wherever I want to refer. Initially this blog was not public, as I was thinking and fearing that whoever is going to read will laugh at the way I am writing, the grammatical mistakes I do, the topic I choose to write and so on, and the most stupid reason was no one is going to read my blog.  I was reading few articles based on Shrimad Bhagvad Gita, where I read the above quote, and I thought, okay, let's begin the change. I made this blog public, conquer my fear of thinking people laughing at me and my mistakes. Now, I thought, the only way to learn new things is to read, and try, unless you don't do mistake you will have very less chance to learn how to improve, and that's why I never believe in perfection - it will never make you think of improvement, there is no 100% in my dictionary, it's always 90%. I remember the days when SQL Server Pedia and was managed by Brent Ozar, I wanted my blog to get syndicate there but I could not do that. The reason was grammatical mistake, and, since my attitude was to correct my mistake, Brent introduced me to Lori Edwards ( B ), Jeremiah Peschka ( B ) and Patrick Kansa ( B ) who have helped me tremendously to proof read my writing, suggesting and help modification in my posts. I would like to thank Brent Ozar, Lori Edwards, Jeremiah Peschka and Patrick Kansa for helping and inspiring me, and everyone who have helped me grow .

When  HA was newly introduced and was buzz word, I have been on some projects where I have to helped customer, and I failed it but then, I was able to author a book on that very topic, for beginners though. That means, I love and believe in the quote that I have mentioned in starting.  I was amateur blogger then, and I am still. 

This blog space is hosted on Blogger platform, it has served me well. But as I already mentioned, changed is inevitable and is only constant. Few days back, in my last post I have mentioned that it will be the new beginning. Well, here is an announcement, this blog space is moving to somewhere else. It will be on word press platform. It is still in in designing phase, I will make an announcement of it's new home in my next post. 

Keep loving me and keep visiting.


photo credit: forrest.wheatey Moving box via photopin (license)

photo credit: Yevy Photography paint via photopin (license)

11/07/2016

A New Beginning #SQLFamily

For the years I have been watching the SQLPASS session recording and hearing that it's really a different experience to see all that happening for real, In-Person!! I was always wondering if I can get a chance to attend SQLSummit and after little while I have my chance - thanks to @sqlpass and +Pythian for helping me.

In the month of May I was able to secure my seat to #SQLSummit and I was so happy and excited as it was my first time, I was excited to see my extended family - #SQLFamily and lots of other folks  whom I have interacted past 6~7 years and haven't had chance to meet In-Person, and, of course there are many technical sessions to attend and learn.

Preparing - Not much on this part, as I have already read my friend Edwin Sarmiento's ( B | T ) guide to First Timers, I would recommend every one to read this guide, extremely helpful. 

I have started my journey on 22nd October from Surat, my native and the city I live and reached Seattle on 23rd October. It was a long journey but it's okay as I was excited to see my colleagues, at the airport I met Amit Bansal ( B | T ) and Luan Moreno ( B | T ) later meet Edwin Sarmiento, Murilo Miranda ( B | T ). We've been working since 4 years and had many conference/video calls but this was the first time we see each other. +Pythian has sponsored our stay which was nice and just about a block away from conference hall, so convenient, Thank you +Pythian. Technically (according to IST), it was my birthday which I have spent with my friend Ashish Sharma ( L | T ), Shreya Sharma ( L ) and Luan. Ashish and I know each other since 2008, not so long but we share a relation like brothers do, he lives in Vancouver, Canada, and he drive all the way to Seattle just to meet me, we had lunch, did some shopping and of course we talk a lot, love you bro!!  

Day - 0
24th October, the day started with fun, Luan started taking sadistic pleasure and confusing me around time zone difference, every hour he reminds me what time it would be for me. At the end of the day, headed towards Pike Street and visited StarBuck's first every coffee shop.

Day - 1
On the 25th October I completed my registration and headed to attend chapter leaders meeting where I met Gogul Aryalingam ( B | T ) who is RM for APAC region of SQLPASS for the first time, and we are friend now. Later that day, I met my good friend Pinal Dave ( B | T ) and make a new friend Paras Doshi ( B | T ), and we recall old memories. 

Day - 2
Here comes the real excitement, the actual first day of the #SQLSummit, attended the key notes and then I saw Zaheer Khan (No, not an Indian Cricketer) who was my Team Manager when I was working with Satyam back in 2007, and it was this time I met him after 2007 and I wasn't aware that he's attending. Since I have developed my interest in Azure, most of the sessions I have attended are around Azure, along with Leadership (non-technical) and HA related sessions. This day I met few more colleagues of mine - Warner Chaves ( B | T ), Alejandro Cordero ( B | T ), Chris Presley ( B | T ), Scott McCromic ( B | T ) and Shara French (B | T ). met John Eisbrener ( B | T ) who was my Team Manager in recent past, aah I met Amit Banerjee ( B | T ) as well, Luan and Murilo presented on Enterise Database Administration and Deployment in lightening talk. 

Day - 3
Again, attended few more sessions and during break I met few more people, like Yvonn Vansalageren, Sourav Mukharjee ( B | T ).  This evening, attended the annual blogger's meet organized by my employer where I met Paul Vallee ( B | T ) who is a founder and CEO of +Pythian, he is amazing person, also meet some of other bloggers.

Day - 4 
Since the morning, it was mix feeling of excitement and sadness as it was the last day of the event. Again, had attended some sessions and meet my new and old friends, felt so sad to say good bye until next time to my extended #sqlfamily and I am agree with each word written by Edwin in his post - I so hate leaving my #SQLFamily.

Day - 5 
Edwin left the place early morning to catch his next flight, Luan left couple of hours later to catch his flight to his home while I and Murilo was waiting for our cab as our flight will take off about noon. We both boarded in the same flight and bid good bye each other at Paris airport, promising each other to see you soon (team meeting). 

Day - 6,7 & 8........
Guys, this is the fun part, I build a story, a conspiracy to surprise my family - since the day I landed in Seattle I was saying my family that I will be back home on 4th November on my daughter's birthday,. After I landed at Mumbai airport on 31st mid night I headed to Andheri station to catch train to my native, I have already talked to my wife and kids that I will be calling on Skype about 11 AM and we'll have video call so we can see each other, as, 31st is celebrated as new year in Guajrat, everyone was agreed, technically "on the same page".  And, when I reach home, I was surprised, no ones at home, they went to temple to have blessings and I waited for them in reception area of my society. But yes, it paid off finally, my daughter was so happy to see me, and my son too!!  For next 2 days, I was like zombie due to Jetleg.  

I know, it hast to end but that's the sad part, and there is a happy part as well, a new beginning - I am so happy that I have decided to meet my #SQLFamily in one or other conference, sessions in India and other part of world, and to do so, I would participate in more activities hereafter. Starting with joint event of Surat and Mumbai SQLPASS Chapter, I will post more details after we freeze schedule, keep watching this place for more details.

I met Pinal afte 6 years and we talk a lot during conference, almost daily and we talk about NoSQL; I mean, not about SQL, its about family, city, leaving, job etc everything but SQL :). Sameway, a lots of discussion with Luan, Murilo and Edwin and we never discuss about techical stuff, we have team meeting for that, those days were for friends, and I really enjoyed it very much. I wish I could have some more time to meet amazing people like Brent Ozar ( B | T ), Parikshit Savjani ( B | T ) and Kevin Kline ( B | T ), I believe that the world is small, we'll meet in some other conference if not, for sure next time at SQLSummit. 

A photo album of the event, #SQLSummit

Source of Image: http://gph.is/1XOVmky

8/24/2016

Resolve Microsoft SQL Server Error Code 824

Since data consistency is a crucial aspect for every user, the server has in-built mechanism which helps to maintain the data consistency at every step. Sometimes, the user may face SQL Server Error 824 while making any SQL query or data modification. It is mainly inconsistency based error that reports an inconsistency during the I/O operation being carried out in the database. When such error is reported by the server, the control automatically comes to the application and the database connection is terminated. The following article is aimed to provide a clear understanding on SQL Server Error 824 and the efficient ways to resolve the error.

What is SQL Serve Error Code 824?

While working on the SQL queries, the user may suddenly face the situation where error 824 is displayed on the screen and database connection is terminated.

The SQL Server error number 824 depicts the following information, which can be useful fir the user during problem resolution:

  • The name of database file for which I/O is inconsistent
  • The file offset for a particular location
  • The actual database name of the inconsistent file
  • The page number for inconsistent I/O operation
  • Details regarding failure of consistency check

Causes of Microsoft SQL Server Error 824

The main reason behind this issue is associated with Windows API that are used by SQL Server to carry out I/O operations. It may be possible that the Windows API have successfully read data to the disk, but the data may have suffered some logical inconsistency. As any kind of logical inconsistency immediately raises an error in SQL, the error code 824 may be the indication of such data inconsistency.

However, other possible reasons behind encountering this error may include:

  • Insufficient disk storage leading to data inconsistency
  • Faulty hardware associated with the I/O devices
  • Highly damaged or corrupted database files
  • Faulty filter drivers in the path of I/O operation
  • Corrupted file system leading to data inconsistency

Resolve SQL Server Error 824

The error 824 can be faced due to many reasons, which have been discussed above. So, the user needs to go through the following measures to resolve the error and resume the efficient working of the SQL Server.

- Run the DBCC CHECKDB command to carry out the consistency check. It scans through the entire database to find its consistency and integrity. Any inconsistency found in the database will be immediately pointed out by this command. Steps can be taken by the user to remove inconsistency.

- Look for the PAGE_VERIFY CHECKSUM database option. It must always be turned on during the database processing as it verifies the data consistency after every write operation on the disk.

- View all suspect_pages of the same database or different database. Check whether the same error is encountered in these pages.

- Check the Windows Event Log to find any generated error from operating system or device driver. If any error is encountered, make sure to fix those errors first.

- Make sure that the device drivers and other associated I/O software are updated.

- Check for the updates of the filter drivers that are used along the path of I/O operation.

- Check whether the filter drivers can be deactivated or not. If user is allowed to disable the drivers, then the filter drivers must be disabled.

Conclusion

Microsoft SQL Server Error 824 is generally reported by the server to indicate any kind of inconsistency faced or I/O devices incompatibility. The various manual ways to resolve the error have been discussed above. The user having a clear understanding of SQL concepts and I/O devices can attempt to resolve the error quickly. An automated third party tool, SQL Recovery allows a complete recovery of the database files without any data loss. However, users are always advised to maintain the database backup at a regular time interval.

7/25/2016

Learn About SQL Server Master Data Services 2016

SQL Server 2016 comes up with the new release of Master Data Services (MDS). In it, some new features have been added to improve its performance. It helps the database administrators to manage the database properly. In the following discussion, we will be discussing the changes that have taken place in SQL Server Master Data Services 2016.

In-Depth Study of Latest Features in Master Data Service

There are various changes that have taken place in MDS SQL Server 2016 are discussed below:

Configuration control

There is a control on logging configurations for models. Models enclose the entities and the entities contain data. The data have a log file that keeps on increasing depending upon the transactional data. Therefore, it is important to have the control on retention and it is a necessary feature. In MDS 2016, user can control it with the mentioned setting under Model Configuration. It also gives an option to manage the Log retention days. By default, it is 1 and indicates that the log tables will not be cleaned. If the value is 0, then the log tables preserve only day’s work. The data logs of the previous days are truncated. If the value is greater than 0 then, the log will retain for number of days that is specified by value. The default value is -1, which means that log will not be deleted. Therefore, user must take care while changing it as it contains the history of transactional log, validation, and staging batches.

Data Compression

Row-level security in SQL helps in compressing the entity data and indexes it in a way to minimize the I/O operation. It is helpful at the time, when values of data are not so wide. However, there is an increase in CPU usage with this compression, which is needed to be considered when there are some limitations on CPU.

Explicit Hierarchies

SQL Server Master Data Services previously had two kinds of hierarchies, i.e. Explicit and Derived. Explicit hierarchies are built for some specific purpose of dynamic level of hierarchies. Derived hierarchies are used to set one to many relations between the entities. In SQL 2016, Explicit is deprecated.

Entity Dependencies

In the previous edition of MDS, the relationship entity could be created in Excel Add-in but there is no way to be visible on web. The new edition of MDS has a tab in MDS explorer for it to view on web.

Functioning of Security

There is the new permission in SQL 2016, i.e. super user function. This permission works as Server Admin. In the previous edition of MDS, the server admin access is given to the user who configured the MDS and no one can change it. However, in this new feature in Master Data Service in SQL Server 2016 helps to resolve this issue.

Detailed-level Security

In the previous edition of MDS, there are three modes- Update, Read-Only, and Deny that limits the settings create only, or update only securities. Whereas in the new version, there are six modes available, i.e. Deny, Read, Admin, Create, Delete, and Update.

Display Attributes Name

User can display the name for columns in the latest edition, which helps in changing the display name of code. It makes easy for users to set the desired name accordingly. These were unchangeable in the prior editions of MDS.

Conclusion

In the above discussion, latest features in SQL Server Master Data Services 2016 are described. It helps users in having the deep understanding about the features of MDS 2016 for its proper usage.

6/24/2016

Problems When Running DBCC SHRINKFILE On SQL Server Database

Overview of the Situation

While accessing data files in SQL Server, many issues may occur that may lead to frustrating situation for users. One such situation occurs when user are running DBCC SHRINKFILE. There are many issues with running DBCC SHRINKFILE on your SQL Server data file. In the information described below, user will get to know the causes of this issue and relevant solutions to overcome from the situation.

Reason for Occurrence of Issue

The fragmentation plays a major role in the occurrence of an issue with running DBCC SHRINKFILE on your SQL Server data file. It occurs in two ways as mentioned below:

  • First is the fragmentation of file. As by default 1 MB of file size is used, commonly the file must grow. Though file fragmentation is not directly linked with the fragmentation that is within SQL table, it grounds extra IO operations.
  • Another form of fragmentation is index fragmentation. It arises when the SQL Server splits a page to permit for insertion of new row. The fragmentation of index can be restrained with
sys.dm_db_index_physical_stats

Workaround with the Situation

DBCC SHRINKFILE helps to shrink the size of .mdf file whether to a specific size or to a small size. It moves the pages of file from back to first free pages in front of file along through the importance the pages in reverse order ends up.

User can remove the fragmentation by rebuilding the index by using DBCC DBREINDEX or user can user ALTER TABLE with REBUILD option. User can use mentioned script to rebuild:

PK_Consume_Space

It helps to remove the fragmentation and after that, the user can measure both the space used by database and fragmentation.

Sometimes, the user notice that the size of database has grown from 18 to 42 megabytes. This is because the data is copied to new space in the file and there are two copies of data on disk. If the script is run to add extra space to defragmented index then, the space is allotted for the insertion of new rows without splitting. It helps to boost the performance at time of insertion, which helps to eliminate the fragmentation. User can note down the percentage, which signifies that the physical order rows in the table that are within the file helps to match the logical order of rows. This ordering of data helps in efficient reading the data.

There are the effective solutions with which user can solve the problem as mentioned:

  • User must have extra space in file. If the database keeps on increasing or very dynamic, the extra space is important to avoid the growth of file at the production time.
  • Another option is to rebuild a complete file. It requires CREATE INDEX with DROP_EXISTING statement for all the indexes that are present in file and can be difficult. The easy solution to use ALTER INDEX REORGANIZE or DBCC INDEXDEFRAG. It helps in rebuilding to remove defragmentation.

Conclusion

In the above discussion, some reasons of issues are discussed by running DBCC SHRIKFILE on SQL Server data file. Even along with this, a solution is also discussed which helps users to remove the issues with running DBCC SHRINKFILE on your SQL Server data files.

5/15/2016

SQLPASS SUMMIT 2016 Discount Code

SQLPASS SUMMIT 2016 is around, only few months left for this super event. Probably you all would have heard about the SQLPASS SUMMIT but let me just brief you about this event. This event happens every year during October in different cities in USA, 3 days event. Well, you would also ask why I would bother even if it's an event that run for 3 or 4 days? I would say, my friend, you just need to go to SQLPASS SUMMIT 2015 website and read some numbers, you will surprise to see that there are thousands of people travelling to attend this SUMMIT and to listens the renown speakers including MCM, MVPs. Edwin Sarmiento ( T | B ), my colleague, SQL MVP, MCM and friend is kind of enough to write a guide for SQLPASS SUMMIT First Timer.  

So, let me end the surprise here, want to attend the SQLPASS SUMMIT 2016? Then I have something that would make you happy, and, that is the discount code worth 150$, yes, you've read it right. What you need to do is, while you are registering you need to provide the below details and avail 150$ discount. 

Your Chapter: Surat User Group
Unique Discount Code: LC15KCF8
*The discount code cannot be combined with any other PASS Summit 2016 registration, and cannot be applied retroactively

I would also suggest that you:
  1. Don't forget to read a guide for SQLPASS SUMMIT First Timer by Edwin Sarmiento ( T | B )
  2. Use the Unique discount code when you register for SQL PASS SUMMIT
  3. I am attending the SUMMIT this year, if you happened to attend this year, let's meet. I will be on twitter  




Thank you
Hemantgiri S. Goswami

4/26/2016

How to Recover Data From Corrupted Log File In SQL Server ?

Introduction

SQL Server is a database platform designed for providing large-scale transactions, e-commerce applications, data mining, etc. It is widely used on Business platforms for data analysis, data integration and processing components.

Structured Query Language (commonly known as SQL) is a programming language used for managing data held in relational database management system (RDMS) consisting of data definition language, data manipulation language and a data control language. However, the SQL server database consists of three files:

  1. Primary Database File (MDF file)
  2. Secondary Database File (NDF file)
  3. Log file (LDF file)

Among these three, one database file is Log File. The file maintains a log of all the transactions done in SQL Server Database so that information can be later accessed to recover the database. There must exist one log file for each database and it is possible that more than one log file can be created for a single database. The file is in .ldf file extension.

How SQL Server Log File Corrupt?

The log files of the SQL server may be corrupted, i.e. while performing some action upon SQL database, an interruption in between the procedure is probable to occur due to which user first has to repair and recover the log file corrupted SQL server and then continue their transaction.

Causes of Log File Corruption

  • Viruses or other Malicious Software: In computer system, many viruses can infect and damage the log files and makes them inaccessible.
  • Terminating System Abnormally: If system/application is quit abnormally, then files are prone to be corrupted or damaged.
  • Input Output Configuration: The I/O subsystem is a vital component of database used to store system and user databases. Hence if the configuration is disturbed on enhanced that it may lead to corruption in log files
  • Storage Size Issue: The biggest reason behind the corruption of log files is the storage size. In case the data exceeds the limit of LDF, corruption is likely to occur.

How to Repair Corrupt SQL Log File ?

We will be dealing with corrupted log file for repairing it and recovering data from it by executing commands in SQL server as a hit and trial method. User may apply any one the following workaround and if none of the technique resolve the problem then it means that log file is highly corrupted and can only be repaired using a third party tool.

Solution 1: Using Backup of Log (LDF) File

If users create the backup of log file after every transaction, then they can easily retrieve their data from that backup file.

Solution 2: If Event Log Files are Corrupted

If event log files are corrupted, follow the following steps:

  1. Right-Click on the system folder.
  2. From the menu displaying, select on Properties and then click on Clear Log.
  3. A warning box will display that do you want to archive the system log files, click Yes to continue.
  4. Now save the file anywhere on your system.
  5. Now you will be back to properties window, in that click on OK option.
  6. Close and reopen the application window to see the changes

Solution 3: Put Database Online & Offline

If crash recovery function is enabled, then the database in online mode leads to transaction roll back. Follow the following steps:

  1. Put the database in an offline mode.
  2. Clear active log by using checkpoint to delete corrupted log files.
  3. Now again pitch to Online mode and try to access log files.

Solution 4: Recover using DBCC CHECKDB

In order to repair a corrupt LDF file, use the WITH TABLOCK option for DBCC CHECKDB. It will recover the data from a corrupted LDF file that has been corrupted or damaged due to some reason such as logical corruption.

Solution 5: Recover using CHECKDB Command:

For rebuilding log files, we use another technique, i.e. putting database into Emergency mode and then executing CHECKDB command. However, to change the database mode execute following command:

ALTER DATABASE dbname SET EMERGENCY, SINGLE_USER

Similarly, for repairing the log file using CHECKDB command execute the following syntax:

DBCC CHECKDB(‘dbname’ , REPAIR_ALLOW_DATA_LOSS)

Bottom Lines

Here we end up with the fact that manual procedure has a limitation and the technique does not give guarantee the outcome of an exact solution for repairing the log transaction file corrupted in SQL server, it’s just a hit and trial method. However, another drawback is that it is too complex and if something goes wrong then the result may be complete database loss. Therefore, the most reliable and trouble free method is to use SQL Server Transaction Log Recovery Tool, which repair and recover data from corrupted log files. The data integrity is maintained and user can export LDF files into the featured output format for reading the entries without dependency on server.

3/25/2016

Configure SQL Server Database Mirroring: Step By Step

Database Mirroring in SQL Server is deployed as a method to increase the availability of a SQL Server database in the event of a disaster. It can only be implemented in databases that have full recovery model and works on per-database basis. In database mirroring, two copies of a single SQL Server database are stored on different servers that are separated physically. While the Primary Server provides database to the clients, the Mirror Server acts as a standby server that takes over the place of physical server in case any accident occurs.

In this write-up, we will discuss the procedure to configure SQL Server Database Mirroring

Procedure to Configure Database Mirroring in SQL Server

The procedure of configuring SQL Server Database Mirroring is mainly divided into two sections:

I. Preparation of server instances to take part in Database Mirroring in SQL Server

For every database mirroring session, following requirements are need to be fulfilled:

  1. Separate server instances, residing on separate host systems, should host the primary server, the mirror server, and the witness server.
  2. All the server instances need a database mirroring endpoint. If you need to do so, make sure that the endpoint is accessible by other server instances as well.
  3. Two types of transport security exist for database mirroring:
    • Windows Authentication
    • Certificate-based Authentication
  4. The network access requirement depends on the form of authentication that is being used:
    • If Windows Authentication Is Used
    • In case the server instances run in different domain user accounts, all the accounts require a login into the master database of the other accounts. In case the login does not exist, the users must create it.

    • Is Certificates Are Used
    • For enabling certificate authentication on a server instance, the administrator must configure all the server instances to use both outbound and inbound connections certificates. It is to be noted that outbound server needs to be configures before inbound connections.

  5. Make sure that the logon for the all the database exists on the mirror server.

II. Establishing Database Mirroring in SQL Server

Pre-requisites:

  1. Restore all the below-mentioned backups for creating a mirror database by RESTORE WITH NORECOVERY command on all the restore operations:
    • Restore the recent full database backup of principal server. Make sure that the principal server used full recovery model at the time of the backup. Moreover, the name of the mirror database must be same as the primary server.
    • In case any differential backup of the data has been taken after the restoration of full backup, restore the recent differential backup.
    • Do the restoration of all the log backups done since the differential or full backup of the database.
  2. Mirroring can be setup by either using Windows Authentication i.e. Transact-SQL or by Database Mirroring Wizard i.e. SQL Server Management Studio. Here, we are going to use SQL Server Management Studio.

Procedure for Configuring SQL Server Database Mirroring Via SQL Server Management Studio

Follow the below-mentioned steps for establishing database mirroring in SQL Server:

  1. Open SQL Server Management Studio.
  2. Connect to the Primary server.
  3. Click on View option and select Object Explorer option from the drop-down menu.
  4. Click on the Server Name and expand the server tree.
  5. Click on Databases and expand it.
  6. Select the database that has to be mirrored.
  7. Right click on the database and select Tasks option.
  8. Click on Mirror option. Database Properties dialog box with Mirroring page will open.
  9. Click on Configure Security option to start the configuration.
  10. The Configure Security option will automatically create the database mirroring endpoint on all the server instance and will enter the server network address.
  11. Make sure that the below-mentioned conditions exist.
    • You are connected to principal server instance.
    • Security is configured in a correct manner.
    • The TCP address of the primary and mirror servers are specified.
    • If the operating mode is High Safety with automatic failover, then make sure the TCP address of the witness server is also specified.
  12. Click on Start Monitoring to start the process.
  13. In case you want to change the operating mode, you can do so and save the changes by Ok.

Conclusion

With the help of the above-mentioned procedure, you can easily configure SQL Server Database Mirroring.

1/20/2016

SQL Server Community Activities under Surat User Group

I am very pleased to announce that I will be speaking about SQL Server Storage Structure, Level 100 session on 23rd Jan 2016 at 5 PM.  


Well, this will be the first appearance as a speaker after 2012, since this is on-demand session there will be limited number of audience of 10~20 but I will be very happy to speak.  There will be some announcement to be made after the discussion of this UG meet and I will wrote another post and make those announcement public.

The Venue will be:

Inkey Solutions
406, Empire State Building, Ring Road, Nr Udhna Darwaja

1/12/2016

Blog Roll - List of blog post and script contribution

Greetings of the day!!


I am getting frequent emails with questions why I do not write as often I used to write earlier. I know, I got very few blog posts since 2013; the reason was that I had some personal commitment that kept me busy to take time off and make a blog post. However I tried to keep the moment by posting couple of blogs every year but that's on the another blog space - my employers blog space at http://www.pythian.com/blog/author/goswami/ . I have also contributed some scripts to Technet Script Gallery.  Below is the list of the blog post I made last year and the Script Gallery.

I am determined to post at least couple of entry each month now, I would appreciate your support as always!!


Links to the blog posts
Date Title URL
Jan 5 2016 SQL Server 2016 – AlwaysOn Basic Availability Group http://www.pythian.com/blog/alwayson-basic-availability-group-sql-server-2016/
July 31 2015 SQL Server and OS Error 1117, Error 9001, Error 823 http://www.pythian.com/blog/sql-server-and-os-error-1117-error-9001-error-823/
July 9 2015 Reading System Logs on SQL Server http://www.pythian.com/blog/reading-system-logs-on-sql-server/
July 20 2015 Reading System Logs on SQL Server - Part 2 http://www.pythian.com/blog/reading-system-logs-sql-server-part-2/
Sep 30 2015 Import / Export Multiple SSIS Packages http://www.pythian.com/blog/importexport-multiple-ssis-packages/
July 28 2014 Unexpected Shutdown caused by ASR http://www.pythian.com/blog/unexpected-shutdown-caused-by-asr/
Jan 23 2014 Script to Collect Database Information Quickly http://www.pythian.com/blog/script-to-collect-database-information-quickly/


Links to the Technet script Gallery

Date Title URL
Nov 22 2015 Script to Collect ALL Database Information wtih VLF Count https://gallery.technet.microsoft.com/Script-to-Collect-ALL-82664699
May 3 2013 Collect Cluster Information using TSQL https://gallery.technet.microsoft.com/scriptcenter/COLLECT-CLUSTER-INFORMATION-9a75e4a7
Mar 9 2013 Configure Auto Growth in Fixed MB https://gallery.technet.microsoft.com/scriptcenter/Configure-AutoGrowth-in-f4f3d7d1
Jun 26 2015 Script to Monitor Database Mirroring Health https://gallery.technet.microsoft.com/scriptcenter/Script-to-monitor-database-0f35c5d7
Jun 26 2015 Script to Monitor AlwaysOn Health https://gallery.technet.microsoft.com/scriptcenter/TSQL-for-AlwaysOn-Health-6aae827d

8/03/2015

SQL Server GEEKS Annual summit 2015

BannerSSGS_200x200px

Last month, I have requested my manager for an approval so that I can attend SQL Server Geeks Annual Summit 2015,  and now  when I hear back from him, I am really very excited that I will be attending.

You must be wondering what is so exciting about this Summit ? Well, let me tell you, there are number of reasons, at least for me to be that excited. 

  • In past  (even this year), when I had a chance I could not attended PASS Summit. And I always use to think why something like this is not organizing in INDIA. Well done Amit Bansal ( Blog | Twitter ) and team. You have done this, and, like other folks, I would be thankful.
  • I would be able to meet friends whom I never met in-person despite of knowing them for so long time, and to some I will be meeting after several years. For example Satya Shyam K Jayanti ( Blog | Twitter ), Amit Bansal ( Blog | Twitter ) , Prashant Kumar ( Blog | Twitter ) and probably Denny Cherry ( Blog | Twitter )  as I had few email exchange while I was authoring my book on SQL Server High Availability for Beginners.
  • I have always believed in learning, if you believe me learning is never ending process, if we want to make progress  we have to keep learning; SQLServerGeek’s Annual Summit will provide that platform to learn something new, something exciting, for instance BI, Cloud and Big Data. Learning these technologies from experts will be an advantage.

There are some personal reasons as well for me to attend this event, as I already mentioned – to meet few friends in-person, and to make few more friends. I have been knowing few of these friends while I was aspiring DBA, when I was MVP and when I was being transformed as a professional.

I have been knowing Satya Shyam K Jayanti when I was aspiring DBA, I learned many things from him. Amit Bansal, I know him since 2007 when I was an MVP, though we didn’t meet each other personally we have had many occasion to talk over phone,  email and chat. Prashant Kumar is my colleague and friend, we’ve already met in-person and we talk to each other often as he is working as technical escalation, one level up then me. I will also meet Pinal Dave ( Blog | Twitter ) if he is in city, he don’t need introduction in SQL Server community, he is like brother to me.   There are others whom I have read and it would be fun and exciting to meet if time permits, they are Benjamin Nevarez, Sarabpreet Singh and Manohar Punna.

I would like to thank them all and wish belated Guru Purnima, all as I have learned things from them all. I have always admired dedication and zest that Pinal and Amit showed for community, I wish someday I will be able to match that level of energy and dedication. Sorry I missed to name few, if I write about them all this list and post will not end.  

In last, I would like to thank Amit once again for organizing this event and providing a platform to learn and socialize with alike minded community people. I would also like to thank my manager – John Eisbrener and CEO – Paul Vallee  and my company Pythian for sponsoring me so that I can enjoy the company of alike minded geeks and learn something new.

See you there at #SSGAS2015!!!!

11/09/2013

SSAS– Resolving target machine actively refused

medium_5462764117

I always wanted to have my hands on SSAS, SSIS and SSRS as I always feel short-hand in these areas. Yesterday I decided to get my hands dirty with one of this – SSAS.  Few month back I have created a virtual lab on my 4 year old Dell Laptop with 3 VMs and about total 4 instances of SQL Server – 2 on Host machine and 2 on Guest, one of them is having SSAS installed on one of the named instance.

With lot of excitement I tried connecting to SSAS – it was unsuccessful attempt!! It shows me an error message which says - A connection cannot be made to redirector. Ensure that 'SQL Browser' service is running. (Microsoft.AnalysisServices.AdomdClient)

SSAS_Error

As we always do – Googled and it lead me to the connect item filed by Aaron Bertrand ( Blog | Twitter ). Reading through the post I came to know that if I try to connect to the named instance SSAS it’s chance that I get this very error. I also have the similar case, I have renamed my machine hence this error showed up with the named instance.

Workaround:  As suggested in the comment section, I have granted NTFS permission on folder program files (x86)\microsoft sql server\90\shared\asconfig for the SQL Browser service account and I am sorted!!

Reference: Microsoft Connect ID 679792 

photo credit: JLaw45 via photopin cc

—Hemantgiri S. Goswami (http://www.sql-server-citation.com)

11/03/2013

SQL Server–Security Risk Assessment PMP Way

clip_image001Recently I had a chance to speak my friend Ashish Sharma who is my colleague too. We were discussing Project Management topics (he recently passed PMP and ACP). Our discussion ended at very interesting topic – RISK Assessment.
Definition: According to PMBOK, Projects can have unforeseen event or activity that can impact the project’s progress, result or an outcome in negative or positive way. Further, A Risk can be assessed using two factors – impact and probability. Determining the quantitative or qualitative value of risk related to a concrete situation and recognized threats is known as Risk Assessment.
So, coming to the point, how do we do Risk Assessment for Database systems, especially SQL Server ?
I myself have never come to situation where I have created or used Risk Assessment Register for SQL Server. I was thinking is there some readily available Risk Assessment Register / document somewhere? Hence, I have decided to research little more on it. I found absolutely nothing on this very topic. However, searching for different term - SQL Server security, and Risk Assessment – none found.
After spending some time researching I have found a good sample document for Risk Assessment Register that we can use, you can download it from here.
Now, this is what we have is Risk Register, how about threats? Sometime back, in 2009 I have wrote an article on SQL Server Security, using that article I have tried to pen down some threats that I can think off at this moment, they are:
Possible Threats Risk Mitigation
Virus Attack Install AV on DB Server however do not forget to exclude SQL server files
Unauthorised Logins Always use Audit (C2, Windows, SQL or both) failed login attempts and analyze them regularly. Always, rename SA / Admin account and use strong password mechanism for all the servers. If possible always try to use domain authenticated acconts.
MiM Attack To metigate MiM attach do following :
  • Isolate database server from Application / IIS Server
  • Configure SQL Server to use non-default port
  • Use Encryption / SSL
  • Use Firewall inbound / outbound rules
Root Access To avoid someone getting root access do following:
  • Disable / Rename in-built Admin account
  • Grant least access to Service Account
  • Use strong / random password
SQL Injection Do following:
  • Avoid using Dynamic SQL when you can
  • use parameterized query / stored procedure 3) Always validate the value at form level before it's being passed to database server
  • Refer above items
Known Configuration Always try to customize default configuration if you can, for example:
  • Run SQL server on non standard port
  • disable / rename SA account
  • Disable xp_cmdshell and other extended procedures

References :
http://en.wikipedia.org/wiki/Risk_assessment
http://en.wikipedia.org/wiki/Project_risk_management
http://www.brighthubpm.com/risk-management/3247-creating-a-risk-register-a-free-excel-template/
photo credit: kenteegardin via photopin cc
-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

10/21/2013

MSDB– cleanup is necessary

Recently I have been asked to work on the task which is to reduce the size of MSDB database. The MSDB has grown to 20 Gigs. Well, you would say, what is the big deal with that? 20 gigs is not considered a big for database. Correct, but for MSDB, yes it is.
So, the question is - why the size has grown this much, and what would be the adverse impact it would have on the performance on my system?
I would say, there could me many reasons, like:
  • Usually, we do not create user objects inside MSDB, but it is good to check
  • Check if there are multiple SSIS/DTS Packages that are large, check with development team if you can store them in file system. Check the link for the list of tables refer links SQL Server 2005, SQL Server 2008 , SQL Server 2008 R2 , and SQL Server 2012
  • There isn’t any CleanUp job configured
  • There are several hundreds of jobs running i.e. LS aka Log Shipping
  • And, so on….
The case:
Client has a server configured with LS for DR purpose. The LS is configured to sync every 15 minutes for several – hundreds of databases which intern inserting lots of data into the historical tables like backup, restore and log_shipping_monitor_history table –all of them had > 75 Lacks of records.
The issue for us was, that the MSDB is configured on the local drive aka where OS and binaries resides – no RAID. Also, the size of the C drive is nearing to it’s capacity – 30 Gigs and it’s quickly filling up. Well, on top of this, the database is in FULL recovery model. The reason that MSDB grows to 20 Gigs are
  1. It never had CleanUp job on it,
  2. there are hundreds of databases keep inserting records for backup and restore
Adverse impact:
  1. Possibly, your backup would take longer than usual as it would take time to write backup and /or restore history
  2. You would see timeout error when you try to dig out the reason for backup job failure
What I did was, I have created a maintenance plan for CleanUp which will call sp_delete_backuphistory which will run cleanup for below tables, per client’s request I have configured job to remove all the older data before 60 days.
To complete the cleanup activity job successfully, the MSDB and tempdb will need some space to grow which is not possible in our case since we are left with only 10 Gigs of space.  Hence, I have added an addition log file and data file for tempdb and msdb on another drive where we have ample space. Schedule LOG backup for MSDB to run every 10 minutes.
And, then, I’ve invoked the CleanUp job – it took about 3 hours to finish, but it did what it should.
Took FULL backup for MSDB, change the recovery model to simple and shrink it – we were able to shrunk the MSDB successfully and bring it down to 6.5 Gigs.
Constraints and possible options:
  1. We had a limited maintenance window to accomplish a task
  2. Another maintenance activity has to be performed once we are done
  3. Option: We could have script foreign keys and other constraint, drop constraints and keys, and delete the records. I haven’t opt this method because I personally never did this.
Take out from this post:  Do health check, and, schedule a Cleanup task for MSDB to run on regular basis.

-- Hemantgiri Goswami (http://www.sql-server-citation.com/)



photo credit: mccun934 via photopin cc

9/06/2013

Not so good news for MCM Aspirants

Few days back, late Friday (30th August 2013) MS has made an announcement – that they are cancelling the MCM Certification, not so good news for MCM Aspirants. This announcement has upset many in the SQL Community including me.

I have my own reasons, and belief about not attending any of the certification exam until few month back. In my 14+ years of career not even a single employer of mine have asked me to go and get certified. I can say, I am lucky, but all I know that I have worked hard – a lot. I have tried to learn a lot, from friends, from colleagues, from online web forums, books online, books and MS Evangelists, and still learning. And, that pays me well, I participate in community activities, speaks, blog and have been able to put my thoughts together to publish a book on SQL Server Cluster – this way I am surviving as a professional.

To be honest, during my career I have interviewed a lot many people and many of them are certified but they don’t have exposure and practical troubleshooting skills which was expected; apart from this, there are dumps available that can made it easy to clear an exam. Please, Neither I am not criticizing those who has passed nor I am saying all who haven’t passed aren’t good as professional, but I am just trying to make a point why I haven’t attempted one. With this kind of experience it never interest me to pass the certification.

And, then, MCM was introduced. I have heard a lot about Oracle exams which has lab test as well and MCM is similar kind of exams. Brent Ozar ( Blog | Twitter ) blogged about What is MCM in 2010. I have followed all his posts about MCM and has developed my interest in attending MCM training followed by an exam. Event though the exam and training fees are way too high for me, I have made my mind that I will utilize the money my employer provide each of us for training + some from my pocket to get train and earn this title – but, sadly, it is gone now.  Since MCITP is a pre-requisite for MCM, I have made my mind, attempted and failed. However, I was sure, I was reading more and will clear it in next attempt. But, I am not sure, whether I will be taking an MCITP now. Sad smile

I will be more than happiest person, if this decision for MCM exam are reversed or some relevant / at par exams are introduced. In our country, it is said that “whatever happened has happened for the good, and whatever that is going to be happen in future will be for good”.  I am hoping that this saying will hold true.

At the end, I would like to say, if you are believer and aspirant of this exam just like me please take some time and vote on the MS Connect site for reconsideration.

Thank you

Hemantgiri S. Goswami (http://www.sql-server-citation.com)