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).

Software to help automate windows os patching with SQL Server high availability/failover clusters

N
nishika last edited by

is there a third party tool or a way to automate installing windows updates on windows clusters with sql server high availability groups installed? right now we have a manual process of installing then failing over etc.
Reply Quote 0
1 Reply Last reply
Mystic last edited by

There is not a tool that offers an out of box solution. However, there are many orchestration tools you can utilize to build your own automation solution. A few that come to mind are System Center Orchestrator or Ansible. Not to say those are the only tools, or the best tools. In the orchestration solutions you can build out a multi step playbook. Steps can include, checking if a replica is primary, pushing patches, failing replicas over, and rebooting servers. When or if each step executes could be tied to the outcome of the step before it.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

R

SQL Server help tables?
SQL, Database Testing • sql server sql server 2008 • • rielyn

2

0
Votes

2
Posts

0
Views

E

spt_values is not exactly a help table for your use. It is an undocumented system table used by internal stored procedures for a variety of tasks. Since it contains neither unique nor contiguous numbers, and because it is undocumented and unsupported (and could simply vanish in a future version), I strongly recommend you don't use this table for anything. You can always generate a sequence based on row_number from documented and supported system views, such as sys.all_objects, sys.all_columns, or a cross join of the two if you need a large number. Or just build a Numbers table and store the numbers for yourself. This is likely to be more efficient in most cases because, if you use it enough, it will always be accessed in memory without having to calculate the sequence.
S

High Disk I/O from sql server or is High disk I/O slowing sql server?
SQL, Database Testing • sql server perfmon • • simrah

2

0
Votes

2
Posts

0
Views

O

Have a look at the following perfmon counters: SQL Server, Buffer Manager Object: Page lookups/sec Page reads/sec Readahead pages/sec SQL Server, Access Methods Object Full Scans/sec Range Scans/sec Skipped Ghosted Records/sec SQL Server, Wait Statistics Object Page IO latch waits SQL Server driving a high number of IO requests would be corroborated with a high number scans, increase in page lookups and page reads and high page IO latch waits. Is worth trying a look at sys.dm_exec_query_stats for entries with high physical reads counts. They could quickly pinpoint the culprit. In general approaching the problem as a performance troubleshooting problem, following a methodlogy like Waits and Queues is the right approach. You DBA seems to be doing the right thing so you should listen to him.
C

Why does existing data not replicate when using custom stored procedures
SQL, Database Testing • sql server transac languagetional re • • charlenef

2

0
Votes

2
Posts

0
Views

P

Why would the existing data not replicate? Because when you added the subscription using sp_addsubscription the value that you provided to the parameter @sync_type was N'replication support only', and according to Microsoft docs this value means: Provides automatic generation at the Subscriber of article custom stored procedures and triggers that support updating subscriptions, if appropriate. Assumes that the Subscriber already has the schema and initial data for published tables. References: sp_addsubscription (Transact-SQL) | Arguments (Microsoft Docs) This means that you as publisher of the subscription are responsible that the initial data is already available at the subscriber, before the replication is set up.
SQL Server - global temporary table vs normal table
SQL, Database Testing • sql server temporary tables • • irl

2

0
Votes

2
Posts

0
Views

R

A normal table will cause logging in your database, consume space, and require log flush on every commit. So for temporary data, you should use a temporary table. But you would normally use a regular (#) temporary table here, not a global (##) temporary table. Global temporary tables are useful in the (very rare) scenario where multiple, separate sessions need access to the same temporary table. Another scenario where a global temporary table can be useful is when the temp table is created in dynamic SQL, but referenced in the calling batch, eg: exec ('select * into ##foo from sys.objects') select * from ##foo But a global temporary table is not necessary to create a temp table in a stored procedure and access it in dynamic SQL, eg: create table #foo(id int, data varchar(200)) exec ('insert into #foo(id,data) values (1,''hello'')') select * from #foo And when you create a regular temporary table in a stored procedure it will automatically be destroyed when the stored procedure ends, and you can run multiple instances of the same procedure without interfering with each other.
E

Configure cluster on an existing SQL Server - to setup AlwaysOn Availability Groups
SQL, Database Testing • sql server availability groups high availability • • emran

2

0
Votes

2
Posts

1
Views

K

You can do it without sql reinstall. Only some reboots. Install WSFC, add nodes with existing sql server. Then enable alwayson in the configuration manager:
G

SQL insert data simultaneous
SQL, Database Testing • sql server • • guilherme

2

0
Votes

2
Posts

0
Views

A

I think 2 options that can help you: Instead of getting the last number and add 1 you can create a table with an IDENTITY column (SerialNumber). When you insert one row the identity column adds the increment you specify (in your case, 1). If 2 users hit the button at the same time SQL server is capable to manage the concurrency and it will assign different numbers. The same solution as number 1, but instead of using IDENTITY column you extract the next serial number from a sequence object. You create this sequence object and define the "Starts With" number and "Increment" number. Same way about the users, you extract the number from the sequence and this number can only be extracted once avoiding concurrency issues. With this solution, you have to keep in mind that if by any reason your insert fails the serial number is now lost, once you extracted it the counter of the sequence was incremented even though your insert was not committed. Here you have the official links for extra details about how to create an identity column and a sequence object: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-2017 https://docs.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-2017
R

How to Make Group By with a Soma Field
Software Programming • mysql sql server sql oracle firebird • • rielyn

2

0
Votes

2
Posts

0
Views

Good afternoon Marcos!That's right. sum(qtde) saida_qtde line 25 is in a subquery that feeds line 9 statement. Try to put at the end of the code one GROUP BY 9 or GROUP BY saida_qtde.I think the querie will ask to group other columns too.
C

Automated backups sql server IaaS
SQL, Database Testing • sql server backup azure azure blob storage • • charlenef

2

0
Votes

2
Posts

0
Views

E

From An Azure storage account to use for storing Automated Backup files in blob storage. A container is created at this location to store all backup files. The backup file naming convention includes the date, time, and database GUID. https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/automated-backup If you turn that feature off in the portal, you can still use the underlying engine feature SQL Server managed backup to Microsoft Azure, which allows you to specify the container.
K

How does SQL Server Startup?
SQL, Database Testing • sql server sql server 2008 r2 • • kalyn

2

0
Votes

2
Posts

0
Views

J

Ah, after you updated your comment I realized that you were asking about how to start another process after all databases have been restored/loaded. You can configure a stored procedure to be called using the sp_procoption. I am pretty sure that runs after all databases are ready. Next, you just need to invoke your external process via sql server. "...Execution of the stored procedures starts when all databases are recovered and the "Recovery is completed" message is logged at startup...."
G

In-Memory OLTP SQL Server 2016 - insufficient system memory
SQL, Database Testing • sql server sql server 2016 memory optimized tables • • guilherme

2

0
Votes

2
Posts

0
Views

T

So my guess is you may not actually have enough Memory for that table to be used as an In-Memory Table. As mentioned in my comments, per this Brent Ozar article Hekaton (In-Memory OLTP) Tables In Use, it sounds like one of the requirements are you have at least twice as much Memory available as is the size of your In-Memory Tables: But you need to understand the limitations, and they’ve changed a lot over the years... Your memory needs to be 2x the size of your data While you dedicated 56 GB of Memory to your SQL Server instance, a 23 GB table to be moved to an In-Memory Table means you should have at least 46 GB available for just that table, following Brent Ozar's advice. And because SQL Server uses the Memory you allocate to it for caching other things like data pages loaded in Memory, the 10 GB you have left over from 56 GB - 46 GB is not a lot to rely on. I.e. I wouldn't doubt your instance is already consuming more than 10 GB and therefor you don't have 46 GB available to provide to your In-Memory Table. This article Estimate Memory Requirements for Memory-Optimized Tables in Microsoft's Books Online also is a good guide on how to estimate how much Memory you'll actually need. I would recommend testing a much smaller table that's only a few GB or so as a proof of concept. Then work your way up. If you get that working, then you can try increasing your Memory a bit further before retrying your original table.
E

Is it possible to convert existing users from SQL Server authentication to Windows (AD) authentication? (SQL Server 2008 R2)
SQL, Database Testing • sql server security ac languagetive direc lan • • emran

2

0
Votes

2
Posts

0
Views

J

The security concept of SQL Servers differentiate between login and user. A login is at the SQL Server instance level and can be mapped to different users for different databases, as long as the database user is a SQL user and not a Windows user. You could create the Windows login and the user (both having the same name) followed by script that copies the permissions from the SQL user to the Windows user. (Search for "sql server clone user permissions").
E

Why do case/iif return nullable datatypes?
SQL, Database Testing • sql server t sql • • emran

2

0
Votes

2
Posts

0
Views

T

First off, the column is_nullable returned from sys.dm_exec_describe_first_result_set_for_object has the following definition as per docs.Microsoft.com: Contains the value 1 if the column allows NULLs, 0 if the column does not allow NULLs, and 1 if it cannot be determined that the column allows NULLs. This means that the engine could also be looking for a situation where it can't predict the nullability of the return column. (This is more for your information than anything else - in this case it doesn't appear to be happening) Now when we simplify the stored procedure and eliminate the variables as such: CREATE PROCEDURE TestProc AS SELECT IIF('hello' = 'hello','true','false') GO We can see the nullability goes away. This is because the variables you are utilising in your procedure (@Zerobit and @Onebit) are NULLABLE by definition. The variables themselves are getting set at run-time, but the underlying metadata for the data type of the variable is still nullable. There is also a possibility that the query optimiser needs to account for all scenarios, and it can't use the value of the variable at runtime for an all-use plan. If we rewrite the query and do not set the value of the variables, we will see the same results (The column metadata for the return of the procedure is still NULLABLE): CREATE PROCEDURE TestProc AS DECLARE @trueoutput VARCHAR(10) DECLARE @falseoutput VARCHAR(10) SELECT IIF('hello' = 'hello',@trueoutput,@falseoutput) GO My suggestion for the above case would be (as Mikael Eriksson says) to use constants - this is unless it's a cut down example and this isn't possible. The issue then would be the returned data type would always be nullable without being wrapped with ISNULL, CASE etc
C

SQL Replication groups in Windows failover clusters RRS feed
SQL, Database Testing • sql server failover • • courtlanda

2

0
Votes

2
Posts

0
Views

A

I believe, DFS replication is meant for files synchronization not for the databases. consider following options as database solutions/features in SQL Server. SQL FCI Since you already have SQL FCI in-place, the databases and data (program files) related to SQL Server are already in shared location which can be accessible to both nodes. Availability Groups Unlike FCI, availability groups hosted by stand-alone SQL instances, each instances called as replica, every replica maintains their own copy of databases and every change in the database replicated all database copies across replicas within a group. you can have 8 replicas, required enterprise license. WSFC required for auto-fail-over purpose. Log Shipping Log shipping works based on scheduled backups, which periodically backups the changes on primary server, secondary server copies backup files and restores regularly as per defined schedule. SQL Replication Above options considered as high availability solutions, but replication is something used for copying and distributing the database's objects
R

Cannot start SQL SERVER SERVICE in Windows Server 2000
SQL, Database Testing • sql server windows • • rielyn

2

0
Votes

2
Posts

0
Views

M

You don't mention the version of SQL Server you are running, however, are you up-to-date with updates? There have been fixes for Access Violations in SQL Server that you may not have at this time. Here is an MSDN post that has some relevance (though it is specific to SQL 2008): sql server 2008 is terminating because of fatal exception c0000005 Access Violations occur because the application has attempted to access memory areas that it doesn't own. That is a problem with code. And quite often there are service packs or updates that address these issues. Without knowing exactly which version of SQL Server you are running a more specific fix cannot be suggested.
Deny view definition at DB level and symmetric keys
SQL, Database Testing • sql server permissions sql server 2005 • • briley

2

0
Votes

2
Posts

0
Views

T

From BOL: any permission denial overrides all grants The implication is that when you DENY permissions to an object those permissions override any GRANTed permissions applicable to that object. Having said that, you don't need to explicitly deny "view definition" permissions on procedures and other code. If you don't grant VIEW DEFINITION the definitions are not visible, even if the code can be executed. Tables work slightly differently; you'll need to deny view definition to any tables the role has SELECT rights on. You can test this using the code below. Create a test database: USE master; GO IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921') BEGIN DROP DATABASE TestPerms_20160921; END; GO CREATE DATABASE TestPerms_20160921; GO USE TestPerms_20160921; GO Create a table, and a stored proc: IF OBJECT_ID('dbo.Test') IS NOT NULL DROP TABLE dbo.Test; CREATE TABLE dbo.Test ( TestID INT NOT NULL ); GO IF OBJECT_ID('dbo.TestProc') IS NOT NULL DROP PROCEDURE dbo.TestProc; GO CREATE PROCEDURE dbo.TestProc AS BEGIN SELECT 1; END GO Create a test user, and a test role: IF EXISTS ( SELECT 1 FROM sys.database_principals dp WHERE dp.name = 'TestU' ) BEGIN DROP USER TestU; END CREATE USER TestU WITHOUT LOGIN; IF EXISTS ( SELECT * FROM sys.database_principals dp WHERE dp.name = 'dbtestrole' ) BEGIN DROP ROLE dbtestrole; END CREATE ROLE dbtestrole; ALTER ROLE dbtestrole ADD MEMBER TestU; Grant SELECT and EXECUTE to the test objects: GRANT SELECT ON dbo.Test TO dbtestrole; GRANT EXECUTE ON dbo.TestProc TO dbtestrole; Impersonate the user so we can see the effect of the GRANT statements above: EXECUTE AS USER = 'TestU'; /* column names and types **are** visible */ SELECT s.name , t.name , c.name , ty.name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.columns c ON t.object_id = c.object_id INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id WHERE s.name = 'dbo' AND t.name = 'Test' ORDER BY s.name , t.name , c.name; /* access to view the definition is denied */ EXEC sys.sp_helptext @objname = 'dbo.TestProc', @columnname = NULL; /* we can run the proc! */ EXEC dbo.TestProc; REVERT; DENY VIEW DEFINITION on the table, and test again: DENY VIEW DEFINITION ON dbo.Test TO dbtestrole; EXECUTE AS USER = 'TestU'; /* no rows returned as we can no longer view the table definition */ SELECT s.name , t.name , c.name , ty.name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id INNER JOIN sys.columns c ON t.object_id = c.object_id INNER JOIN sys.types ty ON c.user_type_id = ty.user_type_id WHERE s.name = 'dbo' AND t.name = 'Test' ORDER BY s.name , t.name , c.name; /* we can still run the proc */ EXEC dbo.TestProc; /* we can still see the content of the table */ SELECT * FROM dbo.Test; REVERT; Cleanup: USE master; GO IF EXISTS (SELECT 1 FROM master.sys.databases d WHERE d.name = 'TestPerms_20160921') DROP DATABASE TestPerms_20160921;

Software to help automate windows os patching with SQL Server high availability/failover clusters

Suggested Topics

SQL Server help tables? SQL, Database Testing • sql server sql server 2008 • • rielyn

High Disk I/O from sql server or is High disk I/O slowing sql server? SQL, Database Testing • sql server perfmon • • simrah

Why does existing data not replicate when using custom stored procedures SQL, Database Testing • sql server transac languagetional re • • charlenef

SQL Server - global temporary table vs normal table SQL, Database Testing • sql server temporary tables • • irl

Configure cluster on an existing SQL Server - to setup AlwaysOn Availability Groups SQL, Database Testing • sql server availability groups high availability • • emran

SQL insert data simultaneous SQL, Database Testing • sql server • • guilherme

How to Make Group By with a Soma Field Software Programming • mysql sql server sql oracle firebird • • rielyn

Automated backups sql server IaaS SQL, Database Testing • sql server backup azure azure blob storage • • charlenef

How does SQL Server Startup? SQL, Database Testing • sql server sql server 2008 r2 • • kalyn

In-Memory OLTP SQL Server 2016 - insufficient system memory SQL, Database Testing • sql server sql server 2016 memory optimized tables • • guilherme

Is it possible to convert existing users from SQL Server authentication to Windows (AD) authentication? (SQL Server 2008 R2) SQL, Database Testing • sql server security ac languagetive direc lan • • emran

Why do case/iif return nullable datatypes? SQL, Database Testing • sql server t sql • • emran

SQL Replication groups in Windows failover clusters RRS feed SQL, Database Testing • sql server failover • • courtlanda

Cannot start SQL SERVER SERVICE in Windows Server 2000 SQL, Database Testing • sql server windows • • rielyn

Deny view definition at DB level and symmetric keys SQL, Database Testing • sql server permissions sql server 2005 • • briley

SQL Server help tables?
SQL, Database Testing • sql server sql server 2008 • • rielyn

High Disk I/O from sql server or is High disk I/O slowing sql server?
SQL, Database Testing • sql server perfmon • • simrah

Why does existing data not replicate when using custom stored procedures
SQL, Database Testing • sql server transac languagetional re • • charlenef

SQL Server - global temporary table vs normal table
SQL, Database Testing • sql server temporary tables • • irl

Configure cluster on an existing SQL Server - to setup AlwaysOn Availability Groups
SQL, Database Testing • sql server availability groups high availability • • emran

SQL insert data simultaneous
SQL, Database Testing • sql server • • guilherme

How to Make Group By with a Soma Field
Software Programming • mysql sql server sql oracle firebird • • rielyn

Automated backups sql server IaaS
SQL, Database Testing • sql server backup azure azure blob storage • • charlenef

How does SQL Server Startup?
SQL, Database Testing • sql server sql server 2008 r2 • • kalyn

In-Memory OLTP SQL Server 2016 - insufficient system memory
SQL, Database Testing • sql server sql server 2016 memory optimized tables • • guilherme

Is it possible to convert existing users from SQL Server authentication to Windows (AD) authentication? (SQL Server 2008 R2)
SQL, Database Testing • sql server security ac languagetive direc lan • • emran

Why do case/iif return nullable datatypes?
SQL, Database Testing • sql server t sql • • emran

SQL Replication groups in Windows failover clusters RRS feed
SQL, Database Testing • sql server failover • • courtlanda

Cannot start SQL SERVER SERVICE in Windows Server 2000
SQL, Database Testing • sql server windows • • rielyn

Deny view definition at DB level and symmetric keys
SQL, Database Testing • sql server permissions sql server 2005 • • briley