Recently we have got an weird issue reported by our content authors on production. They mentioned the content are not reflected on website after they been publish or even re-publish.
First I wanted to check does the Publishing service doing all right and publishing contents correctly. Checked and it was working absolutely fine. I did switch to web database and validated recently published content. After some initial trouble shoot, discovered on CDs, the cache is not been clearing upon successful publish.
This leads my thought to suspect event queue table may have been flooded with lots of entry and may need a clean.
Did a eventqueue table cleaning as mentioned here and things did not went to normal. Still published changes were not reflecting on the website.
Now, I need to dive into detail to understand what is happening in eventqueue table.
The Properties table holds the last event execution time stamp against all the CDs.
To get that last run timestamp follow below steps:
go to your web app and open the Kudu advance tool.
Navigate to Environment page from menu in header
Get the Machine Name from System Info section
Open SSMS and connect to Database
Open new query window for the database which is configured as your Event Queue database( in our case we have configured dedicated one. By default it is web)
Run below query to get the last run time stamp for that CD instance
SELECT * FROM Properties where Key LIKE '%WEB_EQSTAMP_<<MACHINENAME>>%' ORDER BY Value DESC
Replace<<MACHINENAME>> in above query with machine name from step 3 above
Depending on how may instances your selected CD is running, it will show you that may rows (by default there will be two rows for each CD. one is for Production instance and another for pre-prod instance). The value column holds the Time stamp value of last run.
Now, that we have the last run time stamp for the CD, we can get the list of all pending events for this CD instance by running below query.
SELECT * FROM EventQueue WHERE Stamp >= CONVERT(VARBINARY, <<TIMESTAMP>>) ORDER BY Created DESC
Replace the <<TIMESTAMP>> with the value from previous query.
By combining all queries together, the final query will look like below
SELECT * FROM EventQueue WHERE Stamp >=
(
SELECT CONVERT(VARBINARY, Value) from [Properties] where [Key] = 'WEB_EQSTAMP_<<MACHINENAME>>'
)
ORDER BY [Created] DESC
After all this investigation, it is clear that CD servers are not triggering remote events registered in dedicated event queue table.
Did little more investigation and found out wrong event queue configuration files has been deployed to CDs accidentally and that was causing this behavior. We placed the correct config files for dedicated event queue and the peace has been established on CD role again.
While setting my local environment for development on Sitecore 9.3 using docker, I came across below issue.
Docker SQL Connection Issue
[Win32Exception (0x80004005): The network path was not found]
[SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) +1341
System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) +159
System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) +382
System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) +307
System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) +198
System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry) +422
System.Data.SqlClient.SqlConnection.Open() +199
Sitecore.Data.DataProviders.Sql.DataProviderCommand..ctor(IDbCommand command, DataProviderTransaction transaction, Boolean openConnection) +113
Sitecore.Data.DataProviders.Sql.<>c__DisplayClass26_0.<CreateCommand>b__0() +48
Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover) +293
Sitecore.Data.DataProviders.Sql.<>c__DisplayClass29_0.<CreateReader>b__0() +30
Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover) +293
Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters) +281
Sitecore.Data.DataProviders.Sql.SqlDataProvider.GetContentLanguages() +169
Sitecore.Data.DataProviders.Sql.SqlDataProvider.LoadLanguages() +133
Sitecore.Data.DataProviders.Sql.SqlDataProvider.GetLanguages() +49
Sitecore.Data.SqlServer.SqlServerDataProvider.ExecutePreLoadItemDefinitionSql(String sql, Object[] parameters, SafeDictionary`2 prefetchData) +52
Sitecore.Data.DataProviders.Retryer.ExecuteNoResult(Action action, Action recover) +539
Sitecore.Data.SqlServer.SqlServerDataProvider.LoadInitialItemDefinitions(String condition, Object[] parameters, SafeDictionary`2 prefetchData) +237
Sitecore.Data.DataProviders.Sql.SqlDataProvider.EnsureInitialPrefetch() +333
Sitecore.Data.DataProviders.Sql.SqlDataProvider.GetPrefetchData(ID itemId) +62
Unicorn.Data.DataProvider.UnicornSqlServerDataProvider.GetItemDefinition(ID itemId, CallContext context) +242
Sitecore.Data.DataProviders.DataProvider.GetItemDefinition(ID itemID, CallContext context, DataProviderCollection providers) +156
Sitecore.Data.DataSource.GetItemInformation(ID itemID) +88
Sitecore.Data.DataSource.GetItemData(ID itemID, Language language, Version version) +32
Sitecore.Data.Engines.TemplateEngine.GetdefaultSectionOrder() +118
Sitecore.Data.Engines.TemplateEngine.InternalGetTemplates() +486
Sitecore.Data.Engines.TemplateEngine.GetTemplate(ID templateId) +184
Sitecore.XA.Foundation.SitecoreExtensions.Extensions.DatabaseExtensions.GetContentItemsOfTemplate(Database database, ID templateId) +126
Sitecore.XA.Foundation.Multisite.SiteResolvers.EnvironmentSitesResolver.ResolveAllSites(Database database) +63
Sitecore.XA.Foundation.Multisite.Providers.SxaSiteProvider.GetSiteList() +162
Sitecore.XA.Foundation.Multisite.Providers.SxaSiteProvider.InitializeSites() +105
Sitecore.XA.Foundation.Multisite.Providers.SxaSiteProvider.GetSites() +18
System.Linq.<SelectManyIterator>d__17`2.MoveNext() +265
Sitecore.Sites.SiteCollection.AddRange(IEnumerable`1 sites) +221
Sitecore.Sites.SitecoreSiteProvider.GetSites() +258
Sitecore.Sites.DefaultSiteContextFactory.GetSites() +253
Sitecore.XA.Foundation.Multisite.SiteInfoResolver.get_Sites() +60
Sitecore.XA.Foundation.Multisite.Pipelines.Initialize.InitSiteManager.Process(PipelineArgs args) +85
(Object , Object ) +9
Sitecore.Pipelines.CorePipeline.Run(PipelineArgs args) +490
Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain, Boolean failIfNotExists) +236
Sitecore.Pipelines.DefaultCorePipelineManager.Run(String pipelineName, PipelineArgs args, String pipelineDomain) +22
Sitecore.Nexus.Web.HttpModule.Application_Start() +220
Sitecore.Nexus.Web.HttpModule.Init(HttpApplication app) +1165
System.Web.HttpApplication.RegisterEventSubscriptionsWithIIS(IntPtr appContext, HttpContext context, MethodInfo[] handlers) +584
System.Web.HttpApplication.InitSpecial(HttpApplicationState state, MethodInfo[] handlers, IntPtr appContext, HttpContext context) +168
System.Web.HttpApplicationFactory.GetSpecialApplicationInstance(IntPtr appContext, HttpContext context) +277
System.Web.Hosting.PipelineRuntime.InitializeApplication(IntPtr appContext) +369
[HttpException (0x80004005): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)]
System.Web.HttpRuntime.FirstRequestInit(HttpContext context) +532
System.Web.HttpRuntime.EnsureFirstRequestInit(HttpContext context) +111
System.Web.HttpRuntime.ProcessRequestNotificationPrivate(IIS7WorkerRequest wr, HttpContext context) +724
The error speaks itself, that the CM and/or CD was not able to connect to underlying SQL server. SO, I immediately opens the SSMS and try to connect to my SQL server running as container on my local and I am successfully able to connect both using IP address as well as using alias(in my case it’s sql).
So what’s the issue??? When I am able to connect to SQL server running in container using outside of the Docker created network than why my CM and/or CD instance is not able to connect to it within the same network???
I check inward and outward binding ports setting in docker-compose.yml file for SQL service. Double check connection strings for server name, user name and password and that all looks correct.
Thanks to one of my colleague, who points out my docker-compose file was missing hostname for SQL server service.
I have added them to my docker-compose.yml for SQL, SOLR and XCONNECT and happy days.
Cooking with Sitecore 