Trying to be clever, we turned on replication to update the new server with the latest data on the large table, but that broke the partitioning and compression.
By default replication will initialize the subscriber with a copy of the table from the publisher. This could have dropped your existing table on the subscriber.
On the physical disk, I can see ...
Those two numbers are measuring slightly different things. You're right that they are both measure redo, but they do it in different ways.
The Perfmon counter is updated in near-real-time - it's the number of bytes redone in the last second:
Amount of log records redone in the last second to catch up the database replica
The AG dashboard is based on the ...
You can try this trigger:
CREATE TRIGGER [logon_trigger_not_from_SSMS]
ON ALL SERVER FOR LOGON
IF APP_NAME() LIKE '%Microsoft SQL Server Management Studio%' --app name check
--the only persons allowed to use SSMS
IF suser_name() not IN ('sa','DBA_LOGINS')
and suser_name() not like 'dev[_]%'
These settings are stored inside the database. When a secondary becomes a primary, it is no longer a secondary, and it will adapt the settings for the primary.
Also, since this setting is stored inside the database and since a secondary is read only, you can only set these on one place: the one which currently is the primary.
One aspect that might not be the type of implication you are asking for, but IMO equally important is:
Application Name and HostName are set by the client app. So your developers can easily connect to your SQL Server using SSMS and in the connection string specify something like below:
Application Name=MyAppName;Workstation ID=MickeyMouse
(You can specify ...
No but you can create a script or a cron job that can take a copy of the files:
Such files are stored under C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA and are the actual database.
So if you take a copy of those two files and zip them together we can say that you took a backup of the database.
Answers to your questions below, this doesn´t necessarily mean that I think it is a good idea, it seems quite a high risk approach to me.
Yes restoring a standard edition db to enterprise edition just works as long as it is not on a later version than the destination server.
Restoring an enterprise edition to a standard edition works AS LONG AS you have not ...
Database mail uses a Service Broker queue and an asynchronous background process to actually send emails, so performance shouldn't be a big deal. But logon triggers can easily cause downtime, so they require a great deal of care in writing and testing. Also you could end up with thousands of emails if a developer runs a load test or somesuch.
So it's ...
I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that.
At insert rates of under 10,000/sec hot page latch contention is not a big issue, and the efficiency and locality of end-of-index inserts is preferable.
Change the primary key to be composite on (started, id) so it'...
While this might not be the answer you are looking for as it requires a migration in the end, you could try running your workload on SQL Server 2019 to be able to use "SQL Server 2019 Intelligent Performance -Worker Migration" or also commonly called Worker Stealing.
Worker migration (AKA “worker stealing”) allows an idle SOS scheduler
to migrate ...
Disable automatic failover for Node 3. This way it will always automatically failover between Nodes 1 & 2 only. If you encounter a scenario where one of these nodes dies and you need Node 3 to become an automatic failover node, simply change the setting.
For VIEW DEFINITION / VIEW ANY DEFINITION you are probably fine given that the developers likely already have access to the source-code.
For VIEW SERVER STATE, that does control access to a wide range of functionality, so you need to be more deliberate and cautious when granting this permission. The primary consideration is: How open-ended vs constrained ...
Few notes from Dan Guzman's link:
VIEW SERVER STATE
User will be able to use DMV's to look at queries.
If the queries or query parameters can contain confidential information that the user wouldn't otherwise be able to see,
allowing VIEW SERVER STATE would allow them to do so (i.e. dob = or ssn =)
From a security perspective, you run the risk of letting a ...
VIEW DEFINITION is a database permission. There is a corresponding server permission VIEW ANY DEFINITION you can add. If you want grant some logins access to view server state and any all object metadata, you can do it like this:
create server role developers
grant view any definition to developers
grant connect any database to developers
grant view ...
They seem logically equivalent.
They are. Even with ANSI_NULLS OFF "a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL."
I found an old feedback item for this here, you can go vote for it.
GRANT IMPERSONATE ON LOGIN::admin to notadmin is telling SQL Server that you want to give the IMPERSONATE permission to the notadmin account so that it can IMPERSONATE other users. It sounds like your goal is the opposite, that you want to impersonate the notadmin account from the admin account. You already are accomplishing this when you run the SQL code ...
It looks like replication was not set up properly; I'm guessing that that means that when a transaction gets written to the log and replication fails, the log entry doesn't get marked as ready-to-be-cleaned-up, so the entries just pile up and the log utilisation goes up with it.
I deleted all replication configuration, and the log file utilisation dropped ...