Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2014 Availability Groups Expand / Collapse
Author
Message
Posted Wednesday, May 21, 2014 8:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 54, Visits: 1,133
how does security works between availability groups.
ex if i create an object and grant permissions to a user will that be replicated to secondary replica .
Post #1573190
Posted Wednesday, May 21, 2014 8:14 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 11:25 AM
Points: 292, Visits: 1,206
Yes, DB Users peice of security will be good. But keep in mind, you still have to deal with Logins at the Instance level in secondary replicas.
Post #1573196
Posted Friday, May 23, 2014 9:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
I use these scripts for my SQL users. If you read my post yesterday, Domain users do not need to have the sids matching. Just add the domain user(s) on the secondary and it will sync up. Also here is an article for your reading pleasure.

http://support.microsoft.com/kb/918992


I got these nifhty scripts off anothers DBA site and they work really well for me;
-- Get Sids for all SQL Server logins on the old server instance
SELECT name, [sid]
FROM sys.server_principals
WHERE [type] = 's';

-- Create new SQL Login on new server instance
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'SQLAppUser')
DROP LOGIN SQLAppUser;
GO

-- Use the sid from the old server instance
CREATE LOGIN SQLAppUser WITH PASSWORD = N'YourStrongPassword#', sid = 0x2F5B769F543973419BCEF78DE9FC1A64,
DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO


MCSA SQL Server 2012
Post #1574115
Posted Friday, May 23, 2014 10:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 54, Visits: 1,133
thanks for your reply.

How about an index, if i create a index on a large table consider 1 million records, it may take couple of hours to complete. What will happen on replica.

Does index creation start at the same time?

If it fails it will rollback on replica since we use sync mode.

both primary/replica will be unavilable when this kind of operation goes right
Post #1574131
Posted Friday, May 23, 2014 6:49 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:25 AM
Points: 301, Visits: 595
indexes are sent over to the secondary database auto from the primary in AlwaysOn, replication it takes a little longer

MCSA SQL Server 2012
Post #1574279
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse