Posts

Showing posts from May, 2019

How to setup a public ip to a Azure virtual machine

Image
The below images will guide you how to create a public ip to an existing azure virtual machine. Wherever I highlighted in red click on those, If you click on "Virtual Machines" then it will show you the virtual machines that are available. Here I am changing the IP of "rameshdb" VM. If you click on "rameshdbVM", it will open another blade, and in "Over View" section "Public ip" showing as blank. And you can see the image below. Next you can go to the tabs that are under below image. Click on "Networking" tab, and it will route open another blade, and click on "Network Interface". And in the same image you can see "public ip" showing blank After clicking on "Network interface" it takes you to below image. Click on "IpConfiguration" and once you click on it another blade will open. So you also click on below link where "Public IPAddress" showing as blank...

Which user has what permissions on table level in all the databases.

DECLARE @Command NVARCHAR(4000) SELECT @Command='select db_name(db_id(''?'')) ,sys.schemas.name ''Schema'' ,sys.objects.name Object ,sys.database_principals.name username ,sys.database_permissions.type permissions_type ,sys.database_permissions.permission_name ,sys.database_permissions.state permission_state ,sys.database_permissions.state_desc ,state_desc + '' '' + permission_name + '' on [''+ sys.schemas.name + ''].['' + sys.objects.name + ''] to ['' + sys.database_principals.name + '']'' COLLATE LATIN1_General_CI_AS from sys.database_permissions join sys.objects on sys.database_permissions.major_id =sys.objects.object_id join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id join sys.database_principals on sys.database_permissions.grantee_principal_id =sys.database_principals.principal_id order by 1, 2, 3, 5' EXEC sp_MSf...

Replication is failing after doing fail over to Secondary replica in Always On

Image
To day i configured Replication on Always on High availability where i have One primary and two secondaries, I configured successfully first time, but i started facing issue after fail over. To overcome those issue we need to run the below commands.  Before you are running the below commands you have to remotely login into distributor server and run the commands. Dont access distributor server from publiser server and run these. You have REMOTELY LOGIN into the DISTRIBUTOR server. After fail over what happens is primary server become secondary and secondary server becomes primary, In these cases replication fail with an error message, if it is a Transaction publication and the error message would be "The process could not execute sp_replcmds on NODE1".  At this stage NODE1 became a SECONDARY replica and it is throwing error. At this stage we have to run the below commands, the below command will redirect every query to the Availability Group listener regardless of...

Rebuild system databases in SQL Server

Image
To day after moving system database files from one drive to another drive, I faced this problem. I am unable to restart the SQL Server instance, so I though I will rebuild all the system databases. So I ran below command in command prompt , I rectified the below error mentioned as well. 1) While executing the command in command prompt don't, change the pattern. Use the below pattern. Use the same parameter pattern /ACTION=REBUILDDATABASES /SQLSYSADMINACCOUNTS="RB.RAMESH"--> This could be your computer name or the logon account with which sql server services are running. /INSTANCENAME=MSSQLSERVER-->Provide SQL Server service name here, the name  that you are seeing under configuration manager. /SAPWD--> Don't get confuse with this SA password, this is not SA login password this is representing SA(system administrator) password,however you can also provide this when you are running sql server with mixed mode authentication. But while passing t...

Unable to connect to SQL Server instance by providing FQDN name

Today i got one of the errors in my environment, i am trying to access one of the sql server instances from another server. For example  i have two servers one is NODE1 and another is NODE2. I am  trying connecting to NODE2 from NODE1. If i give a server name as NODE2 it is not working but if i give ip address of the respective instance it is working. My challenge here is how to connect to SQL Server instance by providing the name.  I am getting error as name is not available, but if we see the server name  is existing. At this stage i follwed below process. I wen to below path in NODE1  the path is C:\Windows\System32\drivers\etc  and in etc folder i found "hosts" file. Right click on this file open with note pad. And add NODE2 ip along with FQDN name and save it. And then check now it will take server name, you can access SQL Server instance after you are adding the ip and FQDN name of that particular server. 192.168.10.11  NODE2.RAP...

FN_HADR_GROUP_IS_PRIMARY function

USE master go SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION dbo.[fn_hadr_group_is_primary] (@AGName SYSNAME) RETURNS BIT AS BEGIN                                 DECLARE @PrimaryReplica SYSNAME;                                 SELECT @PrimaryReplica=HAGS.Primary_replica                                 FROM sys.dm_hadr_availability_group_states HAGS                                 INNER JOIN sys.availability_groups AG                                 ON AG.group_id=HAGS.group_id                       ...