You cannot set IPAll to "no" on a failover cluster instance (as mentioned before), see:
How to configure SQL server to listen on different ports on different IP addresses?
More than likely, though, you are wondering how to secure your failover cluster instance of SQL Server to a certain IP address, or to verify that it isn't indeed listening on all IP addresses registered.
The failover cluster instance will be listening on the IP address(es) registered for the virtual network name in the cluster resource group with the Windows failover cluster.
But you want to verify which IP addresses SQL Server is listening on?
select
ip_address,
port,
is_ipv4,
type_desc,
state_desc
from sys.dm_tcp_listener_states;
That will give you the real-time snapshot of what IP addresses SQL Server is listening on. That'll be your verification.
I found that it is a new feature (don't know what is use of it)
Before SQL Server 2012, identity allocations were always individually logged (as each value was used). This per-row logging activity could limit throughput in scenarios where many identity values are generated in a short space of time. To improve efficiency, SQL Server 2012 (and later) logs only the allocation of a batch of identity values. The allocated range is cached and issued on demand until a new batch of values is needed.
If SQL Server is restarted without the database containing the identity object being checkpointed on shut down, any remaining unused values in the cached range are lost, resulting in a jump in the values on restart.
Unfortunately, common ways of shutting down SQL Server 2012 do not currently automatically checkpoint databases (this contradicts the documentation, so it should be fixed at some point in the future). To avoid this particular cause of jumps in assigned identity values on SQL Server 2012, always shut down SQL Server using the T-SQL
command SHUTDOWN
(without the NOWAIT
option).
The SHUTDOWN
command will checkpoint all user databases correctly before shutting down the server. Do not use the Windows Service Control application, SQL Server Configuration Manager, the SQL Server Management Studio UI, or any other method.
You could also manually CHECKPOINT
all databases before shutting down the SQL Server using any other method, but this requires you ensure no identity-allocation activity occurs in any database after your checkpoints, and before the shutdown completes. This may not be easy to achieve reliably.
More information and background in Lost Identity by Kalen Delaney.
Trace flag 272 is now documented in DBCC TRACEON - Trace Flags (Transact-SQL):
Disables identity pre-allocation to avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server. Note that identity caching is used to improve INSERT performance on tables with identity columns.
Note: Starting with SQL Server 2017, to accomplish this at the database level, see the IDENTITY_CACHE option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).
Scope: global only
Best Answer
You can cycle the database by doing:
And then to bring the database back online you can do:
This is a way to "restart" the database itself directly within SQL Server, without bouncing either the SQL Server service(s) or the server operating system itself.
As for the question of "would that help?", there is no way for us to answer that. You should be asking the vendor why do I need to restart the database server? Oftentimes people that don't know what they are particularly doing will cycle an entire environment after a migration/upgrade/change. Why? "Just in case".
If the vendor made a change directly to the database (DDL/DML, whatever) then that change takes effect immediately. So you really need to follow up with the vendor to find out why they want the database server restarted.