Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Does manual failover of availability group bind an SSL certificate?

briley last edited by

I'm trying to find the least intrusive way to bind a new SSL certificate to an Always On Availability Group 3-node cluster. After applying the new certificate, a message appears that SQL Server will need to be restarted for the change to take effect.

Is failing over a group sufficient to bind the new certificate? Or does SQL Server have to be restarted from the Cluster Manager?

Thanks for any help.
Reply Quote 0
1 Reply Last reply
T
Trenton last edited by

Is failing over a group sufficient to bind the new certificate?

Unfortunately it is not. Failing over an AG does not restart the instance, rather it causes state changes to the AG in both the cluster and SQL Server.

Or does SQL Server have to be restarted from the Cluster Manager?

Please do not use FCM with AGs as it isn't supported and can cause there to be synchronization issues between SQL Server and the Cluster, needing to be reset by stopping all instances of SQL Server and one by one bringing them back online. Note that this is only for AGs and not for FCIs which is perfectly fine to use FCM (unless the FCI also hosts AGs).

Stop the services via SSCM, if possible, or other service interaction options such as PowerShell, .Net remoting, etc., which you could start with the secondary replicas first and then fail over, then restart the old primary.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

S

Nested Dynamic Query with SP_EXECUTE_REMOTE for binding the Parameters
SQL, Database Testing • sql server azure sql database • • Saumya

2

0
Votes

2
Posts

0
Views

F

SP_EXECUTE_REMOTE in Azure works very similar to native sp_executesql. So you should be able to bind the params & values. But I believe there is a limitation that you can't get a output param value from it. Try this and see if it works for your case. Thanks. EXEC SP_EXECUTE_REMOTE N'External_DB1', N' DECLARE @param1 nvarchar(max)= @param1_from_sp_DB2, @outparam nvarchar(max) EXEC SP_EXECUTESQL N''EXEC DBO.[sp_DB1]@param1,@outparam OUTPUT'', N''@param1 nvarchar(max),@outparam nvarchar(max) OUTPUT'', @param1 ,@outparam OUTPUT SELECT @param1 AS Paramvalue,@outparam AS outputvalue', N'@param1_from_sp_DB2 nvarchar(max)', @param1 --Since this is the input param name from sp_DB2
Object Auditing with Availability Groups
SQL, Database Testing • sql server sql server 2012 availability groups • • inna

2

0
Votes

2
Posts

0
Views

K

No, auditing only reflects the one server that is running the audits. If you want to centralize audit results from multiple servers, you'll need to combine that data yourself. Keep in mind that if someone really wanted to circumvent this, they'd simply add another replica on any server they want, do the querying they need, and then destroy the replica. An audit on the primary would only capture the fact that a replica was added (and even then, only if you're auditing for those events.)
E

Drop partition scheme with orphan indexes
SQL, Database Testing • sql server partitioning dependenc languageies • • elysha

2

0
Votes

2
Posts

0
Views

Y

but the index names are NULL (and index id is 0) That's because they are heaps. BOL Reference on sys.indexes One way to get these off of that partition scheme would be to create a clustered index for those tables and specify a different filegroup or another partition scheme. Then once you have those tables off of that partition scheme you should be able to continue with removing it. create clustered index IX_YourIndex on dbo.YourTable (YourCiKeyCol) on SomeOtherFilegroup;
SQL Server 2012 not loading its self-generated SSL certificate
SQL, Database Testing • sql server 2012 ssl • • Marcee

2

0
Votes

2
Posts

0
Views

D

Discovered that a service broker endpoint was created using the same cert that we were trying to remove. After querying DMVs for sessions and connections we discovered that no connections were using the service broker endpoint so we deleted the server broker, dropped the certificate, then followed all of the steps for removing the cert in SQL Server Manager, removed the certificate from the Windows certificate store, then restarted the database instance. Confirmed that it loaded by reading in the SQL Server error log... "A self-generated certificate was successfully loaded for encryption."
C

Databases entering "In recovery" mode on restore
SQL, Database Testing • sql server sql server 2008 • • charlenef

2

0
Votes

2
Posts

0
Views

In such situations I am trying this piece of code: ALTER DATABASE SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE
E

How to check what type of lock an UPDATE statement uses?
SQL, Database Testing • sql server sql server 2005 • • emerson

2

0
Votes

2
Posts

0
Views

A

You can see the locks from DMV called sys.dm_tran_locks. The type is of lock can be seen from resource_type column (f. ex. OBJECT, PAGE etc). More information is available in MSDN Also, maybe this query by Jonathan Kehayias is useful: SELECT dm_tran_locks.request_session_id, dm_tran_locks.resource_database_id, DB_NAME(dm_tran_locks.resource_database_id) AS dbname, CASE WHEN resource_type = 'object' THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id) ELSE OBJECT_NAME(partitions.OBJECT_ID) END AS ObjectName, partitions.index_id, indexes.name AS index_name, dm_tran_locks.resource_type, dm_tran_locks.resource_description, dm_tran_locks.resource_associated_entity_id, dm_tran_locks.request_mode, dm_tran_locks.request_status FROM sys.dm_tran_locks LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id WHERE resource_associated_entity_id > 0 AND resource_database_id = DB_ID() ORDER BY request_session_id, resource_associated_entity_id
B

AlwaysOn Availability Groups go to Resolving status
SQL, Database Testing • sql server sql server 2012 availability groups • • baileigh

2

0
Votes

2
Posts

0
Views

K

I've seen this happen a lot when folks only have one set of network cables connecting their servers - meaning, a pair of 1Gb Ethernet cables in each node, at best, and they're using those for both regular networking as well as iSCSI storage connectivity. (The fact that you're using Equallogic is a clue - I've seen a lot of those with 1Gb implementations.) If you have any networking problems at all: The two nodes won't be able to see each other Neither node will be able to see the storage Presto, no one sees a majority, and you lose quorum Things that can cause this include: Backup software (doing huge reads from disk while simultaneously saturating network) Running a CHECKDB (again, huge reads from disk plus huge writes to TempDB, which can prevent the cluster heartbeats from getting through if you only have one network interface for both regular networking and storage) To work around it: Use separate network interfaces for regular networking and iSCSI (like a pair of 1Gb (at least) ports just dedicated to iSCSI, and nothing else) Use faster network interfaces (like 10Gb instead of 1Gb) Do less disk/network-intensive work (stop doing backups and CHECKDB, ha ha ho ho, but also ease up on the index rebuilds)
SQL query to check if secondary database is removed from availability group
SQL, Database Testing • sql server backup availability groups • • morde

2

0
Votes

2
Posts

0
Views

O

You could use exists with the sys.dm_hadr_database_replica_states DMV to see if the DB is in a AG. Returns a row for each database that is participating in an Always On availability group for which the local instance of SQL Server is hosting an availability replica. This dynamic management view exposes state information on both the primary and secondary replicas. On a secondary replica, this view returns a row for every secondary database on the server instance. On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database Here is the query: if exists( select DBName = db_name(s.database_id) ,s.is_local ,s.synchronization_state_desc ,s.synchronization_health_desc from sys.dm_hadr_database_replica_states s where db_name(s.database_id) = 'your_database' ) begin --do something useful end else begin --exit, probably without error end This would need to query against the secondary if you aren't on 2014+. If you are, then you can add s.is_primary_replica = 0 to the query. Or you can use this method or a similar approach.
N

When inserting the varchar value into the datetime field, it throws conversion error
SQL, Database Testing • sql server sql server 2008 • • Nykeriab

2

0
Votes

2
Posts

0
Views

P

Since you are not shared the sample data of the Gradd19 value, I suspect the date value is in the yyyy-dd-mm format, so when trying to convert (month value more than 12) it cause the out-of-range value error. For sample, if the date format in yyyy-mm-dd, it will execute properly: DECLARE @FromDate DATETIME; DECLARE @Gradd19 AS VARCHAR (20) = '2016-11-15'; SELECT @FromDate = CONVERT(VARCHAR, @Gradd19, 105); SELECT @FromDate; Incase if it is in yyyy-dd-mm it will throw out-of-range value error DECLARE @FromDate1 DATETIME; DECLARE @Gradd191 AS VARCHAR (20) = '2016-15-11'; SELECT @FromDate1 = CONVERT(VARCHAR, @Gradd191, 105); SELECT @FromDate1; So in that case you need do one more conversion to skip the issue, like the below: DECLARE @FromDate2 DATETIME; DECLARE @Gradd192 AS VARCHAR (20) = '2016-15-11'; SELECT @FromDate2 = CONVERT(VARCHAR(19), CONVERT(DATETIME, @Gradd192, 105), 120) SELECT @FromDate2; So instead of CONVERT(VARCHAR, Gradd19, 105) AS ExpiryDate you need to use CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120) AS ExpiryDate Update: As you mentioned in the comments the Gradd19 having the values of 20 and 60, it is difficult to filter those non-valid data. But based on the LEN() function you may avoid the non-valid data, then using the CONVERT you can get the VARCAHR field as DATETIME value. Based on your sample data the valid date's minimum length is 10 characters, so you can apply the condition in the WHERE clause. Sample execution: DECLARE @GradedDETAILS TABLE (Gradd19 VARCHAR (30)); INSERT INTO @GradedDETAILS (Gradd19) VALUES ('Sep 16 2016 12:00AM'), ('Sep 16 2016 12:00AM'), ('Sep 16 2016 12:00AM'), ('Sep 19 2016 12:00AM'), ('Sep 19 2016 12:00AM'), ('Sep 16 2016 12:00AM'), ('20'), ('60'), ('20-09-2016'), ('20-09-2016'); SELECT CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120) FROM @GradedDETAILS WHERE LEN(Gradd19) >= 10 So in your actual query also you need to add the LEN(Gradd19) >= 10 condition and in the SELECT use this to convert as DATETIME by CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)
A

SQL Server Always-On Availability Groups LeaseTimeout
SQL, Database Testing • sql server availability groups clustering failover • • authera

2

0
Votes

2
Posts

0
Views

If you'd like to know why, the answer will be in the cluster log. If you need help looking over it, please upload the primary node's (at the time) cluster log. Clustering makes the decisions when it comes to FCIs and AGs with SQL Server. The cluster is responsible for metadata updates and changes, health checking, resource placements, etc., and most people don't change the by default cluster behavior which is to restart the resource after a period of time. Since in your case it connected back up one minute later, the best guess without the cluster log is that this is what occurred, especially since you've stated that the default clustering settings have not been changed. More Info 1: https://docs.microsoft.com/en-us/previous-versions/orphan-topics/ws.11/dn281898(v=ws.11)?redirectedfrom=MSDN More Info 2: https://support.microsoft.com/en-us/help/228923/cluster-resources-can-be-configured-to-restart-automatically so does restarting the resource mean restarting the SQL instance? No, it restarts the AG Resource which is not a SQL Server instance. What I dont understand is, if its set to restart the SQL service before trying a failover (when it comes to a leasetimeout) - wouldn't a restart of the service/resource cause a failover anyway? No, because it's not the instance. The availability group tied to that resource will stop synchronizing and will go into a resolving state but the instance itself will not have any issues.
E

SQL Server agent job question
SQL, Database Testing • sql server sql server 2012 sql server agent • • elysha

2

0
Votes

2
Posts

0
Views

C

1. Is it mandatory to have SQL Server agent account defined in SQL server logins to run the job? See this and this. The SQL Server Agent runs as a Windows service named NT SERVICE\SQLSERVERAGENT. The NT SERVICE\SQLSERVERAGENT login is how the Windows process that is SQL Server Agent connects to the Database Engine to read the msdb database to find out what it should do; and then do it. Both of these logins are members of the sysadmin fixed server role, so they can do anything in the Database Engine. And they need to stay that way. As for the account that you used to run the services, this is complicated and has changed in each version of SQL Server.The short answer is that the account you specify will be used when a process tries to reach outside of the current Windows environment. But within the computer, there is a mix of authorization granted to the domain user, the service, and the Windows group SQLServerMSSQLUser$computername$MSSQLSERVER. There are permission in the operating system that an agent account gets by default. Details here. As per books online: The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles: The account must be a member of the sysadmin fixed server role. To use multiserver job processing, the account must be a member of the msdb database role TargetServersRole on the master server. In all Windows versions, permission to log on as a service (SeServiceLogonRight) But it should not be a member of local administration group. We recommend choosing a Windows user account that is not a member of the Windows Administrators group. However, there are limitations for using multiserver administration when the SQL Server Agent service account is not a member of the local Administrators group 2. What user/login my update job is using in order to update the table? Reference: https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/ Job ownership is an important concept in SQL Server Agent. SQL Server Agent sets the security context for job execution based on the role of the user owning the job. By default, SQL Server Agent executes job steps under the SQL Server Agent service account irrespective of job ownership, or under the context of a proxy account. The exception to this rule is T-SQL job steps, which execute under the security context of the job owner. If the job owner is a member of the sysadmin role, then the job step executes in the context of the SQL Server Agent service account.
Date handling issue in an ACCESS/VBA application with SQL bank Server
Software Programming • sql server vba datetime ms access • • irl

2

0
Votes

2
Posts

0
Views

E

After many searches on the internet and nothing enlightening I ended up arriving at a post on a forum that elucidated the issue.When looking at the design of one of the ODBC tables of the sql server within the access, I noticed that the date fields were sealed as short text.By bringing the sql server tables through the access wizard odbc he understood that the datetime2(0) fields were short text, rather than date, and therefore did not treat it correctly in the application.To correct this I had to complete three actions: - Ensure all tables have a primary key. - Ensure all Bit fields have a default value - Switch all date fields to datetime typeAfter following these steps and updating the linked tables, the access correctly read the fields as being date, rather than short text and all my requests could be made without needing to format dates.OBS: My SQL bank Server was migrated from Access through a migration tool and set all date fields as datetime2(0).
A

Changing feature on a database that is a member of SQL Server AlwaysOn availability group
SQL, Database Testing • sql server availability groups • • Avante

2

0
Votes

2
Posts

0
Views

R

There is no other way to enable service broker except taking that database out of AlwaysON Availability group, enabling service broker and then joining it back. If you know that your source database has service broker enabled, then when you restore the database, use RESTORE .. with ENABLE_BROKER, so that you dont have to go with such trouble again. If you try to enable service broker for a database involved in AG, you will get below error : Msg 1468, Level 16, State 1, Line 3 The operation cannot be performed on database "AdventureWorks2012" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group. Msg 5069, Level 16, State 1, Line 3 ALTER DATABASE statement failed.
E

After installing a new SSL certificate, my MSSQL server is no longer able to be restarted
SQL, Database Testing • sql server ssl • • elysha

2

0
Votes

2
Posts

0
Views

J

In addition to the permissions mentioned in another answer ... If the service account that the SQL Engine runs under is not a local administrator, you need to grant it Read access to the certificate's private key. Open the Certificates management console, or add it as a snap-in to a fresh MMC session. Select Local Computer for the set of certificates to manage. Find the certificate in question. It is probably under Personal --> Certificates. Right-click and select All Tasks --> Manage Private Keys.... Add the SQL Engine service account, giving it Read access. (It'll default to Full Control when you add it, but Read is sufficient.) (In a cluster, you have to do this on each node.)
F

sql query to compare column name to column data
SQL, Database Testing • sql server sql server 2012 • • fbio

2

0
Votes

2
Posts

0
Views

D

The question is somewhat unclear, but I'll assume that you are trying to get the values of one particular column. And looking at the column names of a particular table, you want a list of all columns whose name is the same as the returned value. Then something like below might be a good start. SELECT T.ColumnName, -- will show the value of the column C.Object_name([object_id]), --will show the name of the table C.name -- will show the name of the column that matched the column value FROM SchemaName.TableName T INNER JOIN Sys.Columns C ON T.ColumnName= C.name WHERE [object_id] = OBJECT_ID('SchemaName.TableName') If you want to compare the value of the column to the column names in a different table or any tables, you simply need to change or delete the WHERE clause.

Does manual failover of availability group bind an SSL certificate?

Suggested Topics

Nested Dynamic Query with SP_EXECUTE_REMOTE for binding the Parameters SQL, Database Testing • sql server azure sql database • • Saumya

Object Auditing with Availability Groups SQL, Database Testing • sql server sql server 2012 availability groups • • inna

Drop partition scheme with orphan indexes SQL, Database Testing • sql server partitioning dependenc languageies • • elysha

SQL Server 2012 not loading its self-generated SSL certificate SQL, Database Testing • sql server 2012 ssl • • Marcee

Databases entering "In recovery" mode on restore SQL, Database Testing • sql server sql server 2008 • • charlenef

How to check what type of lock an UPDATE statement uses? SQL, Database Testing • sql server sql server 2005 • • emerson

AlwaysOn Availability Groups go to Resolving status SQL, Database Testing • sql server sql server 2012 availability groups • • baileigh

SQL query to check if secondary database is removed from availability group SQL, Database Testing • sql server backup availability groups • • morde

When inserting the varchar value into the datetime field, it throws conversion error SQL, Database Testing • sql server sql server 2008 • • Nykeriab

SQL Server Always-On Availability Groups LeaseTimeout SQL, Database Testing • sql server availability groups clustering failover • • authera

SQL Server agent job question SQL, Database Testing • sql server sql server 2012 sql server agent • • elysha

Date handling issue in an ACCESS/VBA application with SQL bank Server Software Programming • sql server vba datetime ms access • • irl

Changing feature on a database that is a member of SQL Server AlwaysOn availability group SQL, Database Testing • sql server availability groups • • Avante

After installing a new SSL certificate, my MSSQL server is no longer able to be restarted SQL, Database Testing • sql server ssl • • elysha

sql query to compare column name to column data SQL, Database Testing • sql server sql server 2012 • • fbio

Nested Dynamic Query with SP_EXECUTE_REMOTE for binding the Parameters
SQL, Database Testing • sql server azure sql database • • Saumya

Object Auditing with Availability Groups
SQL, Database Testing • sql server sql server 2012 availability groups • • inna

Drop partition scheme with orphan indexes
SQL, Database Testing • sql server partitioning dependenc languageies • • elysha

SQL Server 2012 not loading its self-generated SSL certificate
SQL, Database Testing • sql server 2012 ssl • • Marcee

Databases entering "In recovery" mode on restore
SQL, Database Testing • sql server sql server 2008 • • charlenef

How to check what type of lock an UPDATE statement uses?
SQL, Database Testing • sql server sql server 2005 • • emerson

AlwaysOn Availability Groups go to Resolving status
SQL, Database Testing • sql server sql server 2012 availability groups • • baileigh

SQL query to check if secondary database is removed from availability group
SQL, Database Testing • sql server backup availability groups • • morde

When inserting the varchar value into the datetime field, it throws conversion error
SQL, Database Testing • sql server sql server 2008 • • Nykeriab

SQL Server Always-On Availability Groups LeaseTimeout
SQL, Database Testing • sql server availability groups clustering failover • • authera

SQL Server agent job question
SQL, Database Testing • sql server sql server 2012 sql server agent • • elysha

Date handling issue in an ACCESS/VBA application with SQL bank Server
Software Programming • sql server vba datetime ms access • • irl

Changing feature on a database that is a member of SQL Server AlwaysOn availability group
SQL, Database Testing • sql server availability groups • • Avante

After installing a new SSL certificate, my MSSQL server is no longer able to be restarted
SQL, Database Testing • sql server ssl • • elysha

sql query to compare column name to column data
SQL, Database Testing • sql server sql server 2012 • • fbio