Windows authentication connection from Linux client to SQL Server Database

About the Kerberos integrated authentication and Linux.


Using Integrated Authentication

http://msdn.microsoft.com/en-us/library/hh568450(v=sql.110).aspx

SQL Server 2014: while re-install sql server got an error "cannot find one or more components. please reinstall the application"

The  below error came while re-installaing SQL Server 2014 on Win 2012.

Error: "cannot find one or more components. please reinstall the application"

I tried different options but not working to install and finally found below solutiion.
Solution: 
Go to contrl-panel---->uninstall ---->

1.Remove/uninstall all SQL Server installed components
2. Import: Also remove all Visual Studio components also. Other wise you will get the above error message while installing the SQL Server 2014.

SQL Server 2012 Always ON: databases not connected if secondary server down?

Always on configured for SQL Server2012 (RTM version) on Windwos 2008
Node1: Primary server (DB name: TestDB1) Node2: Secondary server(configured always on for DB name TestDB1) Node3: Fileshare server(for automatic failover) Always on: Always on configured with automatic failover(move immediately if any failover scenario)

Testing Scenario: Node1 shutdown(primary shutdown) We did Node1 shutdown and node2 became primary. But TestDB1 database not able to access on NOde2(whichis beacme Primary after shut Node1 shut down).

we did below command as well net start clussvc /fq --on NOde2 (it showing that "requested service already started). in command line if we typ--> Cluster node ( status showing ---->Node1 down,Node2 UP)

Question: In order to access the TestDB1 database on Node2, what should we do? Is there any configuration change?
Note: After Node1 shutdown, Node2 instance is up and running, but not able to access the TestDB1 database.(TestDB1 database showin not synchronized)


Solution: Check below and modify accordingly in order to work it.
1. alter availability group [Test001AAG] set (failure_condition_level=5) (--previously it was 3)
    --select * from sys.availability_groups
2.Modified Availibility session timeout to 120(previously it was 10).
3.Cluster group properties
     max failover in specified period: 3
     Hours: 6

SSDT-BI for SQL Server 2014, Command line silent unattendant automatic installation script/steps

SSDT-BI for SQL Server 2014, Command line silent unattendant automatic installation script/steps

Note: While installing SQL Server 2014, you can not find the Feature like BIDS(SSDT Client tool for SSIS/SSRS/SSAS development).
For SQL Server 2014 version, need to separately down load the SSDT-BI tool and install it.


Downloaded SSDT-BI software and it's free tool
http://www.microsoft.com/en-us/download/details.aspx?id=42313


--Command line script to install SSDT-BI client tool
setup.exe /ACTION=INSTALL /FEATURES=SSDTBI /Q /IACCEPTSQLSERVERLICENSETERMS

Grant read access on SYSTEM and USER Databases

1.SYSTEM Databases
--Read access(including metadata) on system databases.

USE msdb
go
drop user [ADGroup_Name]
go
USE master
go
drop user [ADGroup_Name]
go
drop login [ADGroup_Name]
go

USE master
go
IF EXISTS(select 1 from sys.server_principals where name='ADGroup_Name')
BEGIN
drop login [ADGroup_Name]
END
go
CREATE LOGIN [ADGroup_Name] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO

GRANT VIEW SERVER STATE to [ADGroup_Name]
GRANT VIEW ANY DEFINITION to [ADGroup_Name]
GRANT VIEW ANY DATABASE to [ADGroup_Name]
go


USE master
go
IF EXISTS(select 1 from sys.database_principals where name='ADGroup_Name')
BEGIN
drop user [ADGroup_Name]
END
go
CREATE USER [ADGroup_Name] FOR LOGIN [ADGroup_Name]
go
EXEC sp_addrolemember db_datareader, [ADGroup_Name]
go
GRANT EXECUTE on xp_readerrorlog to [ADGroup_Name]
GRANT SHOWPLAN TO [ADGroup_Name]
go

--SQL Server Agent
USE msdb
go
IF EXISTS(select 1 from sys.database_principals where name='ADGroup_Name')
BEGIN
drop user [ADGroup_Name]
END
go
CREATE USER [ADGroup_Name] FOR LOGIN [ADGroup_Name]
go
EXEC sp_addrolemember db_datareader, [ADGroup_Name]
go
EXEC sp_addrolemember SQLAgentReaderRole, [ADGroup_Name]
GO
GRANT SHOWPLAN TO [ADGroup_Name]
go

************************************************************************************************

2.USER Databases

--USE
--go
--Remove [ADGroup_NAME]
--drop user [ADGroup_NAME]

IF EXISTS(select 1 from sys.database_principals where name='ADGroup_NAME')
BEGIN
drop user [ADGroup_NAME]
END
go
CREATE USER [ADGroup_NAME] FOR LOGIN [ADGroup_NAME]
go
EXEC sp_addrolemember db_datareader, [ADGroup_NAME]
go
GRANT SHOWPLAN to [ADGroup_NAME]
go

Power Shell did not recognize invoke-sqlcmd on SQL Server 2008 R2 box for Power Shell/SQL scripts

OR
-How to load SQL Server Snapins into PowerShell Session. for SQL Server 2008 R2 box?
-It not required if it is SQL Server 2012 and above.

PowerShell run/execution issue for SQL Server 2012 box ps/scripts on SQL Server 2008 R2 box.
Developed powershell/sql scripts in SQL Server 2012 env box and moved/rollout to  SQL Server 2008 env box. The ps scripts got failed and got below error.

Error: "The term 'invoke-sqlcmd' is not recognized as the name of a cmdlet, function, script file, or operable program..."
       "The specified module 'sqlps' was not loaded because no valid module file was found in any module directory."

Background: The SQLPS module is Powershell module introduced in SQL Server 2012. Prior to the 2012 release Powershell support in SQL Server was provided by a mini-shell i.e. sqlps.exe introduced in SQL 2008 with some minor enhancements in SQL Server 2008 R2.

Execution options on different SQL Server version:

SQL Server 2012: If install sql server 2012, default sqlps module and comdlets will be installed. Can use below script and execute on SQL Server 2012 box.

TestPShell.ps1
Import-Module SQLPS -DisableNameChecking
invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"

SQL Server 2008 R2: If install sql server 2008 R2 on box, then "Import-Module SQLPS" can not work due to sqlps module not available directly. In-order to work sqlps on sql server 2008 R2 box, add below code inplace "Import-Module SQLPS" for above "TestPShell.ps1" PS script. Then invoke-sqlcmd will work.

TestPShell.ps1
#Import-Module SQLPS -DisableNameChecking ----->This is not requied if it is SQL Server 2008 box(powershell).
if ( (Get-PSSnapin -Name sqlserverprovidersnapin100 -ErrorAction SilentlyContinue) -eq $null )
{
    Add-PsSnapin sqlserverprovidersnapin100
}

if ( (Get-PSSnapin -Name sqlservercmdletsnapin100 -ErrorAction SilentlyContinue) -eq $null )
{
    Add-PsSnapin sqlservercmdletsnapin100
}

invoke-sqlcmd -Servername ServerName -inputFile "C:\scripts\Test.sql" | out-File -filepath "C:\scripts\TestOutput.txt"

Note: Load the following 2 snapin's and execute ps/sql scripts using invoke-sqlcmd command.
SqlServerCmdletSnapin100
SqlServerProviderSnapin100

Always ON Availability Group (AAG) configuration on Cluster(Active/Passive) env servers and standalone servers

SQL Server 2012 or higher versions:  Always on Availability Group (AAG) can configure in 2 scenario's:
1.Cluster (Active/Passive)(Not supports Automatic failover)
2.Standalone Servers (Supports Automatic failover)

1.Cluster (Active/Passive) Env Servers:
 (This does not support automatic failover and it supports manual failover)
Pre-req:
1.Windows Server Faiover Cluster(WSFC) services need to install on both nodes (Windows Admin responsibility)
1.1 Then configure  the quorum ( add the nodes in quorum)
2.Make sure test the validation report for storage,cluster servers .....etc.
    If any issue then contact windows admin
3.IP addresses required for
    Node1 IP address
    Node2 IP address
    Windows Cluster IP
     SQL Server Virtual IP Address
    Listener Name and IP Address (for Always On configure)
    Optional :   MSDTC IP( if you use distributed transactions then it required)
   Note: If you use 3 rd server then submit for Node3 IP address
 4.Use AD group to install the SQL Server Instance(Best practice)
     or can use any Windows Admin group user to install
SQL Installation:
5. High level steps
   5.1 Install SQL Server on Node1
   5.2 Then add on Node2 to install SQL Server
   5.3 Reboot the both nodes
6.Configure Always On
    Create Availability group and add replicas.
    use Always On Listener Name/IP address

2.Standalone Servers: (This supports automatic failover(3rd node also required) and  manual failover(node1/node2 required) )
Pre-req:
1.Install basic Windows Server Failover Cluster(WSFC) services on both nodes (Windows Admin responsibility)
1.1 Then configure  the quorum(add the nodes in quorum)
2.Make sure test the validation report for storage,cluster servers .....etc.
    If any issue then contact windows admin
3.IP addresses required for
    Node1 IP address
    Node2 IP address
    Windows Cluster IP
    Listener Name/IP Address(for Always On configure)
    Optional :   MSDTC IP( if you use distributed transactions then it required)
     Note: If you use 3 rd server then submit for Node3 IP address for automatic failover
 4.Use AD group to install the SQL Server(Best practice)
     or can use any Windows Admin group user to install
SQL Installation:
5. High level steps
   5.1 Node1: Install SQL Server on Node1
   5.2 Node2: Install SQL Server on Node2
   5.3 Node3: This is required for automatic failover purpose. if you  use Node and File share or other options ...on Node3 then SQL Server installation not required.
Node3 is for just automatic failover purpose(voting purpose) only. If it manual failover then node2/node2 required.
Note:"Search in "quorum configuration" options(4 options and select any option to configure the quorum voting)
6. Reboot the all 3 nodes(node1/node2/node3)
7.Configure Always On
    Create Availability group and add replicas.
    use Always On Listener Name/IP address


Issues while create AlwaysON Availability Group:
Note: Before you create a Windows failover cluster, you have to check whether the domain user account that is running the Create Cluster Wizard has the "Create Computer Objects"permission.
Solution: http://support.microsoft.com/kb/2829783)
or
Check  "Steps for configuring the account for the person who installs the cluster" step below link.
http://technet.microsoft.com/en-us/library/cc731002(WS.10).aspx