Troubleshooting Database Mirroring Error 1418

welcome to chandu stacknet in this video I will show you how to troubleshoot a database mirroring configuration failure right how to fix the issue recently one of my friend asked me like they are trying to configure database mirroring on their local machine or you know so they are trying to configure database metering and it is failing with this error right 1418 let's understand how to uh understand where the issue is and how to troubleshoot and fix that issue right before we jump into the demo let's let me let me give you a high level overview what is database mirroring database mirroring is a high availability and Disaster Recovery Solutions that we have in SQL Server right if you see here we have two servers right we call one as principal the other one we call it as a mirror or primary or secondary right Whenever there is a failure happen on primary right the automatic failover would trigger and the secondary database will become primary this automatic failover supports only if you have a inter the other server called witness server right so if you have a witness then with the help of witness Whenever there is a failure of one server the automatic failure happens whenever there is a failure on primary with the help of witness the automatic failure happens and the current secondary will become primary right that's it that is the high level overview of database mirroring right to config to have this feature we will have to configure database mannering let me show you how to configure what are all uh you know so the options that we have to configure database mirroring before we uh you know so configure so what are the prerequisites we should have to have a the uh like you know so the database with a full recovery model the recovery model of the database should be full recovery so I am using this database adventureworks LT 2014 option and I have a full recovery model because the data is replicated the data has to be replicated on the secondary server right so in terms in other terms like it will be a log blocks where it replicates to the secondary server and the database should be restored on the secondary with no recovery that is what I have done so if the database is online you would not be able to if the database is online on secondary you would not be able to configure database mirroring so you will have to have a database restore database on the secondary with no recovery right just to save our time I have restored the database I have backed up the database on primary and restored on the secondary with no recovery and to avoid any LSN mismatch I have taken one log back up and restored on the secondary with no recovery as well right test 2014 is a primary SQL Focus 2014 is the secondary in our test scenario right and this is a single machine or local computer where I have installed two SQL instances with 2014 right this is this is a word group computer whereas it is not added to any domains right since it is not added to the domain I don't have a any active directory service accounts so let's understand the SQL service is running under which account how to identify the service uh running under which account so you have different options where you can go to services.mscr configuration manager the same way you can check on the check from the DMVs as well right so let's start from sys.dm underscore server Services where you get the service account which SQL services are running right you have three whatever features Services I mainly concentrated on these two right local system my SQL service SQL is running under local machine local system this is a principle of primary let's check on the secondary as well cool the secondary the same thing the system is the services are running under local system account right now let's go ahead and configure database mirroring this is not a domain account again I'm telling this is not a domain added server so I'm using the local system accounts to run my SQL Server Services because I don't have any uh domain account to run on the server uh domain domain services or I would say like service accounts to run by SQL Server services let's go ahead and configure this database as a database part of database mirroring you can go to the properties whereas you have this option mirror right this is a principal server right you'll just have to configure next and it is asking whether you have a witness or not if you have a witness it supports automatic failover this is just to show you you know so the configuration failure I am selecting no so I don't have a witness server so I uh the automatic failure would not be supported if there is no witness I am okay with that it is asking whether you have a mirroring endpoint created or not right so when is this configuration the data flow between primary to secondary will be happened through a database mirroring endpoint right we call it as a endpoint mirroring endpoint right so if you you'll have to configure endpoint here and end point here the communication happens this server will connect to the endpoint and that this server will connect to the endpoint and share the data between these two servers the communication channel so endpoint we call it as a Communication channel so endpoint should be required to have a database mirroring even uh you know so always on availability group you should have to have a database mirroring endpoint now I do have database mirroring endpoint already created where you can check that go to server objects you have mirroring endpoint let's remove this endpoint right I just remove this endpoint just to make sure that you know so how this works if you don't have endpoint right right so I just removed the endpoint and let's see the database state is in recovery on the primary this is there now let's close this go to this property of the database which she is going to configure database mirroring if you click on mirroring you don't see this details right earlier we see principle time and all right let's go ahead and configure security next and it is the same screen I don't have a witness I'll go ahead and click next see here it is creating database memory so it the name can be given this name can be anything endpoint thank you right you can give any name here right I'll just leave that as it is you can rename anything endpoint database mirroring and point mirroring endpoint whatever it is right if the mirroring is not exist I mean sorry if the mirroring endpoint is not exist this wizard will go ahead and create the mirroring endpoint for us right see here if the principal mirror or Fitness are are instances on the same server so if you have on the same server then you should have to have a different endpoint port numbers right since this is a local mission where I have installed two instances on the same computer I will have to specify two different port number otherwise one port cannot be shared between multiple process just go to next and it is asking which one acting as a mirror server I will be selecting this and connect and it take us through this screen whereas earlier 5022 now it is 5023 next and it is asking to provide the service accounts here right for SQL servers under like if SQL Server accounts in the same domain or trusted domain specify the service accounts below right as I tell as I told you this is a work group where it doesn't have a uh domain accounts create domain accounts it is not added to the domain so this is not a trusted domain and where I don't have a domain accounts to be specified here right if you the accounts are non-domain right I it is not in domain account or the accounts are in untrusted domain leave the text box empty so what it is telling that if it is a domain accounts and those SQL service is running under those accounts then you will have to specify here right principle what service account it is running mirror what service account it is running so we know that principle is running principal mirror is running under the local account it is not a domain account so we now need to specify anything you can leave this as black what if you specify this principle and mirror domain accounts then it will be creating these logins will be created on principle and mirror server and it will grant the connect permission on the endpoint right the connect permission because this has to connect This Server and this has to connect this server to share the data right what is the communication Channel end point so primary should have a connect permission on the end point of secondary secondary should have a connect permission of endpoint on the primary right vice versa so that you should be able to establish a connection and share the data across the servers between the servers right I will leave this as blank because this is not a trusted domain accounts cool and this will create an end point s see here configure endpoint and principle server configure endpoint and mirror server if you expand this see here it is created with endpoint name database mirroring it is created right cool now let's go ahead and start the database pattern it is just giving the information what is the principal Network address uh mirror Network address right so what it is telling that these properties are not a fully qualified domain name right the network address that we specify in the TCP connection is not a fqdn since it is not a domain service when a machine it doesn't have a fqdn details right so it is just a local Mission it doesn't have a fqdn uh of the uh you know so network name right this is just a local mission right fqdn is nothing but a fully qualified domain name if you are a domain added server then you will have this domain account so like this for example if you see here this is your mission name this is your domain this is your port number right likewise I don't have any okay I'm okay like you know so this is fine I'll just click ok just yes to start the database mirroring let's see what happens right so we got the same error that I that is there in the uh you know so PPT right like 1418 error one for one eight what it says the server Network address cannot be reached or does not exist it says that it is not reached or doesn't uh exist right so we got some other details like check the network address name right the name as in like TechNet and that the ports for the local and remote endpoints are operational since it is only the same server I don't have a concept of remote endpoints right this is on the same server now what uh further we need to understand let's try to configure a database through T SQL there could be bug or there could be some issue with the gray right GUI so let's understand if you are able to configure database mirroring through T SQL okay let's go ahead and see DB mirroring so I have this you know so configuration let's see if you are able to configure on the uh so if you're able to configure using the T SQL script right here the database name is Adventure works cool the vice versa right like for example let's see okay for example this is your principle or primary this is your secondary right so you will have to secondary is a partner to this primary is a partner to secondary right so this is a partner between each others right so these are the your partners primary uh secondary is partner to primary primary is partner to secondary server so what you will have to do you will have to execute this command on the secondary this is what TechNet 50225022 is the port number for primary server where you will be creating as a partner setting as a partner so let's go ahead and execute this and see is already enabled for database mirroring it says the database is already enabled for database mirroring let's refresh and it is in middle of recovery because it is trying to establish a connection and it is failing so let's do remove database from the database metering right how to do that alter database database name set partner off right so if you execute this then the database will be removed from the mirroring refresh it is in restoring state right so you can execute the same thing on primary but I think it should be already deleted right not configured for database mirroring that is fine cool now let's go ahead and execute this on the servers right this is on secondary I'm executing and this is for primary so I'm just executing this on secondary secondary is added if you refresh it will be in recovery now let's go ahead and execute on the primary copy new query and execute this 5023 is for Mirror server let's see what exactly is going on it is executing so it takes it is taking time so it definitely fails right uh see here the server network name Network address cannot be reached or does not the same error as we see in from the uh through right GUI we see the same error right he says that address name there could be a possible like it might be an issue with this uh network name right it is expecting fqdn whereas we are providing this uh you know so local server time right it might be an issue I'm not telling that it is an issue with this name but just to isolate the issue let's try with the IP address that is you know so IP address of the machine right again just remove this database from the mirroring what is the IP address ping minus a this one okay since it is a local Mission it results with the IPv6 address but what I need ipv4 right how to get that minus 4. to get the ipv4 address copy right so this is let's execute this on the okay this is on primary it should be on the primary same thing it should be on secondary should be 5022 let's execute this secondary it is executing fine let's execute this on the primary same 5023 is a mirror server port number 10. number where we are executing this command on principle foreign because we are setting the partner of mirror to the principle right it gives the same error right server Network so definitely it is not an issue uh with the network name or IP address or something right let's understand if the issue is with the port there are chances the port might be blocked it might not be a remote port number but the local Mission the ports might be blocked but let's understand how to check whether the port is blocked or not you will have to go through you will have to test or telnet this port number and see tell net so what is the command for telnet what is the way that you can test whether the port is open or not you will have to use the telnet if it is not installed you will have to install the telnet it is it is a default I mean instead it is a like I would say like um you know so inbuilt feature where we will just have to enable that right and TechNet this is your computer name or server name whatever we consider as n space port number that is 5022 right if you see that a blank screen without any result I mean if you see the blank screen it says that Port is already listening right correct now if you see the same thing if Port is not listen or if the port is not open or if the port is not configured you will see something like this right connect to telnet and the port is failed right connection failed it is failed to connect so now we and first we tested 5022 now let's go ahead and test with five zero two three right this is result the the issue is not with the port number as well let's understand where else we can check and troubleshoot the issue right what we have done so far we tried to configure database mirroring from GUI it is failing with 1418 error whereas we tried with t SQL query and it is failing with the same error and we tested with the IP address it is failed with the same error right configure database measuring with the IP address it is filled with the same error then we tried I mean then troubleshooting part we tested the ports are enabled or not right we tested successfully ports are listening or ports are not blocked I I would say other way right so these four are common in any troubleshooting right so whatever it is either it is the same server or different server or you have domain account whatever it is the troubleshooting will be same now let's understand the failure let's go to the error log and see what exactly you see in the SQL error log right again like SQL error log you have different ways to read I use SP underscore read error log right I convert this I mean I am using this output as a text format this is a grid this is text see what a what uh you know so error you get you can filter with this 0 comma 1 comma for a database right let's see if we get anything for this database starting up database database measuring has been terminated for the database C uh 903 703 sorry database memory has been determinated for the database this is an information message only so it says that it is an information message only but let's see and just filtering with this mirroring right you have different filters you can use zero is for the current error log one is for the SQL error log if you specify 2 it will be a SQL agent log one is for SQL error lock and you can specify any filter here uh text something you can specify you will get the uh you know so whatever filter you use you will get that messages so I will get all the lines which has this mirroring string right if you see here has been disabled star database mirroring connection error and error record while receiving your data something right uh an ex connection was forcefully closed by the host right it says that the connection is closed by the host which host 5023 on primary so I don't have much like it says that the secondary is forcefully closed the connection let's understand why let's connect to the secondary server foreign SQL underscore Focus 2014 let's read the SQL error log again error log 0 comma 1 like that I will convert this into text execute what it says like it is trying to let's take the recent one right it says that starting up database the database is marked as restoring that's okay and it says the database mirroring login attempt by the login by the user failed with the error connection handshake failed the login doesn't have a connect permission on the end point so what it is telling this login since it is a what group it says that this login doesn't have a connect permission on the endpoint I told you right initially whenever you specify that domain accounts while configuring database mirroring it creates those logins and Grant the connect endpoint permission right if it is a domain account since we left it blank it is not and also going to create any account now which account it will create it says that this is your account it doesn't have a permission your group is nothing but your work group it is not a active it is not a domain it is a work group TechNet is a local Mission whereas uh dollar it is your local system account I would say right can I go ahead and create this login or first let's see whether this login exists or not security um security see here does this login what group slash tech net dollar doesn't even exist right so are we able to create this login let's see create login from Windows it says that Windows NT account does not exist or not formed but you you would not be able to create this account so what you will have to do if this is a local system account right so if SQL service is running under local system account this local system will be bind to an account called NT Authority slash system account right this local account will be bind to this account and Grant the connect permission right I mean with this with this account the connection between the servers would be happen right what what I mean sick much this is your principle this is your mirror mirror server and you are using local account as a service account service running on local account service running on local account right if it is a domain account if the service is running on domain account if services are running under domain account sorry domain account then okay so if the services are running under domain account those will be created those accounts login will be created if those are not exist and Grant the connect permission so what does it mean if it is a domain account let's say domain account domain XYZ slash ABC right here you have that x y z slash ABC if this account doesn't exist then this will create this account this login will be created here login will be created here and the login will be created here and it grants the connect 10 point per endpoint Grant connect permission on endpoint since it is a local system account right so it will not create any login right which login it has to create so that reason you will have account called NT Authority slash system account it bind to this entire the artist System account and it uses the enter that is the system account to connect between the servers right so what I mean this entire dot is less system account should have a endpoint permission on the servers wherever it is running under the local account right let's see now let's go ahead and Grant connect permission on the endpoint right agree this is the syntax Grant connect on endpoint so the endpoint name I am using endpoint name as mirroring to enter Authority slash system go ahead and execute we have granted copy this is on Focus the same thing on the other node as well right so we have granted connect permission on the end point now let's go ahead and initiate database mirroring start database mirroring right you should be able to I mean from here you can just click here let's see if it takes or not right see here synchronizes the database are fully synchronized right as soon as I have granted the connect permission on the NT Authority slash system account then I am able to configure database mirroring successfully right we don't have a witness so the reason it is uh you know so setting the property to High safety without automatic failover if you configure witness you can configure to high performance suppose if you select this and if you try to click ok uh it uh one second like you know so high performance okay it is not async like if you see here this is grayed out it is not letting you to enable this feature because it needs requires witness server instance it requires a witness server to have an automatic failure supported right so the you know so high level overview what we have done so the issue is we are trying to configure database mirroring on the same computer but the troubleshooting whatever we have done is same even if it is a domain account or if it is a service added to the domain or a different uh service we have but the configuration that we have on the local computer we have two instances with 2014 and uh trying to configure database mirroring between the servers using the local system account right and it is failing with 1418 and we have tested uh configuring a database measuring through GUI we were unable to proceed and we tested with the T SQL queries and it is failing with the same error and we have validated the port is enabled or not so how we validated the port we tested using the telnet right telnet the server name or fully qualified domain name space port number it like this it gives the as soon as you enter it tells whether the port is enabled or not our Port is listening or not right we have tested and we see that ports are listening without any issue then we verified the SQL error log and primary and we understand the connection is forcefully closed by the secondary server see here 5023 is for secondary on the uh primary right whereas if I go ahead and check the secondary server it says that the login failure to connect the end point which login since the service is running under the local account it is using work group that is your workgroup server and server name and dollar account is your local account it says that this account is doesn't have a connect permission right since it is a local computer account I don't have this account available so I cannot create this login with this account to Grant the connect permission right if your service is running under local account that will be bind to anti Authority slash system account where we will have to provide connect permission to this anti Authority system account on the endpoint to establish a communication between the database mirroring servers right I have just created or granted endpoint connect permission on the endpoint right I just use this grant connect on endpoint so you will have to provide the endpoint name wherever you you know so whatever name you have given to your endpoint you'll have to provide this to anti-authority slash system this has to be executed as it is on both the servers whatever endpoint name it it is and Grant connection once executed then you can just start the database mirroring from the V wherever you configure right that's it so this is how we should troubleshoot the database mirroring thanks for watching maybe I will upload more on you know so troubleshooting other scenarios or other areas like if you have anything specific please mention on the comment so that I will Repro the issue and will provide you the way how to troubleshoot the issue right thanks for watching have a nice day bye

Channel: Chandu's Technet

Views: 3,199

Keywords:

Id: hUysof6yutI

Length: 35min 5sec (2105 seconds)

Published: Sat Dec 17 2022