Migrate/Synch SQL Server Logins from one Instance to Another Instance

Migrate/Synch SQL Server Logins from  one Instance to Another Instance

Select
'Create Login ' + QUOTENAME(A.name)
+ ' With Password=' + CONVERT(varchar(max), A.password_hash, 1) + ' hashed' --script out the passwrod
+ ', DEFAULT_DATABASE=' + quotename(A.default_database_Name) --if you apply the scrip to a mirroed server or log-shipped server user master database, as user database is not in useable state
+ ', DEFAULT_LANGUAGE=' + quotename(A.default_language_Name)
+ ', CHECK_POLICY=' + Case A.is_policy_checked when 0 then 'OFF' When 1 Then 'On' End
+ ', CHECK_EXPIRATION=' + Case A.is_expiration_checked when 0 then 'OFF' When 1 Then 'On' End
+ ', SID=' + CONVERT(varchar(max), A.SID, 1) --script out the SIDs
 As SQLLogin
From
sys.sql_logins A
Where A.name Not like '##%##'  --remove those system generated sql logins
And A.sid != 0x01 --SA sid is always same

Contained Database user connectivity

1.Enable contained database feature

USE master
GO
sp_configure 'show   advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'CONTAINED   DATABASE AUTHENTICATION', 1
GO
RECONFIGURE
GO
sp_configure 'show   advanced options', 0
GO
RECONFIGURE
GO

2.Connecting to Contained database after creating the user(sql or windows authentication)
First you will get an error as shown below. You have to give database name in Options tab in SSMS.Then only it will connect.

SQL Server 2012 AlwaysOn or higher: Multisite/Multi-Subnet Failover scenario requirements

Always On implementation for Multi-Subnet  Failover requirements:

1.Windows servers 2
2.Windows Server Failover Cluster(WSFC) services need to install on both servers
3.Cluster IP's 2
4.Listener IP's 2 ( Note: Listener name should be the same name for both IP's)


SQL Server 2012 AlwaysOn: Multisite/Multi-Subnet Failover Cluster Instance:
https://msdn.microsoft.com/en-us/library/hh750283.aspx?f=255&MSPPError=-2147217396

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups:
https://msdn.microsoft.com/en-us/library/jj191711.aspx


************************Additional references

http://www.sqlskills.com/blogs/joe/alwayson-architecture-guide-building-a-high-availability-and-disaster-recovery-solution-by-using-failover-cluster-instances-and-availability-groups/

Architecture guide for each design pattern:

• The first paper covering the multi-site FCI pattern was written by Mike Weiner, Sanjay Mishra and Min He –> “SQL Server 2012 AlwaysOn: Multisite Failover Cluster Instance”
• The second paper covering the AGs for HA & DR design pattern was published June 23rd, and was co-authored by Sanjay Mishra and me –> “AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups”
• And today the third design pattern paper, covering FCIs for local HA and AGs for DR, also co-authored by Sanjay Mishra and me, can be downloaded here –> “AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups”
• 
  

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