tag:blogger.com,1999:blog-41095947017162935092024-03-14T17:38:08.089+05:30All about MS SQL Server And Hadoop AdministratorIn this blog, we'll discuss the issues along with the features of SQL Server. Here I'll share my real world experiences about MS SQL Server as well as my learning experience about Hadoop Administrator. Apart from all this, a bit about my personal life and little financial tips.Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.comBlogger35125tag:blogger.com,1999:blog-4109594701716293509.post-29318491973578499902017-03-14T13:27:00.000+05:302017-03-14T13:28:19.020+05:30SQL Server SSL Connectivity Issue<div dir="ltr" style="text-align: left;" trbidi="on">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><br />
<!--[if gte mso 9]><xml>
<o:OfficeDocumentSettings>
<o:AllowPNG/>
</o:OfficeDocumentSettings>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:WordDocument>
<w:View>Normal</w:View>
<w:Zoom>0</w:Zoom>
<w:TrackMoves>false</w:TrackMoves>
<w:TrackFormatting/>
<w:PunctuationKerning/>
<w:ValidateAgainstSchemas/>
<w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid>
<w:IgnoreMixedContent>false</w:IgnoreMixedContent>
<w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText>
<w:DoNotPromoteQF/>
<w:LidThemeOther>EN-US</w:LidThemeOther>
<w:LidThemeAsian>X-NONE</w:LidThemeAsian>
<w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript>
<w:Compatibility>
<w:BreakWrappedTables/>
<w:SnapToGridInCell/>
<w:WrapTextWithPunct/>
<w:UseAsianBreakRules/>
<w:DontGrowAutofit/>
<w:SplitPgBreakAndParaMark/>
<w:EnableOpenTypeKerning/>
<w:DontFlipMirrorIndents/>
<w:OverrideTableStyleHps/>
</w:Compatibility>
<m:mathPr>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="--"/>
<m:smallFrac m:val="off"/>
<m:dispDef/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>
</m:mathPr></w:WordDocument>
</xml><![endif]--><!--[if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="false"
DefSemiHidden="false" DefQFormat="false" DefPriority="99"
LatentStyleCount="371">
<w:LsdException Locked="false" Priority="0" QFormat="true" Name="Normal"/>
<w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 1"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 2"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 3"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 4"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 5"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 6"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 7"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 8"/>
<w:LsdException Locked="false" Priority="9" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="heading 9"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 6"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 7"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 8"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index 9"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 1"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 2"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 3"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 4"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 5"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 6"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 7"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 8"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" Name="toc 9"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Normal Indent"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="footnote text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="annotation text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="header"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="footer"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="index heading"/>
<w:LsdException Locked="false" Priority="35" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="caption"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="table of figures"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="envelope address"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="envelope return"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="footnote reference"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="annotation reference"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="line number"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="page number"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="endnote reference"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="endnote text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="table of authorities"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="macro"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="toa heading"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Bullet 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Number 5"/>
<w:LsdException Locked="false" Priority="10" QFormat="true" Name="Title"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Closing"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Signature"/>
<w:LsdException Locked="false" Priority="1" SemiHidden="true"
UnhideWhenUsed="true" Name="Default Paragraph Font"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text Indent"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="List Continue 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Message Header"/>
<w:LsdException Locked="false" Priority="11" QFormat="true" Name="Subtitle"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Salutation"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Date"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text First Indent"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text First Indent 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Note Heading"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text Indent 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Body Text Indent 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Block Text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Hyperlink"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="FollowedHyperlink"/>
<w:LsdException Locked="false" Priority="22" QFormat="true" Name="Strong"/>
<w:LsdException Locked="false" Priority="20" QFormat="true" Name="Emphasis"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Document Map"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Plain Text"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="E-mail Signature"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Top of Form"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Bottom of Form"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Normal (Web)"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Acronym"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Address"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Cite"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Code"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Definition"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Keyboard"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Preformatted"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Sample"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Typewriter"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="HTML Variable"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Normal Table"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="annotation subject"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="No List"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Outline List 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Outline List 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Outline List 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Simple 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Simple 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Simple 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Classic 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Classic 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Classic 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Classic 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Colorful 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Colorful 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Colorful 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Columns 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 6"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 7"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Grid 8"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 4"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 5"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 6"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 7"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table List 8"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table 3D effects 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table 3D effects 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table 3D effects 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Contemporary"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Elegant"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Professional"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Subtle 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Subtle 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Web 1"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Web 2"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Web 3"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Balloon Text"/>
<w:LsdException Locked="false" Priority="39" Name="Table Grid"/>
<w:LsdException Locked="false" SemiHidden="true" UnhideWhenUsed="true"
Name="Table Theme"/>
<w:LsdException Locked="false" SemiHidden="true" Name="Placeholder Text"/>
<w:LsdException Locked="false" Priority="1" QFormat="true" Name="No Spacing"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading"/>
<w:LsdException Locked="false" Priority="61" Name="Light List"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 1"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 1"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 1"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 1"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 1"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 1"/>
<w:LsdException Locked="false" SemiHidden="true" Name="Revision"/>
<w:LsdException Locked="false" Priority="34" QFormat="true"
Name="List Paragraph"/>
<w:LsdException Locked="false" Priority="29" QFormat="true" Name="Quote"/>
<w:LsdException Locked="false" Priority="30" QFormat="true"
Name="Intense Quote"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 1"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 1"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 1"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 1"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 1"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 1"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 1"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 1"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 2"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 2"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 2"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 2"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 2"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 2"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 2"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 2"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 2"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 2"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 2"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 2"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 2"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 2"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 3"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 3"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 3"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 3"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 3"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 3"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 3"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 3"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 3"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 3"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 3"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 3"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 3"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 3"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 4"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 4"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 4"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 4"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 4"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 4"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 4"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 4"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 4"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 4"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 4"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 4"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 4"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 4"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 5"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 5"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 5"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 5"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 5"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 5"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 5"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 5"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 5"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 5"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 5"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 5"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 5"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 5"/>
<w:LsdException Locked="false" Priority="60" Name="Light Shading Accent 6"/>
<w:LsdException Locked="false" Priority="61" Name="Light List Accent 6"/>
<w:LsdException Locked="false" Priority="62" Name="Light Grid Accent 6"/>
<w:LsdException Locked="false" Priority="63" Name="Medium Shading 1 Accent 6"/>
<w:LsdException Locked="false" Priority="64" Name="Medium Shading 2 Accent 6"/>
<w:LsdException Locked="false" Priority="65" Name="Medium List 1 Accent 6"/>
<w:LsdException Locked="false" Priority="66" Name="Medium List 2 Accent 6"/>
<w:LsdException Locked="false" Priority="67" Name="Medium Grid 1 Accent 6"/>
<w:LsdException Locked="false" Priority="68" Name="Medium Grid 2 Accent 6"/>
<w:LsdException Locked="false" Priority="69" Name="Medium Grid 3 Accent 6"/>
<w:LsdException Locked="false" Priority="70" Name="Dark List Accent 6"/>
<w:LsdException Locked="false" Priority="71" Name="Colorful Shading Accent 6"/>
<w:LsdException Locked="false" Priority="72" Name="Colorful List Accent 6"/>
<w:LsdException Locked="false" Priority="73" Name="Colorful Grid Accent 6"/>
<w:LsdException Locked="false" Priority="19" QFormat="true"
Name="Subtle Emphasis"/>
<w:LsdException Locked="false" Priority="21" QFormat="true"
Name="Intense Emphasis"/>
<w:LsdException Locked="false" Priority="31" QFormat="true"
Name="Subtle Reference"/>
<w:LsdException Locked="false" Priority="32" QFormat="true"
Name="Intense Reference"/>
<w:LsdException Locked="false" Priority="33" QFormat="true" Name="Book Title"/>
<w:LsdException Locked="false" Priority="37" SemiHidden="true"
UnhideWhenUsed="true" Name="Bibliography"/>
<w:LsdException Locked="false" Priority="39" SemiHidden="true"
UnhideWhenUsed="true" QFormat="true" Name="TOC Heading"/>
<w:LsdException Locked="false" Priority="41" Name="Plain Table 1"/>
<w:LsdException Locked="false" Priority="42" Name="Plain Table 2"/>
<w:LsdException Locked="false" Priority="43" Name="Plain Table 3"/>
<w:LsdException Locked="false" Priority="44" Name="Plain Table 4"/>
<w:LsdException Locked="false" Priority="45" Name="Plain Table 5"/>
<w:LsdException Locked="false" Priority="40" Name="Grid Table Light"/>
<w:LsdException Locked="false" Priority="46" Name="Grid Table 1 Light"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark"/>
<w:LsdException Locked="false" Priority="51" Name="Grid Table 6 Colorful"/>
<w:LsdException Locked="false" Priority="52" Name="Grid Table 7 Colorful"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 1"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 1"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 1"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 1"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 1"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 2"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 2"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 2"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 2"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 2"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 3"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 3"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 3"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 3"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 3"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 4"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 4"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 4"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 4"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 4"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 5"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 5"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 5"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 5"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 5"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="46"
Name="Grid Table 1 Light Accent 6"/>
<w:LsdException Locked="false" Priority="47" Name="Grid Table 2 Accent 6"/>
<w:LsdException Locked="false" Priority="48" Name="Grid Table 3 Accent 6"/>
<w:LsdException Locked="false" Priority="49" Name="Grid Table 4 Accent 6"/>
<w:LsdException Locked="false" Priority="50" Name="Grid Table 5 Dark Accent 6"/>
<w:LsdException Locked="false" Priority="51"
Name="Grid Table 6 Colorful Accent 6"/>
<w:LsdException Locked="false" Priority="52"
Name="Grid Table 7 Colorful Accent 6"/>
<w:LsdException Locked="false" Priority="46" Name="List Table 1 Light"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark"/>
<w:LsdException Locked="false" Priority="51" Name="List Table 6 Colorful"/>
<w:LsdException Locked="false" Priority="52" Name="List Table 7 Colorful"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 1"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 1"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 1"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 1"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 1"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 1"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 2"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 2"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 2"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 2"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 2"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 2"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 3"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 3"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 3"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 3"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 3"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 3"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 4"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 4"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 4"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 4"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 4"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 4"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 5"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 5"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 5"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 5"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 5"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 5"/>
<w:LsdException Locked="false" Priority="46"
Name="List Table 1 Light Accent 6"/>
<w:LsdException Locked="false" Priority="47" Name="List Table 2 Accent 6"/>
<w:LsdException Locked="false" Priority="48" Name="List Table 3 Accent 6"/>
<w:LsdException Locked="false" Priority="49" Name="List Table 4 Accent 6"/>
<w:LsdException Locked="false" Priority="50" Name="List Table 5 Dark Accent 6"/>
<w:LsdException Locked="false" Priority="51"
Name="List Table 6 Colorful Accent 6"/>
<w:LsdException Locked="false" Priority="52"
Name="List Table 7 Colorful Accent 6"/>
</w:LatentStyles>
</xml><![endif]--><!--[if gte mso 10]>
<style>
/* Style Definitions */
table.MsoNormalTable
{mso-style-name:"Table Normal";
mso-tstyle-rowband-size:0;
mso-tstyle-colband-size:0;
mso-style-noshow:yes;
mso-style-priority:99;
mso-style-parent:"";
mso-padding-alt:0in 5.4pt 0in 5.4pt;
mso-para-margin-top:0in;
mso-para-margin-right:0in;
mso-para-margin-bottom:8.0pt;
mso-para-margin-left:0in;
line-height:107%;
mso-pagination:widow-orphan;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-ascii-font-family:Calibri;
mso-ascii-theme-font:minor-latin;
mso-hansi-font-family:Calibri;
mso-hansi-theme-font:minor-latin;}
</style>
<![endif]-->
<br />
<div class="MsoNormal">
I'm sure most of you may face different types of error while connecting
to SQL Server via Management Studio or ODBC (or different methods). Exactly same
thing happened in my client side when we encountered with the following:</div>
<div class="MsoNormal">
<br /></div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVi-szF1EjR1fapGsHkqlcA8HIb382YPUEnIu-6PQ-fd0nLzRFrYveRn7l0qGSSHrXBN7KYYqE4hBYsEorhGAhbkLu3oVczYPrCHF2Dr2eXH4ynvMoAA7TtJhsPEmGm_XRWYNWFLV6jWg/s1600/Error.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="SSL Security Error" border="0" height="188" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVi-szF1EjR1fapGsHkqlcA8HIb382YPUEnIu-6PQ-fd0nLzRFrYveRn7l0qGSSHrXBN7KYYqE4hBYsEorhGAhbkLu3oVczYPrCHF2Dr2eXH4ynvMoAA7TtJhsPEmGm_XRWYNWFLV6jWg/s320/Error.png" title="SSL Security Error" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>SSL Security Error</b></td></tr>
</tbody></table>
<div class="MsoNormal">
<span style="mso-no-proof: yes;"> </span></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;"><u>Problem
Description</u></b>:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
After monthly scheduled restart of one Application Server,
the connectivity to Database Server from .Net Client was unable to establish
connection to our Database Server. In addition, tried from connecting through ODBC
that shows the error message which I mentioned above.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;">Application Server</b>:
Windows Server 2012 R2</div>
<div class="MsoNormal">
<b style="mso-bidi-font-weight: normal;">Database Server</b>:
SQL Server 2005</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<b><span style="mso-bidi-font-weight: normal;"><u>Troubleshooting</u></span><u>
/ <span style="mso-bidi-font-weight: normal;">Solution</span></u></b>:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
As the errors states "<b>SSL Security error</b>", it was pointing
to something in the registry level encryption. After hours of troubleshooting
(almost a day) and visiting 50+ blogs, we were able to solve this connectivity issue. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<u>Some observations while troubleshooting are as follows</u>:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span> <b>a</b>. This <b>problem was specific to SQL Server 2005
version only</b> because we tried the connectivity from Application Server to
different version of SQL Server like 2008 and 2012 that was successfully able
to connect. </div>
<div class="MsoListParagraphCxSpFirst" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<b>b</b>. TLS 1.0 in registry was not found. You can check
here for this error in detail <span style="color: blue;"><b><a href="https://support.microsoft.com/en-us/help/3135244/tls-1.2-support-for-microsoft-sql-server" target="_blank">here</a></b></span>. </div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="mso-list: Ignore;"> <b>c</b>. <span style="font: 7.0pt "Times New Roman";"></span></span></span>No traces found in Windows Event Viewer as well
as SQL Server Error Log.</div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="mso-list: Ignore;"> <b> d</b>.<span style="font: 7.0pt "Times New Roman";"> </span></span></span>Tried playing with the dbnetlib.dll files too (May
be for some this solution has worked).</div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<br /></div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="mso-list: Ignore;"> <b>e</b>. <span style="font: 7.0pt "Times New Roman";"></span></span></span>Installed SQL Server 2005 specific drives and
tried connecting through ODBC.</div>
<div class="MsoListParagraphCxSpMiddle" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
</div>
<div class="MsoListParagraphCxSpLast" style="mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="mso-bidi-font-family: Calibri; mso-bidi-theme-font: minor-latin;"><span style="mso-list: Ignore;"><span style="font: 7.0pt "Times New Roman";"></span></span></span> <b>f</b>. Raised this concerned with Microsoft. Since also
the extended support for SQL Server 2005 ended last year on April 2016,
Microsoft denied to support.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Last week there was some patching at Windows level. Due to this,
two new entries (Triple DES & AES) were found in <b style="mso-bidi-font-weight: normal;">Ciphers </b>on the below location:</div>
<div class="MsoNormal">
<br /></div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoqw90yRgqCbKaBqAY-vI2B-SuobuK8i_n1g-z6WqiFESdjjCvNJwOxnE0Y5g9i365Jrn3WKvNhWhvlymhmysNXfL2mFdf-GoYfx5Bf3BpkKQ70XGLhyphenhyphenjVAoPvULpD25TaBxjMfm02-HI/s1600/Error1.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoqw90yRgqCbKaBqAY-vI2B-SuobuK8i_n1g-z6WqiFESdjjCvNJwOxnE0Y5g9i365Jrn3WKvNhWhvlymhmysNXfL2mFdf-GoYfx5Bf3BpkKQ70XGLhyphenhyphenjVAoPvULpD25TaBxjMfm02-HI/s1600/Error1.png" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Registry</b></td></tr>
</tbody></table>
<div class="MsoNormal">
After this discovery, we checked the connectivity by
disabling both this algorithms. However, the issue still existed. Finally, tried
the connectivity by deleting these algorithms and this time we were lucky and we were able to establish the connectivity to SQL Server 2005 again.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Thank you for reading. You can leave the feedback in the below comments space and stay tuned for more articles on SQL Server.</div>
<div class="MsoNormal">
<br /></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-63322309175491761922016-02-04T14:01:00.000+05:302016-02-04T14:01:36.913+05:30Edition Upgrade in SQL Server<div dir="ltr" style="text-align: left;" trbidi="on">
Hi Friends,<br />
<br />
<b><a href="http://vikasbsahu.blogspot.nl/2015/12/introduction-to-hadoop.html" target="_blank"><span style="color: blue;">Check here</span></a></b> for my last post of year 2015 on Hadoop. By this, lets start with this post on "<b>How to perform in-place Edition upgrade</b>". This procedure will work well with SQL Server 2005 and above.<br />
<br />
<b><u>Requirement</u></b>:<br />
<br />
There was a requirement from the client to upgrade the Edition from Enterprise to Standard for one of my SQL Server 2008 R2 instance.<br />
<br />
So lets see how simple and time efficient is the in place upgradation:<br />
<br />
<b><u>Step 1</u></b>: Launch the SQL Server setup file => Go to "<b>Maintenance</b>" tab on left hand side.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_CxVEpZ5ozg97V3wXTJc-zRo9StmnWT0uExS9tsz8gvUDwd7BP4Na9cMMBayB3CFWKtYlpLyS_npVhie2h0W0j31gG5YjcXvXqHYBd7e_TxwsRx2bZJKsXyru4mukDNl-i8hyPyPEox4/s1600/1.PNG" imageanchor="1"><img border="0" height="192" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_CxVEpZ5ozg97V3wXTJc-zRo9StmnWT0uExS9tsz8gvUDwd7BP4Na9cMMBayB3CFWKtYlpLyS_npVhie2h0W0j31gG5YjcXvXqHYBd7e_TxwsRx2bZJKsXyru4mukDNl-i8hyPyPEox4/s640/1.PNG" width="640" /></a><br />
<br />
<b><u>Step 2</u></b>: Click "Edition Upgrade" link on Right hand side. This will check first check the rules.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1uS2eGLSR5nJ9NHoDDaYJ7myR9ZWT42OM0OJUB6J-K39wuOYaAEbguN1KwxEPQmmDieBNKVzR1aYKE8q8uHBSg7P1ze6SlAWumJHFVI0dHfEqQaZcBfGgh_fPBV1lFoceN4Lkg8XUXXM/s1600/2.PNG" imageanchor="1"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj1uS2eGLSR5nJ9NHoDDaYJ7myR9ZWT42OM0OJUB6J-K39wuOYaAEbguN1KwxEPQmmDieBNKVzR1aYKE8q8uHBSg7P1ze6SlAWumJHFVI0dHfEqQaZcBfGgh_fPBV1lFoceN4Lkg8XUXXM/s640/2.PNG" width="640" /></a><br />
<br />
(Next)<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiend6oYBPAfSW4CqfasoEy1a3IS0UuQrNkWQ6X8-MQoOtqwh-iZHkXm6IOyq6PFmyXcBBwUKR_aGYb9MY5HwdHHELPKxEX4mYDSIGZxhBvmF5ittMPIf8G5Th3fqxAU197tZcTzE9QhVM/s1600/3.PNG" imageanchor="1"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiend6oYBPAfSW4CqfasoEy1a3IS0UuQrNkWQ6X8-MQoOtqwh-iZHkXm6IOyq6PFmyXcBBwUKR_aGYb9MY5HwdHHELPKxEX4mYDSIGZxhBvmF5ittMPIf8G5Th3fqxAU197tZcTzE9QhVM/s640/3.PNG" width="640" /></a><br />
<br />
(Next)<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIEqkB_GyeVbJJztJ-B3r3OPGjWFNwEqloDP1jl_d5z_nbnf431tPMVofNjzfPiLmfOn67TJ9ba_QyIy-5RCYMmVxxFzZaTyTdXBDy3QK2fRLoCZw4NZmceuE3lphmOvrrzfrAjj_t_wc/s1600/4.PNG" imageanchor="1"><img border="0" height="478" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIEqkB_GyeVbJJztJ-B3r3OPGjWFNwEqloDP1jl_d5z_nbnf431tPMVofNjzfPiLmfOn67TJ9ba_QyIy-5RCYMmVxxFzZaTyTdXBDy3QK2fRLoCZw4NZmceuE3lphmOvrrzfrAjj_t_wc/s640/4.PNG" width="640" /></a><br />
<br />
<b><u>Step 3</u></b>: Very important step check the new edition here (Which I have highlighted below)<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGJ2TomJ4um7agsZKtsb5z-tl-mHAhJswxddykvmIAk7bM1W8L_s1gvFM6Mdv5eW3woEt_HxTawJ38vUOBpI3z_eZrWgfvH51upL6TAD2iWp522l18f3akuWkfY4i0xTNMS2EIFA39Evk/s1600/5.PNG" imageanchor="1"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGJ2TomJ4um7agsZKtsb5z-tl-mHAhJswxddykvmIAk7bM1W8L_s1gvFM6Mdv5eW3woEt_HxTawJ38vUOBpI3z_eZrWgfvH51upL6TAD2iWp522l18f3akuWkfY4i0xTNMS2EIFA39Evk/s640/5.PNG" width="640" /></a><br />
<br />
<b><u>Step 4</u></b>: Select the instance from the drop down for which the instance need to be upgraded.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOULbQUDavpbFCvvlAkDzy404jVQTt1BeGnsf5NIY46a__fxnpv2KiUznSvD2WFycfwVpM-Jzy5P8-3knenZuRESgumqdH6FUIfx3ruqMe7Qd4Kg-eXdl8Fp9DLHsZsYp5mb4WWd2RgAk/s1600/6.PNG" imageanchor="1"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOULbQUDavpbFCvvlAkDzy404jVQTt1BeGnsf5NIY46a__fxnpv2KiUznSvD2WFycfwVpM-Jzy5P8-3knenZuRESgumqdH6FUIfx3ruqMe7Qd4Kg-eXdl8Fp9DLHsZsYp5mb4WWd2RgAk/s640/6.PNG" width="640" /></a><br />
<br />
(Next)<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyHYXR06veVtNRziX6WDqTpKYvFMFEX0YyLmtXhOM2GQ0EVw54Ozs37nhAEtvpfD5sb8vXy6_Xjpv5R6RhsftVLNEmAewifexbb51-ervzaoDAec4aCBDULZQBV6PgL8k1x8U8COc-se8/s1600/7.PNG" imageanchor="1"><img border="0" height="480" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyHYXR06veVtNRziX6WDqTpKYvFMFEX0YyLmtXhOM2GQ0EVw54Ozs37nhAEtvpfD5sb8vXy6_Xjpv5R6RhsftVLNEmAewifexbb51-ervzaoDAec4aCBDULZQBV6PgL8k1x8U8COc-se8/s640/7.PNG" width="640" /></a><br />
<br />
(Next)<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK-ov2khyQx1CXadQGcjPPNJpFmsqysXM2dq0y_uehXRcO-K426i0-onFvZL9lcQWtKYWdvqtfXO-jaOvV1qPePstjg20J_f9cOJM8sDF0u6pXczuDqd09GuJ9fIuMEMtL0tYKe5UEDEo/s1600/8.PNG" imageanchor="1"><img border="0" height="478" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK-ov2khyQx1CXadQGcjPPNJpFmsqysXM2dq0y_uehXRcO-K426i0-onFvZL9lcQWtKYWdvqtfXO-jaOvV1qPePstjg20J_f9cOJM8sDF0u6pXczuDqd09GuJ9fIuMEMtL0tYKe5UEDEo/s640/8.PNG" width="640" /></a><br />
<br />
Hold on for 3 Minitues and your <b style="background-color: yellow;">in place edition upgradation is finished</b>.<br />
<br />
As you have seen how simple and time efficient it is to perform this, it comes with the cost. AND the cost is "if there is any error during the upgradation phase there is no procedure to rollback".<br />
<br />
The only option left with us is complete uninstalltion and again installing the fresh setup. So before starting this procedure make sure you have taken the necessary precaution.<br />
<br />
Have your ever faced <b><a href="http://vikasbsahu.blogspot.nl/2016/01/error-instant-file-initialization-failed.html" target="_blank"><span style="color: blue;">this error</span></a></b>.<br />
<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-6672329549523763602016-01-21T21:35:00.002+05:302016-01-21T21:37:36.519+05:30Error - Instant File Initialization Failed<div dir="ltr" style="text-align: left;" trbidi="on">
Hi Friends,<br />
<br />
Here comes the first post of the wonderful year 2016 ahead. Last year, we have ended with the post on <b><a href="http://vikasbsahu.blogspot.nl/2015/12/introduction-to-hadoop.html" target="_blank"><span style="color: blue;">Introduction to Hadoop</span></a></b>. Lets start our first post with an Error in SQL Server.<br />
<br />
<b><u>Description</u></b>:<br />
<br />
One of the error which I faced very frequently now a days as you can see the below snapshot: "<b>File initialization failed</b>" because of which my Restoration activity was failed.<br />
<br />
This error occurred when I was in the middle of a Migration activity, were I was suppose to Backup and Restore a Database from one Server to another one. After executing the Restoration command with stats=1, I was waiting for 1 percent to complete (after that I can have a nap because the backup file was huge and the activity was at mid night) but I was awaiting awaiting and awaiting for that 1 percent. It was getting suspicious because it should not take too long to complete even a percent.<br />
<br />
So, I decided to stop the restoration. Once the session was stopped I found the below error message:<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrMTn37wCD5YsolRqcRoUzWpE93PwenF3Jl4VNxzeDmDwW4muGbxi10f57gORJ8X-PUFWcBBlgeLCjsN5WzQZd7Igg-wmfpwwrCwwLCx2KOlatELP-aQsliAn_5Ort89ebyQfUw1-uAsg/s1600/2_1.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="Instant File Initialization Failed" border="0" height="142" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrMTn37wCD5YsolRqcRoUzWpE93PwenF3Jl4VNxzeDmDwW4muGbxi10f57gORJ8X-PUFWcBBlgeLCjsN5WzQZd7Igg-wmfpwwrCwwLCx2KOlatELP-aQsliAn_5Ort89ebyQfUw1-uAsg/s640/2_1.PNG" title="Instant File Initialization Failed" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b><br /></b>
<b>Instant File Initialization Failed</b></td></tr>
</tbody></table>
Basically, I will try to explain the behind the scene what exactly SQL Server does then we create or Restore a backup file in different post. In this post just lets look for the solution for the error.<br />
<br />
<b><u>Solution</u>:</b><br />
<br />
1. Run => Secpol.msc;<br />
<br />
Open Local Security Policy => Local Policies => User Rights Assignment => "Perform Volume Maintenance Task" => Right click => Add the user through which SQL Server services are running. Like you see in the below snapshot.<br />
<br />
2. No need to restart the Server.<br />
<br />
3. Now, start the restoration process and this time it will work well.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4LSv5q0Cdb3LwxCWXOr4Y7q8yGTpBhkkWTDJPoUWS23N8cU1v4jD08xTQzGDZY3OyyXd8sKRCUKjmefhx3f9-h6qlxHrYNJnmq2Wm68BknmmgX3kQQy-LdiPGpo4PD_nIYKh-D0lbrTc/s1600/111.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="Perform Volume Maintenance Task" border="0" height="456" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4LSv5q0Cdb3LwxCWXOr4Y7q8yGTpBhkkWTDJPoUWS23N8cU1v4jD08xTQzGDZY3OyyXd8sKRCUKjmefhx3f9-h6qlxHrYNJnmq2Wm68BknmmgX3kQQy-LdiPGpo4PD_nIYKh-D0lbrTc/s640/111.PNG" title="Perform Volume Maintenance Task" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b><br /></b>
<b>Perform Volume Maintenance Task</b></td></tr>
</tbody></table>
<br />
So from now SQL Server will skip the Zero Initialization whenever we create or restore a Database. Later we will see what exactly does this mean in probably in different post.<br />
<br />
Hope this will save your time and this will help you. Don't forget to drop a comment below. Also do vote below if it is Interesting, Informative or Boring.<br />
<br />
Facing trouble while switching the Database from Single User Mode to Multi Mode <b><a href="http://vikasbsahu.blogspot.nl/2015/11/error-while-putting-database-from.html" target="_blank"><span style="color: blue;">check here</span></a></b> for solution.<br />
<br />
(It's been so long, more than couple of weeks I was away from my blog. I'm afraid this could further continue for few more, due to multiple projects on weekdays as well over the weekends. Due to this, my Blogging might also affected. So, stay tuned soon we will learn many things on SQL Server as well as Hadoop Administrator.)<br />
<br />
Thanks,<br />
Vikas B Sahu<br />
Keep Learning and Enjoy Learning!!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-17780511919702965982015-12-15T20:54:00.003+05:302015-12-22T12:23:44.605+05:30Introduction to Hadoop <div dir="ltr" style="text-align: left;" trbidi="on">
Dear Friends,<br />
<br />
Couple of months back I've published a post on SQL Server 2016 new features <b><a href="http://vikasbsahu.blogspot.in/2015/07/new-features-in-sql-server-2016-part-1.html" target="_blank"><span style="color: blue;">here</span></a></b>.<br />
<br />
Meanwhile, let me introduce you to <b><i>Hadoop</i></b>. We will learn this as a series of inter-related post. So don't miss any post in between and read it serially. Let's make it fun and interesting to learn <b><i>Hadoop</i></b>.<br />
<br />
So, lets understand what are the formats of data that we handle in real word.<br />
<ul style="text-align: left;">
<li>Flat File</li>
<li>Rows and Columns</li>
<li>Images and Document</li>
<li>Audio and Video</li>
<li>XML Data and many more......</li>
</ul>
<i><b><span style="color: blue;"><a href="https://en.wikipedia.org/wiki/Big_data" target="_blank"><span style="color: blue;">Big Data</span></a></span></b></i> is an ocean of data which an organization stores. These data come in three V's i.e. Volume, Velocity and Variety.<br />
<br />
Now-a-days <b><i>huge Volume</i></b> of data are getting generated by many sources such as Facebook,Whatsapp, E-commerce sites, etc, etc, etc. These huge volume of data are getting generated with <b><i>high Velocity</i></b>, can say it is multiplying every seconds, every minute, every hour. Along with the huge Volume and high Velocity <b><i>numerous Variety</i></b> of data is generated in different forms.<br />
<br />
These data can be in any format i.e. structure, semi-structure as well as unstructured. Data stored in the form of row and column can be well defined as structured data whereas data in form of document, image, sms, video, audio,etc can be categories into unstructured and data in html or in XML format can be semi-structure data.<br />
<div class="MsoNormal">
<br />
<b>Q. I am sure you must be thinking that then how does a RDBMS handles these kind of unstructured or semi-structure data in there Database?</b><br />
<br />
<b>A.</b> Well, to handle these kind of data's we have special data type such as Varbinary(Max), XML. Drawback of this is, if we are storing an image, it is stored in binary format within the database; whereas actual image is stored in Filestream or the Server itself. Hence there is an performance impact during storing and retrieving Petabyte of data's. <br />
<br />
Moreover to this, Big Data is not just about maintaining and growing the data year on year, but it is also about how you manages
these data's to make an informative decision. Data in Big Data can also comes in various complex format, to manage and process these type of data we need large set of cluster servers.</div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAocWDcExXMJzYqHPDHVU7abQXifUd0UnH-lPAbGAVDoKaEHCI9VzQXMvpU5kPzB6aIBR9XB7mOwrtrcE78i5N1gq0JyB4E-dLWWiEDG56NrsO6ANvXztaJkhtGzjJFoMrciqTUjafBrM/s1600/big_data_hadoop_blog11.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="BIG DATA & HADOOP" border="0" height="86" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiAocWDcExXMJzYqHPDHVU7abQXifUd0UnH-lPAbGAVDoKaEHCI9VzQXMvpU5kPzB6aIBR9XB7mOwrtrcE78i5N1gq0JyB4E-dLWWiEDG56NrsO6ANvXztaJkhtGzjJFoMrciqTUjafBrM/s200/big_data_hadoop_blog11.jpg" title="BIG DATA & HADOOP" width="200" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><br /></td></tr>
</tbody></table>
<b><i>With this introduction to Big Data, now let me introduce you to Hadoop. </i></b><br />
<br />
Hadoop is a large set of cluster servers which is built to process large set of data. It has two main core component i.e. '<b>Hadoop MapReduce</b>' (Processing Part) and '<b>Hadoop Distributed File System</b>' (Storage Part). Hadoop project comes under Apache and that is why it is called as 'Apache Hadoop'. The idea behind these two core component came into existence when <u>Google has released there two white paper of there project on 'MapReduce' and 'Google File System (GFS)</u>' in the year 2004.<br />
<u>Hadoop was created by Doug Cutting in 2005</u>. Cutting, He was working at Yahoo! at the time he build the software, it was named after his son's toy elephant.<br />
<br />
Wikipedia defines Hadoop as "<i>an open-source software framework written in Java for distributed storage and distributed processing of very large data sets on computer clusters built from commodity hardware</i>"<br />
<br />
Hadoop is an open source framework available in free as well as commercial use under Apache license. It allows distributing and processing of dataset across the large Cluster set. On top of this, there are lot more application build by other organisation who use Hadoop or continuously work on this product which all comes under '<b><i>Hadoop Ecosystem'</i></b>. <a href="https://hadoopecosystemtable.github.io/" target="_blank"><b><span style="color: blue;">Check here</span></b></a> to find the list of projects under Hadoop Ecosystem. As we move further we will see post on the important projects under Hadoop Ecosystem.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2LjTB1I_aNcHP5Ru4-C_PqwpGD7BIlrW6RFUcYCu8VEcwmbnhZc-JvDkEZRb201brzZlvCre5r9wL-qEebJqzqB-SB8bkzMFsspRwNTWayiXjutrDg1S2Rn6EeJr5nBQ5N6hT3agFXP0/s1600/Hadoop+Arch.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="196" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi2LjTB1I_aNcHP5Ru4-C_PqwpGD7BIlrW6RFUcYCu8VEcwmbnhZc-JvDkEZRb201brzZlvCre5r9wL-qEebJqzqB-SB8bkzMFsspRwNTWayiXjutrDg1S2Rn6EeJr5nBQ5N6hT3agFXP0/s200/Hadoop+Arch.jpg" width="200" /></a>Apache Hadoop Architecture consist of following components:<br />
<ul style="text-align: left;">
<li><b>Hadoop Common</b>: It contains the libraries and other utilities needed by other module of Hadoop.</li>
<li><b>Hadoop Distributed File System (HDFS)</b>: It is cluster of Servers with commodity storage which is used for data storage across the cluster.</li>
<li><b>Hadoop YARN</b>: This component is used for Job scheduling and Resource management in Cluster.</li>
<li><b>Hadoop MapReduce</b>: The processing part of the data is done by this component. </li>
</ul>
At least now, I can consider that you are having a fair enough idea about this technology. But how can or who is the best person to get into Hadoop??<br />
<br />
Technical answer for that would be those who are interested to learn this technology can get in two ways:<br />
<ul style="text-align: left;">
<li><b>As a Developer</b></li>
<li><b>As a Administrator</b></li>
</ul>
<ol style="text-align: left;">
<li><b><u>As a Developer</u>: </b><i>Hadoop</i> is a framework which is built in JAVA language. So having <b><u>JAVA background can get easy access to become a Hadoop Developer</u></b>. Since the growing popularity of Hadoop, now a days this is the most common designation you can find in Job sites. </li>
<li><b><u>As a Administrator</u></b>: Most organisation with Hadoop installation prefer for a Part time or a <table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsQYICnmzZCE3ddPZ82rZ0pksSxyZbTZt1HbGSUYuBG0SeYdmo8dTJDTcZ9ZnLsSkDuVBOpBX1N1ASkU2S3lLmh8tf8emmLzbKA90Xeaw_ZopdJ5tmMmW4LUzT3zL1aXu_IPiDB_FhFec/s1600/Hadoop-Admin.png" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img alt="Hadoop Administration" border="0" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgsQYICnmzZCE3ddPZ82rZ0pksSxyZbTZt1HbGSUYuBG0SeYdmo8dTJDTcZ9ZnLsSkDuVBOpBX1N1ASkU2S3lLmh8tf8emmLzbKA90Xeaw_ZopdJ5tmMmW4LUzT3zL1aXu_IPiDB_FhFec/s200/Hadoop-Admin.png" title="Hadoop Administration" width="200" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><br /></td></tr>
</tbody></table>
Full time Administrator to manage there Hadoop Clusters. It is not compulsion that the admin should have the knowledge of JAVA to learn this technology. Indeed! they should have some basics for troubleshooting. Candidate those who are having knowledge with <b><u>Database Admin (SQL Server, Oracle, etc) background</u></b> who already have troubleshooting, Server maintenance, Disaster Recovery knowledge are preferred or anyone with <b><u>Network or Storage or Server Admin (Windows\Linux) skills</u></b> can be the other best choice. <a href="http://www.pythian.com/blog/hadoop-faq-but-what-about-the-dbas/" target="_blank"><b><span style="color: blue;">Here</span></b></a> in this post it is mentioned in detail who suits best for Hadoop. <b> </b></li>
</ol>
<div>
<u>Following might be the questions in your mind if we want to get start with Hadoop Admin</u>:<br />
<br />
<ol>
<li><b style="background-color: orange;">Do we need any DBA skills?</b> Of course Yes; If we need to Admin the Hadoop Cluster (Maintaining, Monitoring, Configuration, Troubleshooting,etc). </li>
<li><b style="background-color: orange;">Do we need to learn Java?</b> Yes; At least some basics to understand the Java errors while troubleshooting any issue.</li>
<li><b style="background-color: orange;">Do we need to understand Non-RDBMS?</b> Yes; Hadoop understand both SQL and NoSQL (Not only SQL). So having knowledge on Non-RDBMS product is most important.</li>
<li><b style="background-color: orange;">Do we need to learn Linux too?</b> Yes; at least the basics.</li>
</ol>
</div>
<div>
In our next post we will see the <b>concept of HDFS (Hadoop Distributed File Structure)</b>.</div>
<br />
Interested in learning SQL Server Clustering <b><a href="http://vikasbsahu.blogspot.in/2015/08/sql-server-2012-clustering-part-1.html" target="_blank"><span style="color: blue;">check here</span></a></b>. Stay tuned for many more updates...<br />
<br />
Keep Learning and Enjoy Learning!!!<br />
<div class="MsoNormal">
<br /></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-38130787226419348942015-11-02T12:58:00.003+05:302015-11-02T12:58:34.753+05:30Error - While Putting Database from Single to Multi User Mode<div dir="ltr" style="text-align: left;" trbidi="on">
Dear Friends,<br />
<div>
<br /></div>
<div>
<b><a href="http://vikasbsahu.blogspot.in/2015/10/how-to-start-sql-server-instance-when.html" target="_blank"><span style="color: blue;">Click here</span></a></b> to check how to start the SQL Server without TempDB Database. Let's learn what to do or how simple it can be to change the Database Mode from Multi User to Single User Mode or vice-versa?<br />
<br />
Indeed! it is simple with the following command:<br />
<br />
<b>a. Alter Database Out set Single_User </b><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuFEDg5CdHi1Eg3HBoO6k5PDfF_UdCCEWlDxIZqhXt1vxp5bF0ZT7hShAAZYZKzEixwn-YSaCgaDejtg0dH6sZY7-hOeIBDwzpPH6XkMBzJlJw1ytOc11PuxNt_eGdRXYk83wqVgbjZ1w/s1600/11.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="137" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjuFEDg5CdHi1Eg3HBoO6k5PDfF_UdCCEWlDxIZqhXt1vxp5bF0ZT7hShAAZYZKzEixwn-YSaCgaDejtg0dH6sZY7-hOeIBDwzpPH6XkMBzJlJw1ytOc11PuxNt_eGdRXYk83wqVgbjZ1w/s200/11.png" width="200" /></a><b>b. Alter Database Out set Multi_User</b><br />
<br />
If we don't mention any termination clause like above it will run until the statements get completed.<br />
<br />
Suppose there are n numbers of users connected to the Database and you executed the above command it will take hell lot of time to complete.<br />
So rather, you can force disconnect the users to put the Database in Single User mode you have to fire the below command:<br />
<br />
Alter Database out set Single_user with<b> Rollback After 30 -- </b>After 30 Seconds it will cancel and Rollback the Query<br />
<br />
Alter Database out set Single_user with<b> Rollback Immediate -- </b>It will immediately cancel and Rollback the Query<br />
<br />
Alter Database out set Single_user with<b> No_Wait -- </b>If there is any incomplete transaction No_Wait will Error<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_EQIbNZ82sU5MQD27avRLLC4bqzzY4sTmue4VcHzNZrHOsVUtE1pxNDxp2Ojg0dKtGmoaVMW32UDVjS1BiF6QoEW8zceAVC1SIgXkvCoaTHjKk_rFu3XY1JjXXhe9cKlNb2Sde8Pmrg4/s1600/1.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="168" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_EQIbNZ82sU5MQD27avRLLC4bqzzY4sTmue4VcHzNZrHOsVUtE1pxNDxp2Ojg0dKtGmoaVMW32UDVjS1BiF6QoEW8zceAVC1SIgXkvCoaTHjKk_rFu3XY1JjXXhe9cKlNb2Sde8Pmrg4/s320/1.PNG" width="320" /></a>But again it might get horror if the Databases is in Single User and you cannot access the Database because only one connection can be made at a time and just think that connection is taken by the system i.e. SQL Server.<br />
<br />
In this situation you are locked out, reason you cannot access the Database. Like you can see in the snapshot the <b>Database Out</b> is used by the system i.e. it is used by the Background process.<br />
<br />
If you try to bring back the Database again in <b>Multi User</b> mode system will throw the following error:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYW_LmveJqm_eFUVEI26ySgFRml7h4pFgpaJB3q79Oa7WpnGNYOvgkhFeT74T7ySz1d1FLf9sz4-ERLyl07mapzT6bw546VmYp502AAUpKIAPs6J0Byp9YC9l-o2wAPqT0pmoj_Cr-WsM/s1600/3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="97" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYW_LmveJqm_eFUVEI26ySgFRml7h4pFgpaJB3q79Oa7WpnGNYOvgkhFeT74T7ySz1d1FLf9sz4-ERLyl07mapzT6bw546VmYp502AAUpKIAPs6J0Byp9YC9l-o2wAPqT0pmoj_Cr-WsM/s640/3.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4vkVrAB3xq_9-uYrEHFc5h3Ck_u_OtN8SDwERzQnO2hpm5C3XmHL_lBOcW5gNECF9yT6dzh4mtLpRmi9WcSWmQ-zBLXqk2j-MEyib8GF4OPCEuX38akQXtMwMAeEql-b_BjdXnTQR6M0/s1600/4.png" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="160" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4vkVrAB3xq_9-uYrEHFc5h3Ck_u_OtN8SDwERzQnO2hpm5C3XmHL_lBOcW5gNECF9yT6dzh4mtLpRmi9WcSWmQ-zBLXqk2j-MEyib8GF4OPCEuX38akQXtMwMAeEql-b_BjdXnTQR6M0/s400/4.png" width="400" /></a>Another possibility you can try would be detaching the Database. But when I tried detaching the Database, SQL Server will first kill the connections to the Database. Rather I should frame it as SQL Server will kill only the User connect and not the system connection.<br />
<br />
After loads of struggle we were back to square one, that our Database was not getting back to Multi User mode. Seems it was like a deadlock between System SPID with <b>Out Database</b>. So we enabled the trace flag and checked the Error Log file. So following snapshot confirms that there was an deadlock:<br />
<br />
<b>DBCC TRACEON (1204,1222,-1)</b><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx5FcqZxL1mh2Ebo_9MY0njBLJuWs7T4oIeYgejaMLHMj5xO0Jf8XvOMFVx7eAQ7CJTamDLspIc0O8x2pOGHNjIpTS2rA1w2Xua7tNKAh4W1hyphenhyphenDI3I0JUQwXrkCJyC-9sAIne7f3_QQbE/s1600/SPID-9.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="Deadlock Graph" border="0" height="322" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhx5FcqZxL1mh2Ebo_9MY0njBLJuWs7T4oIeYgejaMLHMj5xO0Jf8XvOMFVx7eAQ7CJTamDLspIc0O8x2pOGHNjIpTS2rA1w2Xua7tNKAh4W1hyphenhyphenDI3I0JUQwXrkCJyC-9sAIne7f3_QQbE/s640/SPID-9.PNG" title="Deadlock Graph" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Deadlock Graph</b></td></tr>
</tbody></table>
<div class="separator" style="clear: both; text-align: center;">
</div>
If we try to Alter the Database to put it in Multi User mode. We will get a deadlock and since our Alter Statement is having low priority it will fail with the error message:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp2PTBmvRpr4fhD22PC7rvgYvWrbqu-4lQzQ1g78aA3kx2c0Bi0KPzO25rsMsu1DKR6OKz8eEzJ2IkFuqyTXeTDrSsBAk-uFUygyCS5AWbGbPSy4yNyx2B8Dn9k69Fzl0fNOKZR3KH6v4/s1600/SPID-8.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="72" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp2PTBmvRpr4fhD22PC7rvgYvWrbqu-4lQzQ1g78aA3kx2c0Bi0KPzO25rsMsu1DKR6OKz8eEzJ2IkFuqyTXeTDrSsBAk-uFUygyCS5AWbGbPSy4yNyx2B8Dn9k69Fzl0fNOKZR3KH6v4/s640/SPID-8.PNG" width="640" /></a></div>
<br />
After random tries we tried the following command and it saved us. We have to set the deadlock priority high and then execute the Multi User mode query like below:<br />
<b><br /></b>
<b>Set Deadlock_Priority High</b><br />
<b>Go</b><br />
<b>Alter Database Out Set Multi_User</b><br />
<br />
So what this will do is it will set the Dead Lock priority High and Alter the Database to Multi User mode.<br />
<br />
Guys do share the feedback about this article and of course about the blog too.<br />
<br />
Want to start learning SQL Server Clustering?? <b><a href="http://vikasbsahu.blogspot.in/2015/08/sql-server-2012-clustering-part-1.html" target="_blank"><span style="color: blue;">Check here</span></a></b> the three part series on <b>SQL Server Clustering</b>.<br />
<br />
Do you know MS SQL Server 2016 is ready to launch?? <b><a href="http://vikasbsahu.blogspot.in/2015/07/new-features-in-sql-server-2016-part-1.html" target="_blank"><span style="color: blue;">Check here</span></a></b> the two part series on <b>New Features of SQL Server 2016</b>.<br />
<br />
Keep Learning and Enjoy Learning!!!<br />
<br /></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-77237473549763812042015-10-20T16:05:00.001+05:302015-10-20T16:08:37.608+05:30Error 233 - No process is on the other end of the pipe<div dir="ltr" style="text-align: left;" trbidi="on">
Hi Guys,<br />
<br />
As we have seen <a href="http://vikasbsahu.blogspot.in/2015/08/error-1814-could-not-start-sql-server.html" target="_blank"><b><span style="color: blue;">here</span></b></a> an error related to SQL Server Restart. Today we will see another error i.e. Error 233 "<b>A connection was successfully established with the server, but then an error occurred during the login process</b>."<br />
<br />
You might have faced this common issue in your DBA career and yes the solution is relatively simple.<br />
<br />
So what I was doing, I was connecting to the SQL Server from Windows Login but during the login process it failed and prompted the below error message:<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8n30zC9mKKm7ddyuXqAD-yLKRqqZDFkfosDL4SnklWSWcdyjOweTWF7gT3jWbcl9KFKh9LdZjrDbTJJc_R9GOz_q0ej_baUkDreIsOa5n7DUQG82dRJvTOIKBU04brOplN4C9Ol5AphE/s1600/1.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8n30zC9mKKm7ddyuXqAD-yLKRqqZDFkfosDL4SnklWSWcdyjOweTWF7gT3jWbcl9KFKh9LdZjrDbTJJc_R9GOz_q0ej_baUkDreIsOa5n7DUQG82dRJvTOIKBU04brOplN4C9Ol5AphE/s640/1.png" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Error 233 in SQL Server</b></td></tr>
</tbody></table>
As I said this seems an common problem so there might be multiple workaround for this.<br />
<br />
Now, for me the solution was to start the <b>SQL Server management Studio</b> (SSMS) under "<b>Run as Administrator</b>" (When I was fresher, I use to always wonder what difference it makes if I start any program under Run as Administrator?? Let's find out the reason for that).<br />
<br />
Reason for this error was:<br />
<br />
a. When we are running an application under "<b>Run As Administrator</b>" we get extra privileges which we may not have under the local user account.<br />
<br />
b. So running the program under Run As Administrator will grant extra rights to the account (but it should have admin rights in Active Directory). <br />
<br />
<b>Once I started the SSMS under Run as Administrator and connected to SQL Server it succeeded.</b><br />
<br />
There might be chances that this may not solve this issue, check here to find more work around on the issue.<br />
<br />
Do you know?? We should properly set the Auto Growth parameter in Database else it would end up with consuming extra space. <b><a href="http://vikasbsahu.blogspot.in/2015/06/hi-guys-thanks-lot-friends-for-your.html" target="_blank"><span style="color: blue;">Check here</span></a></b>.<br />
<br />
Thanks,<br />
Vikas B Sahu<br />
<br />
Keep Learning and Enjoy Learning!!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-65694934672229478932015-10-08T19:58:00.004+05:302015-10-12T14:37:06.125+05:30How to Start SQL Server Instance when TempDB is unavaliable<div dir="ltr" style="text-align: left;" trbidi="on">
Hi Guys,<br />
<br />
<b><a href="http://vikasbsahu.blogspot.in/2015/10/error-analysis-services-failed-to-start.html" target="_blank"><span style="color: blue;">Here</span></a></b> is my last post on error while starting the SQL Server Analysis Services (SSAS) and <a href="http://vikasbsahu.blogspot.in/2015/08/error-1814-could-not-start-sql-server.html" target="_blank"><b><span style="color: blue;">here</span></b></a> I've shared earlier on a issue, I request you please read that before proceeding.<br />
<br />
Today we will learn a fact on the internal of SQL Server Tempdb (which was little unusual for me before I actually faced it. So I must say it was a learning experience).<br />
<br />
<u><b>Following are some questions that comes to my mind when I think about TempDB</b></u>:<br />
<br />
1. Where and how exactly the TempDB Database is used?<br />
<br />
2. What exactly happened when your TempDB is corrupted?<br />
<br />
3. Have you ever though of restarting the SQL Server Services without TempDB? Is it possible to do that?<br />
<br />
4. Is it possible to Backups (Full, Differential or Log) and Restore the TempDB Database?<br />
<br />
<b><u>Let's see the possible answers to these questions</u></b>:<br />
<br />
<b>Q 1. Where and how exactly the TempDB Database is used?</b><br />
<br />
<b>Ans</b>.<b> </b><i><b>i.</b> It is a <b>System Database</b>. Database ID for TempDB is <b>4</b>. </i><br />
<i> <b>ii.</b> It is use to store <b>temporary Data</b>. Whenever we create a hash table (#abc) it gets created in TempDB. </i><br />
<i> <b>iii.</b> It also used for <b>Sorting of Data</b> (for e.g. If we use Order by clause in a query it uses TempDB to sort).</i><br />
<i> <b>iv.</b> Also used for <b>Row-Versioning</b>.</i><br />
<i> <b>v.</b> Recovery model of the TempDB is "<b>Simple</b>". </i><br />
<i> <b>vi.</b> We cannot run DBCC CheckDB on TempDB. </i><br />
<i><b> vii.</b> We cannot detach the TempDB files. (Rather I must say one cannot detach the system Database files) </i><br />
<i> <b>viii.</b> Most important it is <b>re-created every time whenever SQL Server is restarted</b>. By checking the "<b>Last Creation Date</b>" we can see when was the last SQL Server was restarted (can expect as an interview question).</i><br />
<br />
<b>2. What exactly happened when your TempDB is corrupted?</b><br />
<b><br /></b>
<b>Ans</b>.<b> </b><i><b>i.</b> Since we cannot run DBCC CheckDB; we cannot identify if there is any corruption on TempDB Database.</i><br />
<i> <b>ii.</b> The only way to get rid of corruption is to <b>restart the SQL Server Services</b>. Since it will recreate the Database files again.</i><br />
<i> <b>iii.</b> Without TempDB Database SQL Server Services cannot be started. </i><br />
<i> <b>iv.</b> Model Database acts as the template for all the user created Database as well as for TempDB. So if model Database is unavailable TempDB will not get create and hence SQL Server Service cannot be started.</i><br />
<i><br /></i>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4k4RibxKSacKIspqFrr6o_uBUQe1-tSDT7yfYblKRRVP_6OYoc_CQP1mNhQR7Scm-leLudPE_edYg77uL2oigxln7zwe1FptBtwoZiu_JLvIFZ53eMFYIRbcmZi0rySAArBFtyrZJeTA/s1600/TempDB.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="TempDB Cycle" border="0" height="268" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4k4RibxKSacKIspqFrr6o_uBUQe1-tSDT7yfYblKRRVP_6OYoc_CQP1mNhQR7Scm-leLudPE_edYg77uL2oigxln7zwe1FptBtwoZiu_JLvIFZ53eMFYIRbcmZi0rySAArBFtyrZJeTA/s640/TempDB.jpg" title="TempDB Cycle" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>TempDB Cycle</b></td></tr>
</tbody></table>
<b><br /></b>
<b>3. </b><b>Is it possible to Backups (Full, Differential or Log) and Restore the TempDB Database?</b><br />
<b><br /></b><b>Ans</b>.<b> </b><i><b>i.</b> Since TempDB is recreated every time when ever we restart the instance. We cannot Backup this Database.</i><br />
<b><br /></b>
<b>4. </b><b>Have you ever though of restarting the SQL Server Services without TempDB? Is it possible to do that?</b><br />
<b><br /></b>
<b>Ans</b>.<b> </b>(Now here is the question what made to write this post)<br />
<i><b><br /></b></i>
<i><b>i.</b> As said above, TempDB is restarted every time when a SQL Server is restarted. (This will create both the mdf and ldf files on the location present for TempDB in the Master Database). </i><br />
<i><b><br /></b></i>
<i><b>ii.</b> But what if there is a disk level corruption or the defined location is not present due to xyz reason.</i><br />
<i><br /></i>
<i><b>iii.</b> Now here comes my question: <b>Will the SQL Server Services will start??? </b>So, answer for this is YES!!! it is possible (Till now it was NO for me). </i><br />
<i><br /></i>
<i><b>iv.</b> If you will start the SQL Services normally (assuming there is a disk level corruption on the disk were the TempDB was placed) the disk is unavailable you will get the below error message:</i><br />
<i><br /></i>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsWP1PyFHi_cjdweZKzQpGrq-HVghV8OzlHPHaC4Y6jKjXCBPEmrloyKukvVPCHJoZRQARBFI7BQ_PIjRPHIzc9pCNWxhZnerlQRBab7VqoeAGle2rh4WkdGOllpCoic1LY0DU6CDN6No/s1600/Error.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="TempDB Error Log" border="0" height="108" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsWP1PyFHi_cjdweZKzQpGrq-HVghV8OzlHPHaC4Y6jKjXCBPEmrloyKukvVPCHJoZRQARBFI7BQ_PIjRPHIzc9pCNWxhZnerlQRBab7VqoeAGle2rh4WkdGOllpCoic1LY0DU6CDN6No/s640/Error.PNG" title="TempDB Error Log" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>TempDB Error Log</b></td></tr>
</tbody></table>
<b style="font-style: italic;">v. </b><i>So basically starting the SQL Services normally will not help you. Therefore we have to start the SQL Server Instance in </i><b><u>Single User Mode as well as Minimally Configured</u></b><i>. </i><br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjmoxb3hLC0li-58DSEAU-bg7mG0b7VE2NDLOfMvYI8miZcHUFUiNHYVEatbuPfzH6hC6nnhZEsq00EvBFbA8kbNdT6ssWXdKGlRJoQuvUOH7tAk_zLnDzbj92Fy7HakgJNwZ3Np7r0LY/s1600/TempDB1.PNG" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img alt="Startup Parameter" border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjmoxb3hLC0li-58DSEAU-bg7mG0b7VE2NDLOfMvYI8miZcHUFUiNHYVEatbuPfzH6hC6nnhZEsq00EvBFbA8kbNdT6ssWXdKGlRJoQuvUOH7tAk_zLnDzbj92Fy7HakgJNwZ3Np7r0LY/s320/TempDB1.PNG" title="Startup Parameter" width="273" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Adding Parameter in Startup parameter</b></td></tr>
</tbody></table>
<b>vi. </b>You can do that by adding <b>-m (Single User Mode)</b> and <b>-f </b>(<b>Minimal Configuration</b>) parameter in the startup parameter in the SQL Server Instance.<br />
<br />
<b>vii. </b>This will start your SQL Server in minimal configuration (of course TempDB files is created) placing the TempDB files i.e. Data file on the User Database Data file location and the Log file on the User Log file location.<br />
<br />
<b>viii.</b> And by this way your TempDB files are placed on different location other than the actual location for TempDB files.<br />
<br />
<b>ix. </b>But this different location is temporary and only if you will start your SQL Server Instance in minimal configuration. So it is important to change the TempDB location to another drive by using the following command:<br />
<b><br /></b>
<br />
<span style="font-family: Courier New, Courier, monospace;">USE master;</span><br />
<span style="font-family: Courier New, Courier, monospace;">GO</span><br />
<span style="font-family: Courier New, Courier, monospace;">ALTER DATABASE tempdb</span><br />
<span style="font-family: Courier New, Courier, monospace;">MODIFY FILE</span><br />
<span style="font-family: Courier New, Courier, monospace;">(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.mdf');</span><br />
<span style="font-family: Courier New, Courier, monospace;">GO</span><br />
<span style="font-family: Courier New, Courier, monospace;">ALTER DATABASE tempdb</span><br />
<span style="font-family: Courier New, Courier, monospace;">MODIFY FILE</span><br />
<span style="font-family: Courier New, Courier, monospace;">(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\TEMPDB\Tempdb.ldf');</span><br />
<span style="font-family: Courier New, Courier, monospace;">GO</span><br />
<div>
<span style="font-family: Courier New, Courier, monospace;"><br /></span></div>
<span style="font-family: Courier New, Courier, monospace;">SELECT name, physical_name AS Location</span><br />
<span style="font-family: Courier New, Courier, monospace;">FROM sys.master_files</span><br />
<span style="font-family: Courier New, Courier, monospace;">WHERE database_id = DB_ID(N'tempdb');</span><br />
<span style="font-family: Courier New, Courier, monospace;">GO</span><br />
<div>
<br /></div>
<b></b>Hope this post will give you a different angle on concept of TempDB. Further we will see so more findings on TempDB. Share your comment on this to discuss more on it. You might be aware on SQL Server 2016 <b><a href="http://vikasbsahu.blogspot.in/2015/07/new-features-in-sql-server-2016-part-1.html" target="_blank"><span style="color: blue;">check here</span></a></b> for the new features in it.<br />
<br />
Thank You !!!<br />
<b><br /></b>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-37647173275297379612015-10-07T19:31:00.002+05:302015-10-07T19:31:59.757+05:30Error - Analysis Services Failed To Start in SQL Server 2012 <div dir="ltr" style="text-align: left;" trbidi="on">
Hi Guys,<br />
<br />
Read my last blog on Error while configuring SQL Server Cluster <b><a href="http://vikasbsahu.blogspot.in/2015/10/sql-server-2012-clustering-errors.html" target="_blank"><span style="color: blue;">here</span></a></b>. As a DBA, we cannot or say we should not limit our self to only Database Engine related stuff. Today let's learn something related to Analysis Service i.e. SSAS.<br />
<br />
In one of the migration project, I wanted to migrate an SSAS Database which is called as "<b>Cube</b>" to another Server.<br />
<br />
So firstly, I needed the backup of the Cube. See the following command to take the backup of the Cube.<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwCwWLNpiHRELgubNheZMvh0lCRtTbGzW-HAzlAN4EVfi9id34SB4o3DyyTCxhszWLKbSSJ0AAjX4Dt9WxX8FFNC-4qrpGrSgC7uA_W1gPRRBl_7w4VjHhUJqMJ-YpI9vyHGALDGf08Fc/s1600/5.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="SSAS Cube Backup Code" border="0" height="124" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjwCwWLNpiHRELgubNheZMvh0lCRtTbGzW-HAzlAN4EVfi9id34SB4o3DyyTCxhszWLKbSSJ0AAjX4Dt9WxX8FFNC-4qrpGrSgC7uA_W1gPRRBl_7w4VjHhUJqMJ-YpI9vyHGALDGf08Fc/s640/5.PNG" title="SSAS Cube Backup Code" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>SSAS Cube Backup Code</b></td></tr>
</tbody></table>
Where <b>Test </b>is the<b> Database Name</b> and<b> Test.abf </b>is the<b> Backup Name</b> (Which will take the Backup in the default location).<br />
<br />
Now, I wanted to restore this backup to my new Server. But when I tried to connect the SSAS, it got failed due to SSAS was disabled. So tried to start the SSAS and it gave the following error:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgULHhqNeMXn5D2dHupPFFiNRpbtqqkLB0xkd5eAFhkYFb50wmztLpS7pCbbIl34fDNqn8i4HOynabSGfn7ODpZ2yBoeYW0lKCAU5qr7dWD3CjLDGXbeeeBrviltDc1YpXmY0xNksgdEN8/s1600/1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="SQL Server Analysis Services Stopped" border="0" height="146" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgULHhqNeMXn5D2dHupPFFiNRpbtqqkLB0xkd5eAFhkYFb50wmztLpS7pCbbIl34fDNqn8i4HOynabSGfn7ODpZ2yBoeYW0lKCAU5qr7dWD3CjLDGXbeeeBrviltDc1YpXmY0xNksgdEN8/s400/1.PNG" title="SQL Server Analysis Services Stopped" width="400" /></a></div>
<br />
And when I checked <b>Windows Event Viewer</b> it captured the below error message:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh960NjsPGPVrAPiCyAZFGsa99oaHbAvtNPglwKYMy4LB7AAkhyQ06dW92nVErez6RWUcn4UHpiEhY-1ICyI2r7BySjerrPIB6vlXpnnEFyFChCijh4H37HYLekqFij6TzkTKSqaZE6p1A/s1600/2.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="Windows Event Viewer Error" border="0" height="94" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh960NjsPGPVrAPiCyAZFGsa99oaHbAvtNPglwKYMy4LB7AAkhyQ06dW92nVErez6RWUcn4UHpiEhY-1ICyI2r7BySjerrPIB6vlXpnnEFyFChCijh4H37HYLekqFij6TzkTKSqaZE6p1A/s640/2.PNG" title="Windows Event Viewer Error" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Windows Event Viewer</b></td></tr>
</tbody></table>
From the Error in the Event Viewer it is confirmed that it is related to something with permission issue. So what we commonly do is "<b>Going to that particular drive\ Folder and give full right to the account which runs the SQL Server Analysis Services</b>". But unfortunately it din't helped.<br />
<br />
I started searching more in google related to this error and in most of the sites they will provide the same solution which we saw above. And the search continued and continued till I landed to the next solution. And solution was to check the <b><u>msmdsrv.ini</u></b> file (It is an Configuration file for AS).<br />
<br />
Basically when we configure AS, we have to pass a location where Data and Log file will create, but in my case no such files where created. So what did was as follows:<br />
<br />
1. Created two Folder's Data & Log on E:\ and F:\ drive respectively and gave full permission to it.<br />
<br />
2. Open the msmdsrc.ini file (in notepad) from this location (default):<br />
<b><br /></b>
<b>C:\Program Files\Microsoft SQL Server\MSAS11.MSSQLSERVER\OLAP\Config</b><br />
<b><br /></b>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgjrzwHfpROA20dmHiHLfyPVQk-gydk38lV9C4qi6tSBoSV9Z4kGUrzudAm-QEGwD2TYPXLP14yogFP4UKc-7hknpPYH8wh_NTJW2hC_4RenlD0KY1JFSlFfE_xaLPJfalQFH5knz_sKU/s1600/3.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="msmdsrv.ini" border="0" height="118" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhgjrzwHfpROA20dmHiHLfyPVQk-gydk38lV9C4qi6tSBoSV9Z4kGUrzudAm-QEGwD2TYPXLP14yogFP4UKc-7hknpPYH8wh_NTJW2hC_4RenlD0KY1JFSlFfE_xaLPJfalQFH5knz_sKU/s640/3.PNG" title="msmdsrv.ini" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>msmdsrv.ini File</b></td></tr>
</tbody></table>
3. As we can see<b> DataDir</b> and <b>LogDir </b>tags<b> </b>which contains the location for the files; Change the path save it.<br />
<br />
4. <b>Then tried to re-start the AS and it was successfully started.</b><br />
<br />
So the resolution for this seems simple. Want to know what is SQL Server Azure read my <a href="http://vikasbsahu.blogspot.in/2014/09/introduction-on-sql-azure.html" target="_blank"><b><span style="color: blue;">introduction post on Azure</span></b></a>. Do comment if you like of dislike the page.<br />
<br />
Thank You !!!<br />
Keep Learning and Enjoy Learning !!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-75441953919949149372015-10-05T13:07:00.000+05:302015-10-09T18:26:20.469+05:30SQL Server 2012 Clustering Errors<div dir="ltr" style="text-align: left;" trbidi="on">
Hey Friends,<br />
<br />
So till now we have seen the installation and configuration of SQL Server 2012 Clustering in our three part series as <a href="http://vikasbsahu.blogspot.in/2015/08/sql-server-2012-clustering-part-1.html" target="_blank"><b><span style="color: blue;">Part 1</span></b></a>, <b><a href="http://vikasbsahu.blogspot.in/2015/09/sql-server-2012-clustering-part-2.html" target="_blank"><span style="color: blue;">Part 2</span></a></b> and <a href="http://vikasbsahu.blogspot.in/2015/09/sql-server-2012-clustering-part-3.html" style="color: blue; font-weight: bold;" target="_blank">Part 3</a><b style="color: blue;"> </b>of SQL Server 2012 Clustering.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQI178ih8oxuFj9bwqiKRIN3425WyZGbVcGJmZy4r1IQgPLZXSyAg2XXw4G95EgRHqWo4nLlkutIqUopuOejuxXAG1BiUW0t5Lkdhh1df2POwZVt011j5iO72ZlEDD9xplsUGWsVlFCw4/s1600/A.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQI178ih8oxuFj9bwqiKRIN3425WyZGbVcGJmZy4r1IQgPLZXSyAg2XXw4G95EgRHqWo4nLlkutIqUopuOejuxXAG1BiUW0t5Lkdhh1df2POwZVt011j5iO72ZlEDD9xplsUGWsVlFCw4/s1600/A.jpg" /></a></div>
<br />
<u>Now let us see few errors which I have faced during the Installation and Configuration of SQL Server 2012 Clustering</u>:<br />
<br />
<b>1.</b> <b>Error</b>: <b>Cluster Service verification Failed</b><br />
<b> Description</b>: During one of the Server Migration i.e. Windows Server 2012 and SQL Server 2008 R2<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7rmsHU2iklureAd74xRBTG_dRznCDpPXDukPrGWcgPX_2kJwxjDeOKut71QLs4-9UO6srmhygbNHCRkev1Z8HEldGreLs6dWiqGVyq6Uv_2JmD7HSonVuBwcLo0wpFEJWywvgTUMKTi0/s1600/34.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="Add a failover Cluster Node" border="0" height="482" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7rmsHU2iklureAd74xRBTG_dRznCDpPXDukPrGWcgPX_2kJwxjDeOKut71QLs4-9UO6srmhygbNHCRkev1Z8HEldGreLs6dWiqGVyq6Uv_2JmD7HSonVuBwcLo0wpFEJWywvgTUMKTi0/s640/34.PNG" title="Add a failover Cluster Node" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Error-1</b></td></tr>
</tbody></table>
<b>Solution: </b>The solution was basically we have run the below command from the power shell:<br />
<br />
<b><i>Install-WindowsFeature -Name RSAT-Clustering-AutomationServer</i></b><br />
<br />
Basically MsClus.dll library is by default disabled in Windows Server 2012. So by running the above command it enables it which is require for installation of SQL Server 2008 R2 Cluster.<br />
<br />
I've got the solution of this from <b><a href="http://blogs.msdn.com/b/clustering/archive/2012/04/06/10291601.aspx" target="_blank"><span style="color: blue;">this msdn link</span></a></b>.<br />
<br />
<b>2.</b> <b>Error</b>: <b>Cluster Service verification Failed</b><br />
<b> Description</b>: During one of the Server Migration i.e. Windows Server 2012 and SQL Server 2008 R2<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4ZUbelFuwecWJBoPsBebEIsL-3k8hD-9hCIHCZtYzirQVpeF9CD_hZKfNFllOXewlykkZpaVbpUE3U4v0POUb0F_1kjkTH_7z_vWBrLrTcP2T8Su2R_0IeFFx0aRRbF7YLxiA2eBiwi8/s1600/Error-5.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img alt="Install a SQL Server Failover Cluster" border="0" height="512" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4ZUbelFuwecWJBoPsBebEIsL-3k8hD-9hCIHCZtYzirQVpeF9CD_hZKfNFllOXewlykkZpaVbpUE3U4v0POUb0F_1kjkTH_7z_vWBrLrTcP2T8Su2R_0IeFFx0aRRbF7YLxiA2eBiwi8/s640/Error-5.PNG" title="Install a SQL Server Failover Cluster" width="640" /></a></div>
<br />
<b>Solution: </b><br />
<br />
Follow the below steps:<br />
<br />
<b><i>Copy C:\Windows\Cluster\Clusres.dll TO C:\Windows\system32 and rename the file to W03a2409.dll</i></b><br />
<br />
For this error, if you will search in internet you might get multiple solutions. I found this solution is the shortest and best way to resolve the error.<br />
<br />
I've got the solution of this from <b><a href="https://social.technet.microsoft.com/Forums/windowsserver/en-US/9cb1308d-edd0-44e5-a10e-dfb9f10a1ef3/sql-server-2008-r2-failed-on-windows-server-2012" target="_blank"><span style="color: blue;">this Microsoft link</span></a></b>.<br />
<br />
Keep Learning and Happy Learning!!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-79202219162222156292015-09-14T16:11:00.001+05:302015-10-06T14:53:26.936+05:30SQL Server 2012 Clustering - Part 3<div dir="ltr" style="text-align: left;" trbidi="on">
Hi Guys,<br />
<br />
As we have seen my previous posts on how to configure the <b><a href="http://vikasbsahu.blogspot.in/2015/08/sql-server-2012-clustering-part-1.html" target="_blank"><span style="color: blue;">Windows Clustering</span></a></b> and <b><a href="http://vikasbsahu.blogspot.in/2015/09/sql-server-2012-clustering-part-2.html" target="_blank"><span style="color: blue;">Setup the Quorum</span></a></b>, now we will move forwards towards the final step to configure the SQL Server Cluster.<br />
<br />
Installing and Configuring SQL Server Cluster is <b>very much similar to installing a SQL Stand alone Server</b>. Here, we will install and configure a <b>two node Active - Passive Cluster</b> and will see only the different pages occur while Installing and Configuring the SQL Server Clustering rest pages remains the same.<br />
<br />
<u>Let's start with the installation of SQL Server Cluster on Active Server. Following are snapshots for it</u>:<br />
<br />
<b>1.</b> Start the installation and Click "<b>Installation</b>" link on left hand side of the page. And then start the installation with "<b>New SQL Server Failover Cluster Installation</b>".<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLW9vlinFVhyphenhyphenjbYkJXlUAaHSid8V4gmj0SsquhWND9xrfQO9sR19Ew2XtBB51GigjuAfQt_fgDrRjuVVUscaJuKIhUxHSvgypjMwbfQ12i6_s3WjbduR29f1vlu-gG_VdpCM0ZoagbhcM/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="322" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjLW9vlinFVhyphenhyphenjbYkJXlUAaHSid8V4gmj0SsquhWND9xrfQO9sR19Ew2XtBB51GigjuAfQt_fgDrRjuVVUscaJuKIhUxHSvgypjMwbfQ12i6_s3WjbduR29f1vlu-gG_VdpCM0ZoagbhcM/s640/1.png" width="640" /></a></div>
<br />
<b>2.</b> After couple of Next and Next's you will land to "<b>Instance Configuration</b>" page. Now here you have to Enter the "<b>SQL Server Network Name</b>" and the "<b>Instance Name</b>" (For named instance)<br />
<br />
<b>For Eg</b>. If you enter here the Network Name as "<b>SQLServer_01</b>" and Instance Name as "<b>SQL01</b>". Then the Server Name in the connectivity will be "<b>SQLServer_01\</b><b>SQL01</b>".<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPbWidl0wc5WiglY1o7EqnBktoW7j_e05_XUWpRGoSQb74d2H_zfQ0gYMaMBZoBK9qWnxeNA2JnLjdpJF0tQ_a4cvP4VVvY5cYN_Kz9doOVDtdHn1WhzN336ev0BIA2g2JiUMbu20fbwA/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="366" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPbWidl0wc5WiglY1o7EqnBktoW7j_e05_XUWpRGoSQb74d2H_zfQ0gYMaMBZoBK9qWnxeNA2JnLjdpJF0tQ_a4cvP4VVvY5cYN_Kz9doOVDtdHn1WhzN336ev0BIA2g2JiUMbu20fbwA/s640/2.png" width="640" /></a></div>
<br />
<b>3.</b> Again after few Next's you will land to "<b>Cluster Resource Group</b>" page, were you have to enter a Unique name as a Cluster Resource Group Name. This will help in case there are multiple Cluster configuration.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGke7MAPsBeZ8TyKbFlWK-kus7lZzjmmodUORfwv3Na65woaLoyhCHSyXNIl7ZpPUKqjTf4I7vNL9uBbpRVtps98W8lCCKnLJ4S9T3996WrJzDkq0YwmJ2ko2UnSVqLkUwKZpS814PURo/s1600/3.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="332" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGke7MAPsBeZ8TyKbFlWK-kus7lZzjmmodUORfwv3Na65woaLoyhCHSyXNIl7ZpPUKqjTf4I7vNL9uBbpRVtps98W8lCCKnLJ4S9T3996WrJzDkq0YwmJ2ko2UnSVqLkUwKZpS814PURo/s640/3.PNG" width="640" /></a></div>
<br />
<b>4.</b> Here we have to check all the Disks which you want to bring under that Cluster Group.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJH-9gfvKb6L6PP4T2FmcKsGLoRNNEdDyZE4XckXq2eYS49JMR7HBv4Ru4siE57pviaTIbNvVOXf3Bts6RwhvQR44tYZW6_z7Zbz-l-1Oj4XrQGI_E8Pf1zGnPSTH4y-r8Ox_h0a7VVxs/s1600/4.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="332" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJH-9gfvKb6L6PP4T2FmcKsGLoRNNEdDyZE4XckXq2eYS49JMR7HBv4Ru4siE57pviaTIbNvVOXf3Bts6RwhvQR44tYZW6_z7Zbz-l-1Oj4XrQGI_E8Pf1zGnPSTH4y-r8Ox_h0a7VVxs/s640/4.PNG" width="640" /></a></div>
<br />
<b>5.</b> Enter an "<b>Unique IP address</b>" in the Address box after un-checking the DHCP.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuHaRxPxEEfi476SlWrhz4UAl33RWgq9ptpBlXCORNEIzOsrprT6kwfUnsiLYLGOfCxqGKSSP3VLcN6habx8gfTQBIhMpikOPE4VevYUvKu8Is4kc05BY3HS1j9I9sf9aYHgrQEpTt5F4/s1600/5.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="314" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhuHaRxPxEEfi476SlWrhz4UAl33RWgq9ptpBlXCORNEIzOsrprT6kwfUnsiLYLGOfCxqGKSSP3VLcN6habx8gfTQBIhMpikOPE4VevYUvKu8Is4kc05BY3HS1j9I9sf9aYHgrQEpTt5F4/s640/5.PNG" width="640" /></a></div>
<br />
<b>6.</b> So by now your SQL Server Cluster is installed on the Active Node. Now, you have to start Adding node on the Passive instance. As you can see the snapshot you have to start the installation and Click "<b>Installation</b>" link on left hand side of the page. But this time choosing "<b>Add node to a SQL Server Failover Cluster</b>".<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-6XLad5OVYgSD2CwVfdy5h3Esgurdojfx0_RquIsV2wQlNvQeoMvVjvHo8pFQnRGNkEY8gr3n1zSpBh-9ov0ISgM-M7Q-zfWrp5Ii_oMgT_byBQiV7ni_fK8m36apylSzu_66txTqcVo/s1600/6.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="310" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-6XLad5OVYgSD2CwVfdy5h3Esgurdojfx0_RquIsV2wQlNvQeoMvVjvHo8pFQnRGNkEY8gr3n1zSpBh-9ov0ISgM-M7Q-zfWrp5Ii_oMgT_byBQiV7ni_fK8m36apylSzu_66txTqcVo/s640/6.PNG" width="640" /></a></div>
<br />
Rest of the process are simple. So now the SQL Server Cluster is ready to deploy your Database.<br />
<br />
Click here to refer <b><a href="http://vikasbsahu.blogspot.in/2015/08/sql-server-2012-clustering-part-1.html" target="_blank"><span style="color: blue;">Part 1</span></a></b> and <a href="http://vikasbsahu.blogspot.in/2015/09/sql-server-2012-clustering-part-2.html" target="_blank"><span style="color: blue;"><b>Part 2</b></span></a> of this topic.<br />
<br />
We will discuss the Errors while configuring the SQL Server Clustering in different post. So stay tuned.<br />
<br />
Till then Keep Learning and Enjoy Learning!!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-8810737773678758412015-09-08T20:00:00.000+05:302015-09-08T20:00:24.642+05:30Error 1807 - Database creation failed<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
Hi Friends,<br />
<br />
While creating a Database an Error 1807 occurred as you can see the following snapshot.<br />
<br />
<b>Basically the create database failed due to an exclusive lock cannot be obtained on "Model" Database. </b><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjP2_7-0uPSE0Oprdby9RXZrrdYNVtFuv00n1JgUKEpMYylrohuf06p5qeXDU0UfwY43t_oGN2JVAQsRWgbcqdO43VEaX8A0HF8_DhOeFBNKskyz5OTVYWLaf9of7ScQx2gYpvlUesfznk/s1600/Error.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="Error 1807 - Database creation failed" border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjP2_7-0uPSE0Oprdby9RXZrrdYNVtFuv00n1JgUKEpMYylrohuf06p5qeXDU0UfwY43t_oGN2JVAQsRWgbcqdO43VEaX8A0HF8_DhOeFBNKskyz5OTVYWLaf9of7ScQx2gYpvlUesfznk/s640/Error.PNG" title="Error 1807 - Database creation failed" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Error 1807 - Database Creation Failed</b></td></tr>
</tbody></table>
<br />
Run the below set of queries to find out were exactly is Model Database is on use:<br />
<br />
Use master<br />
GO<br />
IF EXISTS(SELECT request_session_id FROM sys.dm_tran_locks<br />
WHERE resource_database_id = DB_ID('Model'))<br />
PRINT 'Model Database being used by some other session'<br />
ELSE<br />
PRINT 'Model Database not used by other session'<br />
<br />
<br />
SELECT request_session_id FROM sys.dm_tran_locks<br />
WHERE resource_database_id = DB_ID('Model')<br />
<br />
<br />
DBCC InputBuffer(52)<br />
<br />
Kill the SPID and re-run the create database syntax. This time you will create a Database without any error.<br />
<br />
Check out the error <b><a href="http://vikasbsahu.blogspot.in/2015/07/msg-22049-error-executing-extended.html" target="_blank"><span style="color: blue;">here</span></a></b> while executing the Extended Stored Procedure.<br />
<br />
Keep Learning and Enjoy Learning!!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-66187900082880413542015-09-08T13:56:00.000+05:302015-09-08T13:56:38.492+05:30SQL Edition Upgrade Architecture Mismatch<div dir="ltr" style="text-align: left;" trbidi="on">
Hi Friends,<br />
<br />
I have recently posted an article on how to configure Quorum which was <b><a href="http://vikasbsahu.blogspot.in/2015/09/sql-server-2012-clustering-part-2.html" target="_blank"><span style="color: blue;">Part 2</span></a></b> post of three part series of <b><a href="http://vikasbsahu.blogspot.in/2015/08/sql-server-2012-clustering-part-1.html" target="_blank"><span style="color: blue;">SQL Server Clustering</span></a></b>.<br />
<br />
Recently I was checking my old mails and from that I found an error which we have faced a year back. So though of sharing with you guys. As my headline states "<b>SQL Edition Upgrade Architecture Mismatch</b>", yes it some what belongs to Edition Upgradation error.<br />
<br />
<u>Following is the snapshot of the error</u>:<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmb3Emp0vOIphzldib9T1hIUwf48-fSOBYVDJUUN0o9rr66T5-Hs9rfMTRxBgjjgLgL3Hf87kIE8pbhyl2V6eMLDCfNYrQWsy8Xm49QrQGUF5u2ALtP9zo2ZBWJlsXOOZSs3DdLS-BLSs/s1600/2.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="SQL Edition Upgrade Architecture Mismatch" border="0" height="350" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmb3Emp0vOIphzldib9T1hIUwf48-fSOBYVDJUUN0o9rr66T5-Hs9rfMTRxBgjjgLgL3Hf87kIE8pbhyl2V6eMLDCfNYrQWsy8Xm49QrQGUF5u2ALtP9zo2ZBWJlsXOOZSs3DdLS-BLSs/s640/2.png" title="SQL Edition Upgrade Architecture Mismatch" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Error - SQL Edition Upgrade Architecture Mismatch</b></td></tr>
</tbody></table>
Generally we do get this error on the landing page itself during the verification step if SQL Server found there is mismatch in the Bits i.e. x86 or x64 Bit. So the solution for this is very much simple.<br />
<br />
Go to the Option tab on the left hand side in the initial page. Now you can see there are two radio buttons x86 and x64. By default its x64. You can change this as per your requirement.<br />
<br />
You can see the following snapshot for reference:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguUiEW42q6eWsS_lSWn2lZ4IkdrY17xq1rFZNUa5kybOyYU64HRDMg1BrqakOHRza6GLFI1ZmMLjhOUS1A5umJW2q-4uo8Pvw3qN9uYAs5-IxH6fsad8XU7f46K2tf5XJ6scOPASY_5u4/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="366" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEguUiEW42q6eWsS_lSWn2lZ4IkdrY17xq1rFZNUa5kybOyYU64HRDMg1BrqakOHRza6GLFI1ZmMLjhOUS1A5umJW2q-4uo8Pvw3qN9uYAs5-IxH6fsad8XU7f46K2tf5XJ6scOPASY_5u4/s640/1.png" width="640" /></a></div>
<br />
After changing it you can proceed with the installation part.<br />
<br />
If anyone of you planning for SQL Server Certification refer <b><a href="http://vikasbsahu.blogspot.in/2015/06/ms-sql-2012-certification.html" target="_blank"><span style="color: blue;">this post</span></a></b>, it will give you an overview on the examination. Also you can comment on it if you have any.<br />
<br />
Keep Learning and Enjoy Learning!!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-15420110396899637032015-09-07T16:03:00.000+05:302015-10-09T18:25:31.234+05:30SQL Server 2012 Clustering - Part 2<div dir="ltr" style="text-align: left;" trbidi="on">
Dear Friends,<br />
<br />
As you have seen how to configure <a href="http://vikasbsahu.blogspot.in/2015/08/sql-server-2012-clustering-part-1.html" target="_blank"><b><span style="color: blue;">Windows Clustering</span></b></a>, now let's proceed to the next step of SQL Server 2012 Clustering which is <b>Configuration of Quorum</b>.<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
For configuration of Quorum following are the two approaches:<br />
<br />
<b>Method 1: </b>Failover Cluster Manager GUI<br />
<b><br /></b>
<b>Method 2: </b>Windows Power Shell<br />
<div>
<br /></div>
Let's have a look how to configure Quorum by these two method:<br />
<br />
<b>Method 1</b>: <u>Through Failover Cluster Manager GUI</u><br />
<br />
Below are the snapshot with description for the configuration of Quorum:<br />
<br />
1. Open the FOCM (Failover Cluster Manager). Towards the extreme right you can see "<b>More Actions</b>" Click that then click "<b>Configure Cluster Quorum Settings...</b>". You can see these steps in the below snapshot.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8kqm4GuCyE5qHhacnaha0ALvp3mXeg-iC2nRHKLOUf_smwhsohGinfUEbGAwrGoU95-ic9t43mIy07USC6WcK5-MokcmoRFi-cuUerp1q7EssdCWzqAhwkzNp8erJalv0OH0w6dQ4268/s1600/7-QRM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="236" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8kqm4GuCyE5qHhacnaha0ALvp3mXeg-iC2nRHKLOUf_smwhsohGinfUEbGAwrGoU95-ic9t43mIy07USC6WcK5-MokcmoRFi-cuUerp1q7EssdCWzqAhwkzNp8erJalv0OH0w6dQ4268/s640/7-QRM.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br />
2. Once you are in the page of cluster configuration you can refer the below snapshot in clockwise direction.<br />
<br />
<b>i.</b> Check the radio button "Select the Quorum Witness".<br />
<b>ii.</b> Next check "Configure a file share witness".<br />
<b>iii.</b> Pass the location (path name), here it will create the binary for Quorum.<br />
<b>iv.</b> Click next and check all the settings which you have configured.<br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCbzrvwULjTIdnkoFQYic0wlQwTapBKEhD5_hwIU8BOyt8Z0etXBRIp44CVMUT95J5eAqMfVTfn9NrkrqrnYqcDefXgD3TOkZeN7zEaWEvQNMZ4jE8vU6k-cP7yiV10Nuu54XVC9Tfihc/s1600/8-QRMSU.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="438" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjCbzrvwULjTIdnkoFQYic0wlQwTapBKEhD5_hwIU8BOyt8Z0etXBRIp44CVMUT95J5eAqMfVTfn9NrkrqrnYqcDefXgD3TOkZeN7zEaWEvQNMZ4jE8vU6k-cP7yiV10Nuu54XVC9Tfihc/s640/8-QRMSU.png" width="640" /></a></div>
<br />
3. <b>Click Finish and view the report. You have successfully configured the Quorum.</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiugj1tXDZiLFeb2qTt-6iKkOq0x9RxKhw5ZA_oGcXxmd9qTSe4kc3rxhOPocbht_cXsCWvFO10yK8nydQ1_KevdlXY75VHfzWIFNv0s0P9r6kENIlFEYrGjEHjzQyov1v2NYnQ3K5KmMo/s1600/9-QRMSUCCFUL.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="220" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiugj1tXDZiLFeb2qTt-6iKkOq0x9RxKhw5ZA_oGcXxmd9qTSe4kc3rxhOPocbht_cXsCWvFO10yK8nydQ1_KevdlXY75VHfzWIFNv0s0P9r6kENIlFEYrGjEHjzQyov1v2NYnQ3K5KmMo/s320/9-QRMSUCCFUL.png" width="320" /></a></div>
<br />
<b>Method 2</b>: <u>Through Windows Power Shell</u><br />
<br />
Configuring Quorum through Windows Power Shell is simple. The only thing we need to run is a one liner command as you can see in the below snapshot.<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfzScnrVgh2VVzPvGPEkYoQqZz4acsr0f0eY1WItBK680LmNe0eK4wJlWgLyzD9VbAZ1SxzaEt8mNGbGGMhKtobxgu5CVSPEEfY7XKSfBoAVuV8j2DmzdqBPGOhzipOFgQjt78y0FO6yY/s1600/6_PS.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em; text-align: center;"><img border="0" height="38" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgfzScnrVgh2VVzPvGPEkYoQqZz4acsr0f0eY1WItBK680LmNe0eK4wJlWgLyzD9VbAZ1SxzaEt8mNGbGGMhKtobxgu5CVSPEEfY7XKSfBoAVuV8j2DmzdqBPGOhzipOFgQjt78y0FO6yY/s640/6_PS.png" width="640" /></a><br />
<br />
<ul style="text-align: left;">
<li>Open Windows Power Shell from the Task bar or <b>open run and type "powershell"</b>.</li>
<li>Run the below command:</li>
<li><b>Set-ClusterQuorum -NodeAndFileShareMajority \\abc\sqlserver\</b></li>
</ul>
<br />
Till now we have seen how to <b><a href="http://vikasbsahu.blogspot.in/2015/08/sql-server-2012-clustering-part-1.html" target="_blank"><span style="color: blue;">Configure Windows Cluster</span></a></b> and <b>Configuration of Quorum</b>. So both these are the pre requisite for configuration of SQL Server Cluster as well as SQL Server AlwaysON. In the Part 3 of the post we will see how to configure SQL Server Cluster.<br />
<br />
Refer to the link of msdn <a href="https://technet.microsoft.com/en-us/library/jj612870.aspx" target="_blank"><b><span style="color: blue;">here</span></b></a> for more knowledge on Quorum setting.<br />
<br />
Click here to refer <b><a href="http://vikasbsahu.blogspot.in/2015/08/sql-server-2012-clustering-part-1.html" target="_blank"><span style="color: blue;">Part 1</span></a></b> and <b><a href="http://vikasbsahu.blogspot.in/2015/09/sql-server-2012-clustering-part-3.html" target="_blank"><span style="color: blue;">Part 3</span></a></b> of this topic.<br />
<br />
Thank you guys for reading the post, <a href="http://vikasbsahu.blogspot.in/2014/06/blog.html" target="_blank"><b><span style="color: blue;">click here</span></b></a> to read my first post on this blog. Do comment on the post.<br />
<br />
Keep Learning and Happy Learning!!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-23935364589498548102015-09-04T20:19:00.000+05:302015-09-04T20:24:33.634+05:30Dropping a SQL Server Database<div dir="ltr" style="text-align: left;" trbidi="on">
Hi Guys,<br />
<br />
<a href="http://vikasbsahu.blogspot.in/2015/08/error-1814-could-not-start-sql-server.html" target="_blank"><b><span style="color: blue;">Last post</span></b></a> I have shared an Error that I was unable to start my SQL Server Database Engine. Today here I will share a small finding which I thought of worth sharing it.<br />
<br />
In one of my migration project what I observed was "<b>Even after dropping a Database the Data files and Log files were still exist</b>". Ideally this should not happen because if we drop a Database the data file and log file associated with it should be dropped as well. But lets see what happened exactly that the database files are not dropped.<br />
<br />
<b>So what are the reasons for this?</b><br />
<br />
Following are various ways to remove or Offline the Database from the SQL Server Database Engine:<br />
<br />
a. Drop a Database<br />
b. Detach \ Attach<br />
c. Offline \ Online<br />
<br />
General syntax for dropping a Database is: <b>Drop Database Test</b><br />
<br />
<u><b>Let's see the scenario what exactly happened</b></u>:<br />
<br />
For the production migration what we were suppose to do was, remove then Test migrated Database and the replace it with the new Production migration Database. Following is the sequence which I followed:<br />
<br />
<b>Current Production Server</b> : SQLServer-Current<br />
<b>New Production Server</b> : SQLServer-New<br />
<div>
<b>Database Name</b> : Test</div>
<ol style="text-align: left;">
<li>The test migration for the Database "<b>Test</b>" was done on "<b>SQLServer-New</b>" from "<b>SQLServer-Current</b>" Server.</li>
<li>After proper testing on "<b>SQLServer-New</b>" the Databases was put Offline.</li>
<li>On the day of actual Production Migration, we dropped the database so that we can create a fresh copy of the Database.</li>
<li>Once it was dropped we started restoring the Database on the location were we have kept the data and log file.</li>
<li>It popped out an error message that the mdf and ldf files are exist. Either I should rename by new file name or I need to change the path.</li>
<li>Since I cannot change the Database path, I was forced to change the file names.</li>
<li>After that I was able to restore the Database.</li>
<li>But I was wondering how can the files are still exist if I have dropped the Database. So once the migration activity was completed successfully I started troubleshooting it and here I found something new to learn.</li>
</ol>
<div>
<span style="background-color: yellow;">"<b><u>If you put the Database in Offline state and then drop a Database; you will always find the file associated with the Database present in the defined location</u></b>"</span></div>
<div>
<br /></div>
<div>
Share you comments or any queries below to discuss more.</div>
<div>
<br /></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-10623198783361007132015-08-25T14:34:00.002+05:302015-08-25T14:34:21.229+05:30Error 1814 - Could not Start SQL Server Services<div dir="ltr" style="text-align: left;" trbidi="on">
<div>
Hey Guys,</div>
<div>
<br /></div>
<div>
Some of you might be very well familiar with the below error. Let's see under what circumstances I've faced this error.<br />
<br />
Few months back while I was configuring "AlwaysON" on one of the Server, something went wrong and due to which I've to completely decommission the AlwaysON configuration along with Windows Clustering.<br />
<br />
After doing this, I restated the server and when the server was up what I saw was the SQL Server service was disabled, tried starting the same through Configuration Manager but hard luck I was facing the below error message.<br />
<br /></div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7rNvzHlywq52KrH5oN6SiEpR80DCd05AOMLjkolN7Ex8yANLTB43YnFGE7_7JRdaSWDwUe4MrVsYyfwrvfRMZYv6-XJWF0ldK5S_q40e8VND2BHUkVjsnqlWBGuM8Oq-XyaNKWSiQvSg/s1600/Error-1814.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="Error 1814" border="0" height="256" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7rNvzHlywq52KrH5oN6SiEpR80DCd05AOMLjkolN7Ex8yANLTB43YnFGE7_7JRdaSWDwUe4MrVsYyfwrvfRMZYv6-XJWF0ldK5S_q40e8VND2BHUkVjsnqlWBGuM8Oq-XyaNKWSiQvSg/s640/Error-1814.PNG" title="Error 1814" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Error 1814</b></td></tr>
</tbody></table>
<div>
Let's have a look below into the SQL Server Error log what its was it looking like:<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-I7paUcVYP6BbcgQIRcBzWKUJ7qU2OmN2c4iUEiDbdFIhHhTgyX2q28hJANiMVaRQGA19fZ4VxlRBPDewyJtfpCwRKZ3YfeIhbrEZP4L01UU26YunTOApVB4zykfefTl8eykWzhp32nY/s1600/Error.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="Error Log" border="0" height="109" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj-I7paUcVYP6BbcgQIRcBzWKUJ7qU2OmN2c4iUEiDbdFIhHhTgyX2q28hJANiMVaRQGA19fZ4VxlRBPDewyJtfpCwRKZ3YfeIhbrEZP4L01UU26YunTOApVB4zykfefTl8eykWzhp32nY/s640/Error.PNG" title="Error Log" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Error Log</b></td></tr>
</tbody></table>
<b><u>With these error logs we started troubleshooting</u></b>:<br />
<ol style="text-align: left;">
<li>The error in the SQL Error Log states that <b>due to insufficient disk space "TempDB" was not created</b>.</li>
<li>Now what we were wondering about the error, as it was a new Server and disk was having sufficient space. So insufficient space was out of question.</li>
<li>Just to cross verify I open the My Computer tab and what I saw was <b>only C: drive was reflecting and rest of the drive were not visible</b>.</li>
<li>While installation I have <b>kept the TempDB in D: drive</b>.</li>
<li>Now here comes the twist, let's recollect the above activity of AlwaysON which I was performing. But for some reason I have to decommission it.</li>
<li>During decommissioning I've put the disks into offline state and due to which the disk were not accessible.</li>
<li>So at start of SQL Server if the TempDB is not created, the SQL Server Services will not start.</li>
</ol>
<div>
<b><u>Brief note on System Databases</u></b>:</div>
<div>
<br />
<ol style="text-align: left;">
<li><b>Master, Model, MSDB and TempDB</b> are system databases.</li>
<li>Files of the Master Database is used in start up parameter during the SQL Server instance starts. </li>
<li>So if the Master Database is corrupted SQL Server instance will not start. </li>
<li>Also as it's one of the important properties is it stores the location information for other Database. Therefore <b>Master Database is said to be the heart of SQL Server</b>.</li>
<li>Model Database acts as a template for the other User Database as well as TempDB while creation.</li>
<li>So even if Model Database is corrupted SQL Server instances will not start. As indirectly TempDB will not start and if TempDB will not start SQL Server instance will not start. </li>
</ol>
</div>
<div>
So guys System Database plays an very important role in proper functioning of SQL Server Instance.</div>
<div>
<br /></div>
<div>
Here my question is: <b>What if MSDB is corrupted, will the SQL Server Instance will start? You can comment below.</b><br />
<br />
Thank You Guys.</div>
<div>
Keep Learning and Enjoy Learning!!!</div>
<br /></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-45723100122277491192015-08-12T12:50:00.001+05:302015-10-09T18:23:44.563+05:30SQL Server 2012 Clustering - Part 1<div dir="ltr" style="text-align: left;" trbidi="on">
Dear Friends,<br />
<div>
<br /></div>
<div>
Recently I've configured Clustering as a Disaster Recovery (DR) and High Availability (HA) solution for one of my project. You might find many blogs on this topic explaining the concept and how to configure SQL Clustering.<br />
<br />
Here I will try to explain the same in my terms and keep it as much as simple. And yes it will be from starch i.e. We will first see the how to configure Windows Cluster and then on top of it will configure SQL Server Clustering. It will be a multi part series.<br />
<br />
<b><u>Brief on Clustering</u></b>: <br />
<br />
Cluster can be defined as <b><i>when two independent machine come into existence with each other by sharing the disks in common</i></b>. This is done because, just in case of any disaster on physical machine, it will automatically failover the disks to another machine as the disks are shared between the Servers. This will recover the Database in disaster. So this solution can be used as Disaster Recovery (DR) solution along with High Availability (HA).<br />
<br />
<span style="font-weight: bold;">I.</span><span style="font-weight: bold;"> </span><u style="font-weight: bold;">Step for configuration of Windows </u><b><u>Clustering Using GUI:</u></b><br />
<br />
<b>1.</b> To start with the Windows Clustering; it is mandatory to have the Shared Storage between the Servers (which should be setup by the SAN Administrator).<br />
To verify these disks open Server Manager and verify if the Shared Disks are visible on both the Servers like you can see in the below snapshot.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: left; margin-right: 1em; text-align: left;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiepGzecIMPYL8rc0xyizqRDtT_vNEIm_yMmaywFDXhh-osjDUDNNjRrrfa7r4CDthiJfaXMz7BoA3sw7BMXoz29PaXYe_wnvkLF43wktq5YPPIeRFKp3cJ7b8IGSKM6T6M4kzyiyIE4LI/s1600/1-ServiceManager.png" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img alt="Server Manager GUI" border="0" height="203" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiepGzecIMPYL8rc0xyizqRDtT_vNEIm_yMmaywFDXhh-osjDUDNNjRrrfa7r4CDthiJfaXMz7BoA3sw7BMXoz29PaXYe_wnvkLF43wktq5YPPIeRFKp3cJ7b8IGSKM6T6M4kzyiyIE4LI/s640/1-ServiceManager.png" title="Server Manager GUI" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Server Manager GUI</b></td></tr>
</tbody></table>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<b>2.</b> Now we have to add "<b>Failover Cluster</b>" Roles on the Server. Open "Server Manager" then click "Add Roles And Feature".<br />
<br />
<u>Below snapshot means</u>:<br />
<br />
i. Select Roles-based option.<br />
ii. Select the appropriate Server name.<br />
iii. Click Next<br />
iv. Check "<b>Failover Cluster</b>"<br />
v. Click "Add Feature" then click install<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihr9ub8bgwFqA0CS_5P6QQ7wlA3pcXR0oAsZSgyNHkOIb7Jeldgc7PMuZABkyJg5nKXDVXB-7ehmCJV8iHI-w8Yrig87NTFkSkkUqpGxEsf-JMi5t1LsUEJStHMGBiIGAhjAc4loFgcGk/s1600/1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEihr9ub8bgwFqA0CS_5P6QQ7wlA3pcXR0oAsZSgyNHkOIb7Jeldgc7PMuZABkyJg5nKXDVXB-7ehmCJV8iHI-w8Yrig87NTFkSkkUqpGxEsf-JMi5t1LsUEJStHMGBiIGAhjAc4loFgcGk/s640/1.png" width="582" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRZ4s3MPRFrx35At-JVKL4-H05ACKtPQmGHWFPVt4qK4hvZ6HmAdyfscz-6xcAU1YOTWVAk-xvpVhyphenhypheny6VrCZkrMh7egAM_JVQxHcOmoI6URzinLUT17Uw1fWw_es4eqrozIXmOBKrQuYQ/s1600/2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRZ4s3MPRFrx35At-JVKL4-H05ACKtPQmGHWFPVt4qK4hvZ6HmAdyfscz-6xcAU1YOTWVAk-xvpVhyphenhypheny6VrCZkrMh7egAM_JVQxHcOmoI6URzinLUT17Uw1fWw_es4eqrozIXmOBKrQuYQ/s640/2.png" width="568" /></a></div>
<br />
<b>3</b>. Once the roles is added, go to Service manager and then, Tools and then, Failover Cluster Manager. Open Failover Cluster Manager GUI.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNeLS9E-6_zsiNl3XAp5gc2RM5qMMZKBRa8p5rroXzNQ5JCaENKpuFslODTco71sPZnSDJ_j7rFQwpT5x2SiQzzG83za6PHLJlObAN77uPbf3PyPqhQTpzl415KAW6FKmG9RF_r0avtk4/s1600/2-OpenFOCM.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="125" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNeLS9E-6_zsiNl3XAp5gc2RM5qMMZKBRa8p5rroXzNQ5JCaENKpuFslODTco71sPZnSDJ_j7rFQwpT5x2SiQzzG83za6PHLJlObAN77uPbf3PyPqhQTpzl415KAW6FKmG9RF_r0avtk4/s200/2-OpenFOCM.png" width="200" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiX9pzyoiTEmL0-twaGPN3rronbIfZJZtzsFwDjxHifasWPIIVO3GIxTgawvcJzqMSxxaPzd2UzdJfZHQReL9PSEzmj1HsjRZBZMlR7DUtWrfU5qOTloZGCXsWorJ0nu77SXt0dRIn2ckI/s1600/3-FOCM.PNG" imageanchor="1" style="clear: right; display: inline !important; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="137" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiX9pzyoiTEmL0-twaGPN3rronbIfZJZtzsFwDjxHifasWPIIVO3GIxTgawvcJzqMSxxaPzd2UzdJfZHQReL9PSEzmj1HsjRZBZMlR7DUtWrfU5qOTloZGCXsWorJ0nu77SXt0dRIn2ckI/s320/3-FOCM.PNG" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<b>4.</b> As shown in the below snapshot, Go to extreme Right and click Validate Configuration.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdndjO2d6n01shQRSafocLSZdTF3YqAinq8X623ANGQ0Zc02rvFC0X5ZXq9SNiH3wdD8RzHiNErcBeQ_vtAesqv4pfX_5R9JKrLd4Jha-nMcZPBBSMr1qr5ku8nkIkOhCAWu9i2tkBKwg/s1600/4-Validation.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdndjO2d6n01shQRSafocLSZdTF3YqAinq8X623ANGQ0Zc02rvFC0X5ZXq9SNiH3wdD8RzHiNErcBeQ_vtAesqv4pfX_5R9JKrLd4Jha-nMcZPBBSMr1qr5ku8nkIkOhCAWu9i2tkBKwg/s640/4-Validation.png" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<b>5.</b> Validate Configuration step will basically check Storage, Network, etc. One need to check the recommended setting and validate the configuration.<br />
<span style="color: red;">The Disk will go offline and come back online; if Disk Validation is checked. (Disk validation is basically unchecked when the Cluster is in production.)</span><br />
<br />
<u>Below snapshot means</u>:<br />
<br />
i. Enter the both the Server Names.<br />
ii. Run the recommended setting.<br />
iii. You can view the progress<br />
iv. View the error if any or click finish.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiF1vaRQKboicelCGPweKwehehyTliwTF8W5I5Rv8fd87sMi02WqjopDN9YkRVwGZiBQiWJpmuJEdMT9bh3xvXMRzxY6fXZ8j5FGrmHRsbhbJiB8w-HyskbmVDnXDgIGNVqLizVTvvF6V4/s1600/5-ValPro.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="554" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiF1vaRQKboicelCGPweKwehehyTliwTF8W5I5Rv8fd87sMi02WqjopDN9YkRVwGZiBQiWJpmuJEdMT9bh3xvXMRzxY6fXZ8j5FGrmHRsbhbJiB8w-HyskbmVDnXDgIGNVqLizVTvvF6V4/s640/5-ValPro.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<b>6.</b> Once it is successfully validated, you need to <b><span style="color: #3d85c6;"><u>mention a Windows Cluster Name and an IP Address</u></span> </b>for that Windows Cluster as you can see in the below snapshot. Once you have successfully completed click Finish.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim-sU5EE06PhLNsD4rBcCoKEQHhbDQa_MNkW0AOJPxdyQ5j0x1Z5ZORrLC-izA0VqaD47eEUfnFlcderKeGyOsqDctSfnA7C8tHGI7FcZ-Pj6EWK7VZ5ois1jyFWwnf9tqK4_uaoMQpMg/s1600/6-WC.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="430" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEim-sU5EE06PhLNsD4rBcCoKEQHhbDQa_MNkW0AOJPxdyQ5j0x1Z5ZORrLC-izA0VqaD47eEUfnFlcderKeGyOsqDctSfnA7C8tHGI7FcZ-Pj6EWK7VZ5ois1jyFWwnf9tqK4_uaoMQpMg/s640/6-WC.png" width="640" /></a></div>
<br />
<br />
You can cross verify by cmd command and ping the Windows Cluster name or the IP Address provided above in point 5.<br />
<br />
<span style="font-weight: bold;">II.</span><span style="font-weight: bold;"> </span><u style="font-weight: bold;">Step for configuration of Windows </u><b><u>Clustering Using Power Shell:</u></b><br />
<br />
<b>1.</b> We need to first install the Cluster Failover Management Tools:<br />
<br />
Following commands can be run from the Power Shell:<br />
<br />
<b><i>a. Get-WindowsFeature Failover*</i></b><br />
<br />
<b><i>b. Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools</i></b> --This command will install the Windows Feature Failover tools on the local Server but if you want to install the same on remote Server as well we have to run the following command:<br />
<div>
<br /></div>
<b><i>c. Install-WindowsFeature -Name Failover-Clustering -IncludeManagementTools -ComputerName XYZ</i></b><br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDiOOwwXSveMheSU97ldd_tne1tBLCHiCtzfgNcX8eb1Ei4Wz2ZfdMkdhWY2Cs4_xcZWGpjf95iCocN4FzmlAhvuG4qTG0BTSKol4OhNeeZ4y_w70AgIX2EUs99SImZSXDreJi0lF_gHo/s1600/WC1.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="210" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjDiOOwwXSveMheSU97ldd_tne1tBLCHiCtzfgNcX8eb1Ei4Wz2ZfdMkdhWY2Cs4_xcZWGpjf95iCocN4FzmlAhvuG4qTG0BTSKol4OhNeeZ4y_w70AgIX2EUs99SImZSXDreJi0lF_gHo/s640/WC1.PNG" width="640" /></a></div>
<br />
<b>2.</b> Till now you have installed Failover feature on the Servers, next you have to configure Windows Cluster on these Servers:<br />
<br />
Following commands can be run from the Power Shell:<br />
<br />
<b><i>d. New-Cluster -name SQL_Cluster -Node ABC, XYZ -StaticAddress xxx.xx.xx.xxx</i></b><br />
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4XFtJbqAc_sXQ3MVJjNipJ10WQqzq-2gtwGZL7fwEk77y6uFUnzzdlGu4HYA_o_UWUY7m_aFTTqqB2lpwXTQlzBkYxHnDuTE7GL6B0qvJ_Mfqo5-UVfQ4uF5BMOsjS2jsUHrhVBUuRMw/s1600/WC2.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="76" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4XFtJbqAc_sXQ3MVJjNipJ10WQqzq-2gtwGZL7fwEk77y6uFUnzzdlGu4HYA_o_UWUY7m_aFTTqqB2lpwXTQlzBkYxHnDuTE7GL6B0qvJ_Mfqo5-UVfQ4uF5BMOsjS2jsUHrhVBUuRMw/s640/WC2.PNG" width="640" /></a></div>
<br />
where <b>SQL_Cluster</b> is Windows Cluster Name<br />
<b>ABC</b> and <b>XYZ </b>are the Server Names between which Cluster needs to be installed<br />
<b>xxx.xx.xx.xxx</b> is the static IP Address for Windows Cluster.<br />
<br />
So you have seen how simple is to build Windows Clustering through GUI as well as Poser Shell. This is not just limited as a pre- requisite to SQL Server Clustering but also for Configuring AlwaysON.<br />
<br />
Here's the end of <b>Part 1</b> of SQL Server 2012 Clustering. We have seen how to configure the Windows Server Clustering which is the first step towards configuring SQL Server Clustering.<br />
<br />
Now next will be <a href="http://vikasbsahu.blogspot.in/2015/09/sql-server-2012-clustering-part-2.html" target="_blank"><b><span style="color: blue;">configuring the Quorum for the Cluster</span></b></a>.<br />
<br />
Click here to refer <a href="http://vikasbsahu.blogspot.in/2015/09/sql-server-2012-clustering-part-2.html" target="_blank"><span style="color: blue;"><b>Part 2</b></span></a> and <b><a href="http://vikasbsahu.blogspot.in/2015/09/sql-server-2012-clustering-part-3.html" target="_blank"><span style="color: blue;">Part 3</span></a></b> of this topic.<br />
<br />
Thank you for reading this post. So stay tuned and share your comments too.<br />
<br />
Keep Learning and Happy Learning!!!<br />
<br /></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-89131841296829204432015-08-06T16:00:00.005+05:302015-10-06T14:54:00.309+05:30New Features in SQL Server 2016 - Part 2<div dir="ltr" style="text-align: left;" trbidi="on">
Good Morning Guys,<br />
<br />
As we have seen the <a href="http://vikasbsahu.blogspot.in/2015/07/new-features-in-sql-server-2016-part-1.html" target="_blank"><b><span style="color: blue;">top 3 new features</span></b></a> in SQL Server 2016, here is the continuation of that post. We will see few other enhancements in it.<br />
<br />
<u>Following are the list extending to these top 3 features</u>:<br />
<br />
4. <b><u>Enhancements to AlwaysOn</u></b>:<br />
<br />
As everyone knows the concept of <b><i>AlwaysON</i></b> was introduced by Microsoft in SQL Server 2012. This was introduced due to many limitations in the previous versions of SQL Server on <b>High Availability and Disaster Recovery</b> AlwaysON was introduced for mission critical Enterprise Application.<br />
<br />
It was released for only Enterprise Edition but in SQL Server 2016 it will be released in Standard Edition of course with limited functionality. AlwaysON has also seen enhancement in SQL Server 2014 as well.<br />
<br />
There is <b>1 Primary replica, 8 Secondary replicas and upto 3 replicas</b> can be synchronized compared to 2 secondary replica can be synchronized in 2014.<br />
<br />
<b>Distributed transaction or MSDTC</b> was not supported between Databases on AG in 2012 and 2014; which is now supported in SQL Server 2016.<br />
<br />
We can now <b>perform Full, File, File group or Log backups</b> on the secondary replica Databases, but can't perform Differential Backup.<br />
<br />
For more detail please refer to <a href="http://sqlturbo.com/sql-server-alwayson-availability-groups-cheat-sheet/" target="_blank"><b><span style="color: blue;">this</span></b></a> blog on AlwaysON.<br />
<br />
5. <b><u>Native JSON Support</u></b>:<br />
<br />
<b><i>JSON </i></b>stands for <b>JavaScript Object Notation</b>. This is mainly provided for the developers, who will use this function for organizing the data in more logical manner. It is much more similar to XML already available by SQL Server.<br />
<br />
It format the SQL result set as JSON by adding <b>FOR JSON</b> clause in the From statement. It is very much similar to XML in terms of types as well:<br />
<ul style="text-align: left;">
<li>RAW</li>
<li>AUTO</li>
<li>Explicit<blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;">
<blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;">
<blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;">
<blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;">
<blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;">
<blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;">
<blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;">
<blockquote style="border: none; margin: 0 0 0 40px; padding: 0px;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjNVJd1ZkJ9mTB8JxLdKDuPoBLIcGvQjNmqxDKsXggMrEAMWprWw_1M7Pz_bP1fi2RJoFW4CAX1Ansa7wZTJrBNPorJYq1Dh4GA5SwOkryEFe9p-EIFMsJexSde554Dv-H19M5PkfxM8jo/s1600/JSON.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"></a></blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</blockquote>
</li>
</ul>
Following is the syntax for using the JSON:<br />
<br />
<pre class="scroll" style="background-color: #ced5db; border: 1px solid rgb(221, 221, 221); color: #333333; font-size: 1.2em; line-height: 18.165599822998px; margin-bottom: 12px; overflow: auto; padding: 2px 6px; width: 561px;"><code class="mysql" style="font-size: 1.1em;">SELECT column, expression, column as alias
FROM table1, table2, table3
FOR JSON [AUTO | PATH]</code></pre>
<pre class="scroll" style="background-color: #ced5db; border: 1px solid rgb(221, 221, 221); color: #333333; font-size: 1.2em; line-height: 18.165599822998px; margin-bottom: 12px; overflow: auto; padding: 2px 6px; width: 561px;"><code class="mysql" style="font-size: 1.1em;">
</code></pre>
Figure 1 shows the demo how the JSON script be written from a SQL Table:<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEht_0DCpoMC2YJQr82NrNxx-CS0Ti6jqgx7BbXiv8fonciB37AZL9AUTziSvuyEE7HXituLrmEix9OEPWoXooQppP52m5LSs-I6qJ4hFnQRe4ok7rk5Z7_OsmPCM3r2uCP-UI6k5jF-b-Y/s1600/JSON.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="JSON Code" border="0" height="202" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEht_0DCpoMC2YJQr82NrNxx-CS0Ti6jqgx7BbXiv8fonciB37AZL9AUTziSvuyEE7HXituLrmEix9OEPWoXooQppP52m5LSs-I6qJ4hFnQRe4ok7rk5Z7_OsmPCM3r2uCP-UI6k5jF-b-Y/s400/JSON.png" title="JSON Code" width="400" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Figure 1</b></td></tr>
</tbody></table>
Please refer the <a href="http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/06/08/for-json-clause-in-sql-server-2016-ctp2.aspx" target="_blank"><b><span style="color: blue;">msdn blog here</span></b></a> for more detail on JSON.<br />
<br />
<br />
6. <b><u>Enhancement In-Memory OLTP</u></b>:<br />
<br />
The feature of <i style="font-weight: bold;">In-memory OLTP </i>was introduced in SQL Server 2014 with the name "<b>Hekaton</b>"<i style="font-weight: bold;"> </i>which is derived from a Greek which means '<b>one hundred</b>' i.e. the goal of the project was to see 100x performance appraisal.<br />
<br />
It has a separate query processor engine which is <b>lock free design</b> but at the same time it also maintain the data transnational integrity. Due to which it is different from Pin Table in SQL Server 6.5 release or putting Databases in SSD's (Solid State Disks). Refer this <a href="http://sqlmag.com/sql-server-2014/application-performance-inmemory-oltp-database-engine" target="_blank"><span style="color: blue;"><b>link</b></span></a> to get an idea about the architecture of In-memory OLTP.<br />
<br />
<u>When it has done the debut it came with lots of drawback. And now from these demerits many has been converted to merit. lets see some of them below</u>:<br />
<br />
<b> i.</b> The <b>max table size 256 GB</b> which has changed to 2 TB.<br />
<br />
<b> ii.</b> <b>TDE feature</b> which is the greatest security feature in SQL Server was not supported in 2014 version, but now in 2016 it is supported.<br />
<br />
<b> iii.</b> Another major drawback <b>clause such as IN, EXISTS, DISTINCT,OR,NOT,OUTER JOIN was not supported</b> for query. Now these clauses are supported in SQL Server 2016<span style="font-family: Open Sans, Calibri, Lucida Grande, Tahoma;"><span style="background-color: white; font-size: 12px; line-height: 22.3999996185303px;">.</span></span><br />
<span style="font-family: Open Sans, Calibri, Lucida Grande, Tahoma;"><span style="background-color: white; font-size: 12px; line-height: 22.3999996185303px;"><br /></span></span>
<b> iv. LOB data type such as Varbinary(MAX) or Varchar(MAX) were not supported</b> by the released version but from SQL Server 2016 it is supported.<br />
<br />
<b> v.</b> Similar way <b>Foreign Key was not supported in older version</b> which is not supported in SQL Server 2016.<br />
<br />
There are many other new properties which has been enhanced in the new product of SQL Server 2016 for In-Memory OLTP check this <a href="https://msdn.microsoft.com/en-us/library/dn133186(v=sql.130).aspx" target="_blank"><b><span style="color: blue;">msdn link</span></b></a> for more detail.<br />
<br />
These were the few new and some enhancement done in SQL Serve 2016. Check out <b><a href="https://channel9.msdn.com/Shows/SQL-Unplugged#tab_sortBy_recent" target="_blank"><span style="color: blue;">this link</span></a></b> which has some series of SQL unplugged videos for more details. So guys get ready to work with these exciting new features of SQL Server 2016 for Administrators.<br />
<br />
Also along with features for DBA many more are waiting for BI and Developers guys as well.<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-40315705065654236342015-07-16T20:29:00.000+05:302015-07-17T15:54:38.055+05:30Msg 22049-Error executing extended stored procedure: Invalid Parameter <div dir="ltr" style="text-align: left;" trbidi="on">
Hi Guys,<br />
<br />
It's time to learn from the error which I went today. If you remember I have <a href="http://vikasbsahu.blogspot.in/2014/08/database-backup-script.html" style="color: blue; font-weight: bold;" target="_blank"><span style="color: blue;">posted here</span></a><b style="color: blue;"> </b>a backup script. The purpose of the script is to take Database backup along with few options. The script has undergone through few changes now.<br />
<br />
But here I want to discuss an issue which I faced during implementing this code on one of new environment.<br />
<br />
<b>Scenario</b>:<br />
<br />
<ol style="text-align: left;">
<li>We wanted to configure this Database Backup Script in a new Server. There where few Databases on the Server. What we noticed was one of the Database name in the that Server was having 60 character (It was an Share Point Application Database).</li>
<li>And due to that we were getting this (<span style="font-family: 'Century Gothic', sans-serif; font-size: 10pt;">Msg 8152, Level 16, State 2, Line 1 </span><span style="font-family: 'Century Gothic', sans-serif; font-size: 10pt;">String or binary data would be truncated</span>) error message.</li>
<li>For sake, we had changed the variable to Nvarchar(Max).</li>
<li>After changing this variable there was another error popped up.Error was (Msg 22049, Level 15, State 0, Line 0 Error executing extended stored procedure: Invalid Parameter).</li>
<li>Please see the Figure 1 snapshot.</li>
<li>But in between Step 3 and 4; I was executing the SP from a Job and was unable to see this error message. As some of the error message were truncated. So I ran the SP manually from SSMS and I got to know about this error.</li>
<li>Looking into the error, I came to know there was a problem in my last portion of the code.That means First portion 'To take DB Backup' was running fine and the Second part 'To delete the old Backup' was creating issue as I was using Extended Stored Procedure (i.e <b><span lang="EN-US" style="background: white; font-family: "Verdana","sans-serif"; font-size: 8.5pt; mso-ansi-language: EN-US; mso-bidi-font-family: Calibri; mso-bidi-language: AR-SA; mso-fareast-font-family: Calibri; mso-fareast-language: NL; mso-fareast-theme-font: minor-latin;">dbo.xp_delete_file</span></b>)</li>
<li>Not sure what was the issue after doing little research found an partial solution and then tested the code with some changes in variable and it was successful.</li>
<li>Please see the demo code in Figure 2. </li>
</ol>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjo9vdS8i7UQthkbPIhUonzjQaULsTe462yE2pB795w7TPOZZh2fWoBLcMzkhUzxCmt-ImD4m4UgWXQS-4IPjotZsV6Fy263SKeTJNrXNi2OCcsIx6aUbhr8fvB_GFgnTMKIY8NSmCXZ0M/s1600/Error+22049-Max.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><b><img alt="Msg 22049-Error executing extended stored procedure: Invalid Parameter " border="0" height="121" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjo9vdS8i7UQthkbPIhUonzjQaULsTe462yE2pB795w7TPOZZh2fWoBLcMzkhUzxCmt-ImD4m4UgWXQS-4IPjotZsV6Fy263SKeTJNrXNi2OCcsIx6aUbhr8fvB_GFgnTMKIY8NSmCXZ0M/s320/Error+22049-Max.PNG" title="Msg 22049-Error executing extended stored procedure: Invalid Parameter " width="320" /></b></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Figure 1</b></td></tr>
</tbody></table>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8_cjC0K8WQPu07NUmHUj0txOkRVbBvzhk_m3ZiLc8luWfpeljFtM7Vk_-VJUeMKNxM5Iz__2RoSv5Aj4HwMR94BOkuFznfHfny1Q0bqPMh8MbaRsyeSFj9iiWqAV5yrmwqF06uS9pWxk/s1600/Error+22049-Fixed.PNG" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="110" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8_cjC0K8WQPu07NUmHUj0txOkRVbBvzhk_m3ZiLc8luWfpeljFtM7Vk_-VJUeMKNxM5Iz__2RoSv5Aj4HwMR94BOkuFznfHfny1Q0bqPMh8MbaRsyeSFj9iiWqAV5yrmwqF06uS9pWxk/s320/Error+22049-Fixed.PNG" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Figure 2</b></td></tr>
</tbody></table>
Can you find any difference in Figure 1 and Figure 2?? (Of course apart from the Error)<br />
<br />
The solution for the error was @path variable in the above code has datatype Nvarchar(Max) and if that is called from the extended Stored Procedure like in our case from <span lang="EN-US" style="background-attachment: initial; background-clip: initial; background-color: white; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; font-family: Verdana, sans-serif; font-size: 8.5pt;"><b>dbo.xp_delete_file </b></span>it throws the above error.<br />
<br />
But if we pass the datatype as Nvarchar(<same value="">) like in <b>Figure 2 </b>and passed to Extended Stored Procedure the error is solved. </same><br />
<br />
<b>So do remember if we are passing any variable to Extended Stored Procedure do defined a fixed value to the variable</b>.<br />
<br />
Keep Learning and Enjoy Learning!!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-91004801350002277292015-07-12T18:02:00.002+05:302015-10-06T14:54:10.634+05:30New Features in SQL Server 2016 - Part 1<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
Hello Friends,<br />
<!--[if !supportLineBreakNewLine]--><br />
<!--[endif]--></div>
<div class="MsoNormal">
I've given a short introduction to you on SQL Server 2016 <span id="goog_665125842"></span><a href="http://vikasbsahu.blogspot.in/2015/07/introduction-to-sql-server-2016.html" target="_blank"><span style="color: blue;"><b>here</b></span></a> <span id="goog_665125843"></span>now, it's time to learn some of the much awaited new features for DBA's. You can consider this a qualified interview question as well.<br />
I'll try to explain these features one by one here:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
1. <b><u>Stretch Database</u>: </b> </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This is the most interesting feature introduced by Microsoft in SQL 2016. The word "Stretch" means expand, which means you can expand your local (on-premise) Database to Azure (Cloud). So here we will see the combination of on-premise + Azure in one Database.</div>
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOgOuxasztzZSvoqYYcQCvp0HqGheWgeCs_tohzngula6IlP1EcUqTtt8EB990xCjWMTkxK1weEdDkuypApI515r7QULU_1B82YN5J10RyuuJPv8jJZB6eNwEbgnPp3DSTFk0LyCL22nU/s1600/Strch+Db.png" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img alt="Stretch Database" border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiOgOuxasztzZSvoqYYcQCvp0HqGheWgeCs_tohzngula6IlP1EcUqTtt8EB990xCjWMTkxK1weEdDkuypApI515r7QULU_1B82YN5J10RyuuJPv8jJZB6eNwEbgnPp3DSTFk0LyCL22nU/s320/Strch+Db.png" title="Stretch Database" width="223" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Stretch Database</b></td></tr>
</tbody></table>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
By default, this feature is disabled. We need to enable through <b>SP_Configure 'Remote Data Archive'</b>. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Once it is enabled, you can configure this option under; </div>
<div class="MsoNormal">
<b>Right Click on <i>Database</i>; Next <i>Tasks</i>; Next '<i>Enable Database for Stretch...</i></b>' </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
You need to supply the Azure Credential and select the table which you want to move. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
The whole purpose of <b><i>Stretch Database</i></b> is, we can move the old historic data to Azure by remote query processing. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
No changes is required from Application side. If a query is executed from an Application it will hit the on-premise Database and this on-premise Database will fetch the data from Azure and return the result set. (Only if that particular table data is moved to Azure)</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Backup and Restore procedure of these Database would be little different from regular one. If you follow the regular 'Full' backup it will only consider the on-premise Database.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This would be consider as a good option to your environment because <u>you can save the Hardware cost, Backup time would be less, Since historic data are moved out query processing for the current data will be improved</u>. Also most important <u>it's not a pre-requisite to have knowledge on SQL Azure</u> so any DBA who din't had luck to worked on SQL Azure should not worry.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
There would be little <u>Performance impact</u> because it will fetch the data from on-premise as well as Azure. This will be the major drawback of this feature.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This feature must be only available in Enterprise Edition (Not sure yet). </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This was just a brief note about this Database. More information about Stretch Database are <b><a href="https://msdn.microsoft.com/en-us/library/dn935011.aspx" target="_blank"><span style="color: blue;">available here on msdn</span></a></b>. You can also refer <b><a href="https://dbafromthecold.wordpress.com/2015/06/15/sql-server-2016-stretch-databases/" target="_blank"><span style="color: blue;">this site</span></a></b>, it provides each and every details with GUI about <b><i>Stretch Database</i></b>.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
2. <b><u>Always Encrypted</u>: </b></div>
<div class="MsoNormal">
<b><br /></b></div>
<div class="MsoNormal">
Moving ahead with this interesting feature of Stretch Database, you might be definitely having question about data security, when there is any data movement from on-premise to Azure or vice-versa.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
With this let me introduce to the next exciting feature of SQL 2016 which is <i><b>Always Encrypted</b>.</i><br />
You might be well aware of TDE (Transparent Data Encryption) which was introduced by SQL Server 2008 in Enterprise Edition. It's use is to encrypt the data at rest with the help of certain keys (Master Key and Certificate). But as the data in Database is encrypted at the rest, the data is very much transparent; when the data is on fly (i.e. when data is moved from Application to Database or vice-versa). So man in middle attack is easily possible.This was the main drawback for TDE.<br />
<br />
<u>To overcome this drawback Microsoft has introduced new or can say extended security feature of TDE i.e "<b><i>Always Encrypted</i></b>" feature in SQL Server 2016.</u><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-BPgdkO-a0z-RJPjkmyZhyphenhyphenQEK6ztUUIQVAoKAJfTivaYwjoD2CN9MAHn_I2Jsg_9t_fmUSnhKvVUIkdIxvpDrSHv8LdUWW1Tj3JFkvMn0DoGYEkUYIKsUhqGIoXJp0iWKnDY8b_0ZTc0/s1600/Always+Encryp.png" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img alt="Always Encrypted" border="0" height="216" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg-BPgdkO-a0z-RJPjkmyZhyphenhyphenQEK6ztUUIQVAoKAJfTivaYwjoD2CN9MAHn_I2Jsg_9t_fmUSnhKvVUIkdIxvpDrSHv8LdUWW1Tj3JFkvMn0DoGYEkUYIKsUhqGIoXJp0iWKnDY8b_0ZTc0/s320/Always+Encryp.png" title="Always Encrypted" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Always Encrypted</b></td></tr>
</tbody></table>
Basically there is an enhancement in ADO.NET library which will protect the data in rest as well as motion. <br />
<br />
So basically, the keys and certificate which we have create in SQL Server has to be deployed during application creation. This deployment of the Keys and Certificated will be handled by the ADO.NET library.<br />
<br />
The figure on right will give an fair idea about the same. <u>The data during fly will be encrypted and decryption will take place on the client side</u> with the help of keys present in ADO.NET library.<br />
<br />
This was just an overview on <i><b>Always Encrypted</b>,<b> </b></i>you can find more details <b><a href="http://blogs.msdn.com/b/sqlsecurity/archive/2015/06/04/getting-started-with-always-encrypted.aspx" target="_blank"><span style="color: blue;">here</span></a></b> in msdn blog which is explained in depth with GUI. This is a very important feature for any organisation which handles critical data in there Database w.r.t to Auditing and Compliance. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
3. <b><u>PloyBase</u>:</b> </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This one is the most important and exciting feature Microsoft has introduced ever and also the unexpected. Before this version any RDBMS product has the ability to interact with any other RDBMS (Through Linked Server).<br />
But for the <u>first time in SQL Server history it will interact with Non-RDBMS product</u> as well. If you have gone through my previous post <a href="http://vikasbsahu.blogspot.in/2015/07/ms-sql-server-along-with-hadoop.html" target="_blank"><b><span style="color: blue;">here</span></b></a>, I already introduced to you about this Non-RDBMS product i.e. Hadoop. <br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: right; margin-left: 1em; text-align: right;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp9Ea7gKyJ4jiuEBClR36JmMaKquyrT3K9FvvqSAyHtJivmXcNu2F7xJIhvOupd-GwYX6s1gurwJyNN3xgpGrhrL2I_R1xjoF-4OIV3cYe-bBxNQWD6fhk7hy9p_qTB8H_UHWeqYAKBM4/s1600/PolyBase.png" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img alt="PloyBase" border="0" height="184" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp9Ea7gKyJ4jiuEBClR36JmMaKquyrT3K9FvvqSAyHtJivmXcNu2F7xJIhvOupd-GwYX6s1gurwJyNN3xgpGrhrL2I_R1xjoF-4OIV3cYe-bBxNQWD6fhk7hy9p_qTB8H_UHWeqYAKBM4/s200/PolyBase.png" title="PloyBase" width="200" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>PloyBase</b></td></tr>
</tbody></table>
Looking into the future of Database world, Microsoft has also considered to introduce this feature in there upcoming product.<br />
Earlier <u>Apache Sqoop (It is a tool for data transfer between Hadoop Cluster and Relational Database) has the ability to interact with other RDBMS application</u> but it has few limitations.<br />
These limitations can be overcome with this new feature in SQL Server 2016.<br />
<br /></div>
<div class="MsoNormal">
If you have both SQL Server and Hadoop Cluster in your environment now it will not be necessary to learn Hadoop query to fetch data it can be easily just like <a href="http://vikasbsahu.blogspot.in/2014/07/AboutLinkedServers.html" target="_blank"><b><span style="color: blue;">Linked Server</span></b></a> in SQL Server 2016.<br />
<br />
How it will work?<br />
<br />
<ul style="text-align: left;">
<li>Microsoft has introduced two new SQL Services which will be installed during installation of SQL Server. (Uncheck the box if there is no need of this Service)</li>
<li>We need to start by configuring SP_Configure parameter 'Hadoop Connectivity',0; by passing an appropriate value.</li>
<li><span style="background-color: #f6f6f6; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px;">You can use the following configuration values:</span></li>
</ul>
<br />
<table border="0" cellpadding="2" cellspacing="0" style="background-color: #f6f6f6; color: black; font-family: 'Trebuchet MS', Trebuchet, Verdana, sans-serif; font-size: 13px; width: 559px;"><tbody>
<tr><td valign="top" width="49">0</td><td valign="top" width="508">no Hadoop connectivity (default)</td></tr>
<tr><td valign="top" width="49">3</td><td valign="top" width="508">Enable connectivity to Cloudera CDH for Linux</td></tr>
<tr><td valign="top" width="49">4</td><td valign="top" width="508">Enable connectivity to Hortonworks Data Platform for Windows Server (HDP), HDInsight on Analytics Platform System, or HDInsight’s Microsoft Azure blob storage</td></tr>
<tr><td valign="top" width="49">5</td><td valign="top" width="508">Enable connectivity to Hortonworks Data Platform (HDP)for Linux</td></tr>
</tbody></table>
</div>
<div class="MsoNormal">
<br />
Kindly refer to this <a href="http://ms-olap.blogspot.in/2015/05/polybase-in-sql-server-2016-ctp-2.html" target="_blank"><b><span style="color: blue;">link</span></b></a> you will get a complete details about this feature.<br />
<br />
These are just an overview of three new feature which is introduced by Microsoft in there new version of SQL Server 2016. We will see the another few features in our next post.<br />
<br />
Till then stay tuned for more updates and feel free to share your comments here. Also do like and share if you liked the post.<br />
<br />
Check <a href="http://vikasbsahu.blogspot.in/2015/08/new-features-in-sql-server-2016-part-2.html" target="_blank"><b><span style="color: blue;">this link for Part 2</span></b></a> of this post.</div>
<div class="MsoNormal">
<br />
Keep Learning and Enjoy Learning!!!</div>
<div class="MsoNormal">
<br /></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-90802309324244295662015-07-09T16:55:00.001+05:302015-07-12T11:57:56.797+05:30Introduction to SQL Server 2016<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
Hello Guys,<br />
<!--[if !supportLineBreakNewLine]--><br />
<!--[endif]--></div>
<div class="MsoNormal">
As many of you must be aware under Microsoft flagship, they
have announced SQL Server 2016 in May’15 during <a href="https://channel9.msdn.com/Events/Ignite/2015/FND1551" target="_blank"><span style="color: blue;"><b>Microsoft Ignite conference</b></span></a>. So,
the <b style="background-color: yellow;">latest</b><b><span style="background-color: yellow;"> avail</span><span style="background-color: yellow;">able version of MS SQL Server 2016 is CTP 2.1</span></b> (Community Technology
Preview) from Jun’16. Soon (Somewhere in 2016; not yet confirm) the general
version RTM (Release To Manufacture) would be available. Also the code name for this version is yet to release.
</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Tester can <a href="http://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016" target="_blank"><b><span style="color: blue;">download SQL Server 2016 CTP 2.1 from official Microsoft</span></b></a>.
</div>
<div class="MsoNormal">
<br /></div>
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmi3jegzfiO92jNDygVUrjgNoUFaR9jwkOFPr165FStbQG2VcOVlaEzQEHrdMZsby6ZltnDLpUegveyHwDta2rA3k66RK3IbEj647zjPNFLSDZrTdr9jWH7CKuK_3yykDsgcDncqmfeb4/s1600/sql-server-2016-ssms.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="SQL Server 2016" border="0" height="209" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmi3jegzfiO92jNDygVUrjgNoUFaR9jwkOFPr165FStbQG2VcOVlaEzQEHrdMZsby6ZltnDLpUegveyHwDta2rA3k66RK3IbEj647zjPNFLSDZrTdr9jWH7CKuK_3yykDsgcDncqmfeb4/s320/sql-server-2016-ssms.png" title="SQL Server 2016" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>Introducing SSMS for SQL Server 2016</b></td></tr>
</tbody></table>
<div class="MsoNormal">
<u><br /></u>
<u>Following is a brief note on the how the Software is released
in phases</u>:</div>
<ul style="text-align: left;">
<li><b style="text-indent: -0.25in;">CTP</b><span style="text-indent: -0.25in;"> > It stands for </span><b style="text-indent: -0.25in;"><i><u>Community
Technology Preview</u></i></b><span style="text-indent: -0.25in;">. CTP version is released before a newly </span><span style="text-indent: -0.25in;">developed software is roll out in the market. It is releases to improve the
software or to fix </span><span style="text-indent: -0.25in;">bugs present.</span></li>
<li><b style="text-indent: -0.25in;">RTM</b><span style="text-indent: -0.25in;"> > It stand for </span><b style="text-indent: -0.25in;"><i><u>Release
To Manufacture</u></i></b><span style="text-indent: -0.25in;">. This is the first official version which released to
the customers or clients for use.</span> </li>
<li><b style="text-indent: -0.25in;">CU</b><span style="text-indent: -0.25in;"> > It stands for </span><b style="text-indent: -0.25in;"><i><u>Cumulative
Updates</u></i></b><span style="text-indent: -0.25in;"> which keep on releasing after RTM or SP is released. Its main purpose is to fix the bug found in the product.</span></li>
<li><b style="text-indent: -0.25in;">SP</b><span style="text-indent: -0.25in;"> > It stands for </span><b style="text-indent: -0.25in;"><i><u>Service
Pack</u></i></b><span style="text-indent: -0.25in;">. Bunch of CU forms a SP and it is cumulative. That means say there are two SP’s are released SP1 and SP2. You can directly apply SP2 no need
to apply SP1 and then SP2.</span></li>
</ul>
<div class="MsoNormal">
<a href="http://sqlserverbuilds.blogspot.in/" target="_blank"><b><span style="color: blue;">Click here</span></b></a> to find more on release date and the version
number for overall Microsoft product available from SQL Server 7.0 till SQL
Server 2016.</div>
<div class="MsoNormal">
<u><br /></u></div>
<div class="MsoNormal">
With this information now we will see what new features are
available in MS SQL Server 2016. The following diagram will give you an idea about some of the new feature in SQL Server 2016.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhY1oBwopYrNNeNlwhRXBII_b_jMJwXJNtkHkYEnrZpFr4uOd0OJCQmiFQ_cycwZuI5arUu1o6bWzfjT3duCjNzqzrfmu7v_n9mjmRVqM8Q3cDWTHJwQEx5MWfkZdUBUtJbTJTJH94fMco/s1600/2016.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img alt="New Features in SQL Server 2016" border="0" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhY1oBwopYrNNeNlwhRXBII_b_jMJwXJNtkHkYEnrZpFr4uOd0OJCQmiFQ_cycwZuI5arUu1o6bWzfjT3duCjNzqzrfmu7v_n9mjmRVqM8Q3cDWTHJwQEx5MWfkZdUBUtJbTJTJH94fMco/s640/2016.png" title="New Features in SQL Server 2016" width="640" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b>New Features of SQL Server 2016</b></td></tr>
</tbody></table>
<br />
We will see in detail about these new features of SQL Server 2016 on next post here. Till then........<br />
<br />
Keep Learning and Enjoy Learning!!!<br />
<br />
Thank you.</div>
<div class="MsoListParagraph">
<br /></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-45551416680553601762015-07-01T14:31:00.000+05:302015-07-12T20:45:42.046+05:30MS SQL Server Along With Hadoop<div dir="ltr" style="text-align: left;" trbidi="on">
Thank you Guys!!!<br />
<br />
Your appreciation and encouragement on my post of "BIG DATA" under the title "<a href="http://vikasbsahu.blogspot.in/2015/06/apart-from-sql-server.html" target="_blank"><b>Apart From SQL Server...</b></a>".<br />
<br />
From now onward we will learn both "<u>SQL Server</u>" and "<u>Hadoop</u>" Administration under this same blog of mine. So definitely first I'll update my blog title to "<b><a href="http://vikasbsahu.blogspot.in/2014/06/blog.html" target="_blank">All about MS SQL Server And Hadoop Administrator</a></b>" rather than "<u><b><a href="http://vikasbsahu.blogspot.in/2014/06/blog.html" target="_blank">All about MS SQL Server DBA</a></b></u>".<br />
Here I will share my real world experiences about MS SQL Server as well as my learning experience about Hadoop Administrator.<br />
<br />
We saw a tremendous revolution in past decade around us with the help of IT and of course there is much more in coming years. If you look back there were hardly or you can count the e-commerce sites in your finger tips but today there are dozens of e-commence going around. These e-commerce business will generate bunch of Structure as well as unstructured data. Structure data's can be easily managed by RDBMS but to maintain and improve these unstructured data we need a large set of Cluster structure Servers which will handle these type of data. Most of us today are not aware to this technology or just have heard the term "BIG DATA". <br />
<br />
I've already posted <b><a href="http://vikasbsahu.blogspot.in/2015/06/apart-from-sql-server.html" target="_blank">here</a> </b>that how I came to know about this technology. Now, here is the reason why I'm keen to learn Hadoop.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwFF2WB7jBDnEs9bKBtXfhn5GvP1ddwRtZ2phV-4SiG-CImpgLVoXQDJjVaJpfc0GOjmoSluAcV_jFNLnZDL1Ix-qUG8R0sGCqqvi5lutnnJw-RROHxs-Si-U0A1hsa327RsQ_KJGvUKY/s1600/Big+Data.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhwFF2WB7jBDnEs9bKBtXfhn5GvP1ddwRtZ2phV-4SiG-CImpgLVoXQDJjVaJpfc0GOjmoSluAcV_jFNLnZDL1Ix-qUG8R0sGCqqvi5lutnnJw-RROHxs-Si-U0A1hsa327RsQ_KJGvUKY/s1600/Big+Data.jpg" /></a></div>
<br />
<br />
As a MS SQL Server DBA I always wanted to learn some different related technology as well. Since I'm having knowledge on RDBMS and learning Oracle which is also again an RDBMS. So thought of learning Non-RDBMS product and I landed to learn Hadoop.<br />
<br />
So those you want to learn SQL Server or Hadoop or both can bookmark my link. I'm afraid as a new beginner to Hadoop I might go wrong sometimes but trust me I'll try to avoid such mistakes by understand the topic well before writing any blog on Hadoop.<br />
<br />
Feel free to correct me if I'm wrong on any part not just with Hadoop but also with MS SQL Server posts.<br />
<br />
So guys, let's start learning a new upcoming technology along with our own MS SQL Server.<br />
<br />
Keep Learning and Enjoy Learning!!!<br />
<br />
Thank you!!!<br />
<br /></div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-24532878167680072232015-06-26T12:38:00.003+05:302015-10-06T14:54:42.110+05:30Auto Growth Option in SQL Server Database Part-2<div dir="ltr" style="text-align: left;" trbidi="on">
<span style="font-family: inherit;">Good Morning Friends,</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">This post is the continuation of <a href="http://vikasbsahu.blogspot.in/2015/06/hi-guys-thanks-lot-friends-for-your.html" target="_blank"><b>Auto Growth Part 1</b></a>.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><u><b><a href="http://vikasbsahu.blogspot.in/2015/06/hi-guys-thanks-lot-friends-for-your.html" target="_blank">Part 1</a></b></u> we have seen the options available of Auto Growth in SQL Database and the parameters available. Now here we will see how to select Parameters.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">For "<b>Parameter 2</b>" easily we can set to "<b>Unlimited</b>" because no one will stop the growth of there Database. So it is set to "<b>Unlimited</b>" (It will grow until your disk get full or in simple terms you can say limited to your Drive size).</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">For "<b>Parameter 1</b>" best I will select "<b>Growth in MB</b>". </span><span style="font-family: inherit;">Following are the cases I'll try to explain while selecting this option:</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b><u>Case 1</u></b>:</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">1. Suppose I've a Database Clis3, which has one Data and one Log File with size is 50 and 10 GB respectively.</span><br />
<span style="font-family: inherit;">2. Growth for Log file is set to 100 MB. Recovery model in Full recovery mode.</span><br />
<span style="font-family: inherit;">3. Suppose there is a Bulk transaction in the Clis3 Database. Obviously the Log file will capture each and every transaction. SO the log file will start growing.</span><br />
<span style="font-family: inherit;">4. First it will start utilizing all the available VLF (Virtual Log File) up to 10 GB. Once it is full it will add 100 MB to Log file to write the transactions.</span><br />
<span style="font-family: inherit;">5. <b>Now total Log size is 10.10 GB</b>.</span><br />
<span style="font-family: inherit;">6. Once this 100 MB is also full it will add 100 MB more and so on. It will be done until the transaction is completed.</span><br />
<span style="font-family: inherit;">7. <b>So now total Log size is 10.20 GB.</b></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Here SQL Server is adding a defined amount of space (i.e. 100 MB) to the log file. This will indirectly control the growth of Log file.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b><u>Case 2</u></b>:</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">1. Suppose for same Database Clis3, which has one Data and one Log File with size is 50 & 10 GB respectively.</span><br />
<span style="font-family: inherit;">2. Growth for Log file is set to 10 percent. Recovery model in Full recovery mode.</span><br />
<span style="font-family: inherit;">3. Suppose there is a Bulk transaction in the Clis3 Database. Obviously the Log file will capture each and every transaction. SO the log file will start growing.</span><br />
<span style="font-family: inherit;">4. First it will start utilizing all the available VLF up to 10 GB. Once it get full it will add 1024 MB (10% of 10 GB) to Log file to write the transactions.</span><br />
<span style="font-family: inherit;">5. <b>Now total Log size is 11 GB.</b></span><br />
<span style="font-family: inherit;">6. Once this 1 GB is also full it will add 1127 MB more (10% of 11 GB) and so on. It will be done until the transaction is completed.</span><br />
<span style="font-family: inherit;">7. <b>So now total Log size is 12.1 GB.</b></span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Here SQL Server is adding 10 percent of the current Log Size. Which means the size will vary depending upon the current Log File.</span><br />
<span style="font-family: inherit;"><br /></span>
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVxvc7Q_KQVYCK0OnbuH0yFhuIwnQit1nWxe21R6dH7KoMyK6xbdka4l_Pas_7-QxRsJ_ohzM-DqG-ATD5yem3MVn4_KWhK1q0lNawjx7v5FYt953Sgn30ivbVBeW9aMzVLOCw7kksRwA/s1600/Auto-Growth-1.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><span style="font-family: inherit;"><img alt="Auto Growth" border="0" height="246" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVxvc7Q_KQVYCK0OnbuH0yFhuIwnQit1nWxe21R6dH7KoMyK6xbdka4l_Pas_7-QxRsJ_ohzM-DqG-ATD5yem3MVn4_KWhK1q0lNawjx7v5FYt953Sgn30ivbVBeW9aMzVLOCw7kksRwA/s320/Auto-Growth-1.png" title="Auto Growth" width="320" /></span></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b><u><span style="font-family: inherit; font-size: small;">Ideal Setting for Auto Growth</span></u></b></td></tr>
</tbody></table>
<span style="font-family: inherit;">From the above image ideally for large Databases we can set for Auto Growth.</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;"><b><u>Case study for the above two cases</u></b>:</span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">1. Log File in Case 1 will have 10.20 GB at the end whereas;</span><br />
<span style="font-family: inherit;">2. Case 2 will have 12.10 GB at the end.</span><br />
<span style="background-color: yellow; font-family: inherit;"><b>3. That means Log file will utilize 1.9 GB of unnecessary space from the disk which is unwanted.</b></span><br />
<span style="background-color: yellow; font-family: inherit;"><b>4. Hence proves for larger Database it is good to define "Parameter 1" to "Growth in MB's" as it will avoid unnecessary utilization of disk space.</b></span><br />
<div>
<span style="font-family: inherit;"><br /></span>
<br />
<div class="MsoNormal" style="background-color: white; line-height: 18.2000007629395px;">
<span style="font-family: inherit;">Keep in touch... and Happy Learning....</span></div>
<div class="MsoNormal" style="background-color: white; line-height: 18.2000007629395px;">
<span style="font-family: inherit; line-height: 18.2000007629395px;">I appreciate and thank you for reading this post. :) </span></div>
</div>
<div class="MsoNormal" style="background-color: white; color: #666666; font-family: 'Trebuchet MS', Trebuchet, sans-serif; font-size: 13px; line-height: 18.2000007629395px;">
<span style="line-height: 18.2000007629395px;"><br /></span></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-19842346668954323482015-06-25T12:16:00.000+05:302015-10-06T14:54:23.851+05:30Auto Growth Option in SQL Server Database Part-1<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="text-align: left;">
<span style="font-family: inherit;">Hi Guys,</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">Thanks a lot friends for your wishes for my certification completion Blog <a href="http://vikasbsahu.blogspot.in/2015/06/ms-sql-2012-certification.html" target="_blank"><b>here</b></a>.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">There was a discussion couple of days back with one of my colleague DBA about the "<a href="https://msdn.microsoft.com/en-us/library/ms180254(v=sql.110).aspx" target="_blank"><b>Auto growth</b></a>" option in SQL Server Database.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">This topic came into consideration when one of <u>my re-indexing job got failed due to less disk space</u> on the log drive. As we are aware of rebuild requires sufficient amount of disk space for the log file growth (We will discuss on separate post "Behind the scene- while Re-indexing").This will be a two part series.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">We can check the "Auto growth" option under </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><b>Right Click on Database, Next Properties ,Next Files, Next Autogrowth / Maxsize Column</b>. </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0_mmCTPm9ykPVwEGd5MlDAYYw5D33Of2TaKE3w7PuasHm-1_lit-KO0azkdIbCSW-szW6tJoIQCMo_3DAGGr055UwYvjS-6BjTLrt3pl7AEutr0EB8DpSdgM4NDuRjI28_9792oCAw3I/s1600/Auto-Growth.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><span style="color: black; font-family: inherit;"><img alt="Auto Growth GUI" border="0" height="228" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh0_mmCTPm9ykPVwEGd5MlDAYYw5D33Of2TaKE3w7PuasHm-1_lit-KO0azkdIbCSW-szW6tJoIQCMo_3DAGGr055UwYvjS-6BjTLrt3pl7AEutr0EB8DpSdgM4NDuRjI28_9792oCAw3I/s400/Auto-Growth.png" title="Auto Growth GUI" width="400" /></span></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;"><b><span style="font-family: inherit; font-size: small;">Auto- Growth Properties</span></b></td></tr>
</tbody></table>
</div>
<div style="text-align: left;">
<span style="font-family: inherit;">Here you will see depending upon total number of Data and Log Files you have in your Database. </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">By default, it will inherited the value from Model Database but the Log growth will limit up to 2,097,152 MB (2 TB). So now the question is should we limit this to by default value? or we should change the setting? </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">The answer is "<b><u>It Depends</u></b>" (Just like most of the answers in SQL Server). Well, it depends up to scenario to scenario. There are many factors come into consideration before changing the value in Production environment.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">1. How frequently is my log growing?</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">2. What are the operations are happening in my Database?</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">3. I have multiple Log files in different Drive. Still my log file will grow?</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">4. Do we take Log backups?</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">What triggers to grow the Log file rapidly:</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">a. If your recovery model is on "Full" and there is a bulk operation such as BCP it will capture each and every transaction in log. </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">b. Even in rebuilding operations the log files will grow rapidly.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">c. Recovery model is "Full" and there is no Log Backups taken regularly. </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">So as you can see and as I said it depends upon many factors. So before changing anything in Production environment you should be well aware of all the scenarios.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">Following are the parameters we need to set in "Auto Growth" for a Database?</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">1. We can set this option for both Data and Log File by two types:</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><span class="Apple-tab-span" style="white-space: pre;"> </span>a. In Percent</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><span class="Apple-tab-span" style="white-space: pre;"> </span>b. In Megabytes</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">2. We can set the Maximum File Size to</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><span class="Apple-tab-span" style="white-space: pre;"> </span>a. Limited</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><span class="Apple-tab-span" style="white-space: pre;"> </span>b. Unlimited</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">With the help of below query we can find out this setting for all the Databases:</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">--auto growth percentage for data and log files</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">Select DB_NAME(files.database_id) database_name, files.name logical_name, </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">CONVERT (numeric (15,2) , (convert(numeric, size) * 8192)/1048576) [file_size (MB)],</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">[next_auto_growth_size (MB)] = case is_percent_growth</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"> when 1 then CONVERT(numeric(18,2), (((convert(numeric, size)*growth)/100)*8)/1024)</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"> when 0 then CONVERT(numeric(18,2), (convert(numeric, growth)*8)/1024)</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">end,</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">is_read_only = case is_read_only </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"> when 1 then 'Yes'</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"> when 0 then 'No'</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">end, </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">is_percent_growth = case is_percent_growth </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"> when 1 then 'Yes'</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"> when 0 then 'No'</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">end, </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">physical_name</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">from sys.master_files files</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">where files.type in (0,1)</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">and files.growth != 0</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">Now as a DBA needs to set these parameters after knowing the behavior of the Database in given environment. By default it will inherit the values from your Model Database.</span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">We will continue in the <a href="http://vikasbsahu.blogspot.in/2015/06/auto-growth-option-in-sql-server.html" target="_blank"><b><span style="color: blue;">next post Part 2</span></b></a> the cases which best suits for parameter 1. </span></div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div style="text-align: left;">
<span style="font-family: inherit;">Just to share with you last year on <a href="http://vikasbsahu.blogspot.in/2014/06/blog.html" target="_blank"><b>22nd June'14</b></a> I started blogging and it's now 1 Year. Special Thanks to <span style="background-color: white; color: #666666; line-height: 18.2000007629395px;"><b><a href="http://akhilesh-humbe.blogspot.in/" target="_blank">Akhilesh Humbe</a></b></span> for the guidance and Thank you friends for reading my post. </span></div>
<div style="text-align: left;">
If you have any suggestion to improve this blog feel free to comment. Suggestion are most and always welcome. </div>
<div style="text-align: left;">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal" style="background-color: white; line-height: 18.2000007629395px;">
<span style="font-family: inherit;">Keep in touch... And Happy Learning....</span></div>
<div class="MsoNormal" style="background-color: white; line-height: 18.2000007629395px;">
<span style="font-family: inherit;"><br /></span></div>
<div class="MsoNormal" style="background-color: white; line-height: 18.2000007629395px;">
</div>
<div class="MsoNormal" style="background-color: white; line-height: 18.2000007629395px;">
<span style="line-height: 18.2000007629395px;"><span style="font-family: inherit;">I appreciate and thank you for reading this post.</span></span></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-38740078960663930582015-06-19T17:14:00.002+05:302015-07-13T18:46:46.874+05:30MS SQL 2012 Certification<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="MsoNormal">
From past couple of weeks I was preparing for exam 70-461
(Querying Microsoft SQL Server 2012) certification after completing 70-462
(Administering Microsoft SQL Server 2012 Databases) and I got a break through
last week when I cleared my exam. So YES!!!!! I've completed 2 Levels for a
MCSA (Microsoft Certified Solution Associate) and now I am MCP (Microsoft
Certified Professional).</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Just because of your encouragement it boosted me to go ahead
and complete this certification. A biggggg thank you to you all and special
thanks to Sanjeevi K for his guidance. </div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
As I maintain this blog for SQL Server and since now I have
fair idea now about these exams so thought of sharing this with you so that I
can guide you guys if you are planning for these certifications.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
For those who don’t have much idea about how to give
certification below is the simple procedure:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoListParagraphCxSpFirst" style="margin-left: 13.5pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -13.5pt;">
<!--[if !supportLists]-->1.<span style="font-size: 7pt; font-stretch: normal;">
</span><!--[endif]-->Go to the Microsoft site</div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 13.5pt; mso-add-space: auto; text-indent: -13.5pt;">
<a href="https://www.microsoft.com/learning/en-in/exam-70-461.aspx"><span style="color: black;">https://www.microsoft.com/learning/en-in/exam-70-461.aspx</span></a></div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 13.5pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -13.5pt;">
<!--[if !supportLists]-->2.<span style="font-size: 7pt; font-stretch: normal;">
</span><!--[endif]-->Create a Live ID. </div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 13.5pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -13.5pt;">
<!--[if !supportLists]-->3.<span style="font-size: 7pt; font-stretch: normal;">
</span><!--[endif]-->Cost for the certification is 4500 INR (Till
Now).</div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 13.5pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -13.5pt;">
<!--[if !supportLists]-->4.<span style="font-size: 7pt; font-stretch: normal;">
</span><!--[endif]-->Enter your complete address. Depending on your
address it will automatically display your nearest prometric center.</div>
<div class="MsoListParagraphCxSpMiddle" style="margin-left: 13.5pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -13.5pt;">
<!--[if !supportLists]-->5.<span style="font-size: 7pt; font-stretch: normal;">
</span><!--[endif]-->Select the best possible date and time for the
exam which suits you.</div>
<div class="MsoListParagraphCxSpLast" style="margin-left: 13.5pt; mso-add-space: auto; mso-list: l0 level1 lfo1; text-indent: -13.5pt;">
<!--[if !supportLists]-->6.<span style="font-size: 7pt; font-stretch: normal;">
</span><!--[endif]-->Once it is confirmed on exam date reach the
center 15 min prior.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Following are the brief note on the exam:</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Total Questions: 42</div>
<div class="MsoNormal">
Total Marks: 1000</div>
<div class="MsoNormal">
Duration: 120 Minutes</div>
<div class="MsoNormal">
Passing Score: 700 (70% minimum score you should score to
pass)</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
1. Most of the questions are MCQ i.e. Almost 32-35
questions.</div>
<div class="MsoNormal">
2. Few of the questions are query designing i.e. Designing
Views, SP's or Functions.</div>
<div class="MsoNormal">
3. And rest Query designing but along with options.</div>
<div class="MsoNormal">
4. Weightage of these questions is not same as each question
are independent. Marks for each question are not displayed it is calculated
internally. At the end of the exam the total score will be displayed.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
You can follow the below official link from Microsoft for
more detail:<br />
<br /></div>
<div class="MsoNormal">
<a href="https://www.microsoft.com/learning/en-in/exam-70-461.aspx"><span style="color: black;">https://www.microsoft.com/learning/en-in/exam-70-461.aspx</span></a></div>
<div class="MsoNormal">
https://www.microsoft.com/learning/en-in/sql-certification.aspx</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Hope this will help you at least a bit about the exam
70-461. You can leave your comments here or call me for more information.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
Keep in touch... And Happy Learning....</div>
<div class="MsoNormal">
</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
I appreciate and thank you for reading this post. <span style="font-family: Wingdings;">J</span></div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0tag:blogger.com,1999:blog-4109594701716293509.post-87507290363305545102015-06-17T14:55:00.001+05:302015-06-17T14:56:40.592+05:30Apart From SQL Server...<div dir="ltr" style="text-align: left;" trbidi="on">
<h3 style="text-align: left;">
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;">Hi Friends,</span></span></h3>
<h3 style="text-align: left;">
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;">Here is a topic which you can relate or say not related to
SQL Server but yes it is related to the Database world and this would be the
most Hot and upcoming topic in the IT sector (or let me correct it is
already!!!).</span></span></h3>
<div>
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;"><br /></span></span></div>
<h3 style="text-align: left;">
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;">Any wild guesses what the topic it would be??</span></span></h3>
<div>
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;"><br /></span></span></div>
<h3 style="text-align: left;">
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;">I expect some of you guessed it right. And the topic which I
am mentioning is "BIG DATA".</span></span></h3>
<div>
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;"><br /></span></span></div>
<h3 style="text-align: left;">
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;">How I came to know about this???</span></span></h3>
<div>
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;"><br /></span></span></div>
<h3 style="text-align: left;">
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;">Frankly speaking I was not aware of the data storage unit
terms above Terabyte (TB), so I just goggled around and at least now I know
these units that are Petabyte, exabyte, zettabyte, yottabyte that's it till
date.</span></span></h3>
<div>
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;"><br /></span></span></div>
<h3 style="text-align: left;">
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;">After learning the data storage units I was curious about
the company’s that stores the data above TB. I mean of course it would be a
challenge to store such a huge data. Rather I'll say it would also be
challenging task to maintain & process these data's.</span><span style="font-family: Times, Times New Roman, serif;">As per a site the most famous social network site
"Facebook" generates approx 500 TB of data daily basis. As I said storing
and maintaining these data's for companies would be challenging.</span></span></h3>
<div>
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;"><br /></span></span></div>
<h3 style="text-align: left;">
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;">Now-a-days internet is flooded with lots of videos about the
big data; some of them may be worth watching if you are interested to learn.
Once you start learning you will come across many terms such as Hadoop, Hbase,
NoSQL, Pig, Hive, Cassandra and many more. Even till date these are just a term
for me as well as I am a beginner to this topic. As I progress and feed myself
with these topics I will regularly update you through this blog. This is just a
beginning to the new technology of "BIG DATA". In coming days I will
post new topics as well as links about this topic.</span></span></h3>
<div>
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;"><br /></span></span></div>
<h3 style="text-align: left;">
<span style="font-family: Times, Times New Roman, serif;"><span style="font-weight: normal;">According to one of the research company there would be a
crunch of these skill set in the upcoming years. Looking from the job perspective
you can find ample of designation under this topic also with lots of job opportunities. But beside this, one has to select which suits best for them.</span></span></h3>
<div>
<span style="font-family: Times, Times New Roman, serif;"><span style="font-weight: normal;"><br /></span></span></div>
<h3 style="text-align: left;">
<span style="font-weight: normal;"><span style="font-family: Times, Times New Roman, serif;">Happy learning!!!</span></span></h3>
<div style="text-align: left;">
</div>
</div>
Vikas B Sahuhttp://www.blogger.com/profile/02753943881979905714noreply@blogger.com0