Interview Tips

-

What are phantom reads?

PHANTOM READS: Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction.

What is Multisubnet Failover in ALWAYSON ?

If you are using client libraries that support the MultiSubnetFailover connection option in the connection string, you can optimize availability group failover to a different subnet by setting MultiSubnetFailover to “True” or "Yes", depending on the syntax of the provider you are using.

The MultiSubnetFailover connection option only works with the TCP network protocol and is only supported when connecting to an availability group listener and for any virtual network name connecting to SQL Server 2017.


An example of the ADO.NET provider (System.Data.SqlClient) connection string that enables multi-subnet failover is as follows:

Server=tcp:AGListener,1433;Database=AdventureWorks;IntegratedSecurity=SSPI; MultiSubnetFailover=True

The MultiSubnetFailover connection option should be set to True even if the availability group only spans a single subnet. This allows you to preconfigure new clients to support future spanning of subnets without any need for future client connection string changes and also optimizes failover performance for single subnet failovers. While the MultiSubnetFailover connection option is not required, it does provide the benefit of a faster subnet failover. This is because the client driver will attempt to open up a TCP socket for each IP address in parallel associated with the availability group. The client driver will wait for the first IP to respond with success and once it does, will then use it for the connection.



Comments

Post a Comment

Popular posts from this blog

Netezza Commands

-
Table Size  select tablename, used_bytes/pow(1024,4) as used_TB   from _v_table_storage_stat  where tablename  in (  select tablename from _v_table where tablename not like  '_VT_%' and tablename not like '_T_%'); Objects owned by a user: select o.objname, d.database, oc.classname, u.usename from _t_object o, _t_user u, _t_object_classes oc, _v_database d where u.usename='username' and u.usesysid=o.objowner and o.objclass=oc.objclass and o.objdb=d.objid; Tip: Use NZ_HELP to get   syntax of all commands Like -    nz_help | grep grant [nz@dfw86inzb-04 ~]$ nz_help | grep grant nz_db_group_access_listing           To show what groups have been granted access to what databases. nz_db_user_access_listing            To show what users have been granted access to what databases. nz_ddl_grant_group    ...
Read more

MS SQL Server Tips

-
Image
Unable to Shrink the DataFile If you are unable to shrink the datafile then try the following Error :  File ID 3 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Solution:   Add at least 2-3 MB to the data file and start shrinking again. Index Fragmentation Level SELECT distinct dbschemas.[name] as 'Schema', dbtables.[name] as 'Table',  dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, i.rowcnt as 'totalrows' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats....
Read more

MongoDB Version Upgrade 3.4 to 3.6

-
Upgrade of Mongo 3.4 to 3.6 Please follow below steps to upgrade from Mongo 3.4 to 3.6 Pre-requisites: 1.        Make sure your Mongo version running on Mongo 3.4 2.        Connect to Mongo Shell and run the below command on the Primary Node a.        db.adminCommand( { setFeatureCompatibilityVersion: "3.4" } ) POC:PRIMARY> db.adminCommand( { setFeatureCompatibilityVersion: "3.4" } ) { "ok" : 1 } POC:PRIMARY> 3.        Make sure you have the Mongo 3.6 binaries ready Upgrade Procedure: 1.        Stop the Mongod service on one of the secondary node Sudo /etc/init.d/monogd stop 2.        Un-tar the Mongo.3.6.tz file to a location [root@dfwlncmgdb-06 kolguk01]# tar -xvf mongodb-linux-x86_64-rhel62-3.6.3.tgz 3.          Replace the...
Read more