Thursday, January 7, 2016

Sitecore: Sending mails with local MTA

When you working with Sitecore EXM you obviously need to test sending mails (review) creted using EXM.
Sometimes you might get the following error in a error strip when try to send email from EXM.

Your Sitecore App Center credentials are incorrect, or you do not have a valid license. Correct your credentials in <a href='/sitecore/shell/Applications/SitecoreApps/Loader.aspx' target='_blank'>Sitecore App Center</a> or contact Sitecore Customer Service.



This is because Sitecore EXM is using the Sitecore AppCenter's Mail Transfer Agent (MTA).

If you are using the AppCenter MTA (Mail Transfer Agent), then you can sign into the AppCenter and let the AppCenter MTA to do the job for you.

In my case I don't won't this. I need to use my own MTA.
To do this you have to do the following:
1. Set the UseLocalMTA value to 'true' (default is false).
2. Set your own SMTP settings.

Q: Where is this 'UseLocalMTA' setting is ? How can I find it ?
A: Simple. Get a configurations of your website using this URL: 
http://<your_host_name>/sitecore/admin/showconfig.aspx
Then Search for 'UseLocalMTA'.
It will find you the following configuration.
<setting name="UseLocalMTA" value="false" patch:source="Sitecore.EmailExperience.ContentManagement.config"/>

Go to the patch.source using the file system of your website.
Then change the UseLocalMTA value to 'true'.




Tuesday, January 5, 2016

Sitecore 8.0 rev 150621 - Rebuild Reporting Database

When I'm working with our Sitecore integrated website, I noticed that the recent visitor details are not getting recorded in the Sitecore Experience Profile. The reason is to this is the Sitecore reporting database has stopped working or broken.

These are the steps that I followed to rebuild the Sitecore reporting database.

Pre-Steps

1. Get a fresh Sitecore analytics database from a fresh Sitecore instance. Attach it to the SQL Server using a proper name.

2. Get the WFFM post installation sql script (WFFM_Analytics.sql) and run it against the analytic database. If your website has installed the WFFM this sql script can be found inside [site root]\Website\Data

3. Get the ECM post installation sql script (Sitecore.EmailExperienceManager.sql) and run it against the analytic database.

4. Download and execute the WFFM.StoredProcesdure.AddFormStatisticsByContact.sql from https://kb.sitecore.net/en/Articles/2015/06/23/16/27/282795.aspx against the analytic database and do the requested change in the Sitecore.Analytics.Processing.Aggregation.config file.

5. Set db_owner user permission to the user for the analytic database.

6. Change the ConnectionString.config. Set the reporting.secondary property to the newly attached analytic database. Keep the reporting database to the existing database.

7. Change the time to clear the storage setting in Sitecore.Analytics.Processing.Aggregation.config file as bellow.

<reportingStorageManager type="Sitecore.Analytics.Aggregation.History.ReportingStorageManager" singleInstance="true">
        <TimeToClearStorage>0.00:05:00</TimeToClearStorage>

NOTE: Within this time we have to run couple of sql scripts against the analytic database. So make sure you set the time you need. (Rebuilding Steps 3)

Default : <TimeToClearStorage>0.00:01:00</TimeToClearStorage>

8. Delete content inside <DataFolder>/indexes/sitecore_analytics_index folder.

9. Restart the IIS website.

10. Ensure that the Segments table is not empty in current "reporting" database by running SELECT * FROM Segments against the current reporting database.

NOTE: If this table is empty, please copy data from a clean Sitecore reporting database or run /sitecore/admin/redeploymarketingdata.aspx

Example Query :
INSERT INTO DestinationDB..Segments
SELECT * FROM SourceDB..Segments
https://community.sitecore.net/developers/f/9/t/1028

11. Manually change the Deploy Date for the segment definitions in the Segments table of your reporting database to the date earlier than the first visit from your historic data was performed.

NOTE: if the deploy date is set to earlier year/date, then this step is already done.

12. Make sure the following config files are enabled.

  • Sitecore.Analytics.Processing.Aggregation.Services.config
  • Sitecore.Analytics.Processing.Services.config


Rebuilding Steps

1. open the rebuild reporting database history processing page in new browser window using the link: <hostname>/sitecore/admin/RebuildReportingDB.aspx

2. Click Start.

3. In the Rebuild Reporting Database page, when you see "WaitReadyToReceiveData" status, copy the following marketing definition tables from the primary to the secondary reporting database:

  • CampaignActivityDefinitions
  • GoalDefinitions
  • OutcomeDefinitions
  • MarketingAssetDefinitions
  • Taxonomy_TaxonEntity
  • Taxonomy_TaxonEntityFieldDefinition
  • Taxonomy_TaxonEntityFieldValue
Use the bellow sql format to execute the data.
INSERT INTO <DestinationDB>..CampaignActivityDefinitions
SELECT * FROM <SourceDB>..CampaignActivityDefinitions

4. Wait till "Process State : Completed".

Post-Steps

Do the following steps after status get Completed.

1. Change "reporting" connecting string "Database" attribute to Sitecore_Reporting_Secondary database and comment "reporting.secondary" connection string.

2. Check whether the TreeDefinitions table of your reporting database contains any records.

NOTE: If this table is empty, please copy data from the TreeDefinitions table of the current or clean Sitecore reporting database

3. Change the pre-step 7 to default (0.00:01:00)

4. Go to the Sitecore Experience Profile and check it contains the latest visit details.


ISSUE

Sitecore analytic database getting broken time to time.

This happen to database which has more than 20 contacts in the xDB.
To avoid this apply the patch provide by Sitecore from https://kb.sitecore.net/articles/440768