Reinstall SQL Server Reporting Services 2016 in SharePoint Server 2016

Reinstall SQL Reporting involves 3 step, first I removed service application and proxy in SharePoint.

Then uninstall SQL Reporting service in Add or remove program. Here's the step to uninstall SQL Reporting:

1. Open Add or remove program, then select Microsoft SQL Server 2016.
2. Click Uninstall.
3. Click Remove from the form that show up.
   
4. When the wizard show up, select <<Remove shared features only>> under instances to remove features from.
   
5. Click Next.
6. Select both Reporting Services.
   
7. Click Next.
8. Click Remove.
9. Click Close when done.
   
10. Restart the server to complete the uninstall.

Install SQL Reporting Service again. Here's the step:

1. Insert SQL Server 2016 installation disk.
2. Run setup.exe.
3. Click New SQL Server stand-alone installation or add features to an existing installation.
   
4. Click Next on screen Microsoft Update.
   
5. Make sure everything green/yellow and then click Next.
   
6. Select Add features to an existing of SQL Server 2016 and then click Next.
   
7. Select both Reporting Services option and click Next.
   
8. Click Next.
   
9. Click Install.
   
10. Click Close.
    

We're going to activate this SSRS in SharePoint, make sure that this server SharePoint role is Custom.

Run the following PowerShell script:

Install-SPRSService
Install-SPRSServiceProxy
get-spserviceinstance -all |where {$_.TypeName -like "SQL Server Reporting*"} | Start-SPServiceInstance

A successful install won't show any message, in my case above the service SQL Server Reporting has already started so it showed a warning.
Next we're going to create Application Service for this SSRS. Go to Central Admin > Manage Services Application >  Click New > Select SQL Server Reporting Services Service Application.

Enter Name, select Application Pool (I used existing), make sure Web Application Association is selected to the web you want to associate with this service.

Click OK. After it has completed.

ref:

• https://docs.microsoft.com/en-us/sql/sql-server/install/uninstall-reporting-services?view=sql-server-ver15
• https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-or-uninstall-the-reporting-services-add-in-for-sharepoint?view=sql-server-2016
• https://docs.microsoft.com/en-us/sql/reporting-services/install-windows/install-the-first-report-server-in-sharepoint-mode?view=sql-server-2016

SQL Index for or

Let's say you have this query

select id from msEmployee where createdby='me' or createdbyemployeeid=2

index can't be used with or statement, so how do we modify the query so it will be using the index? There is one way to make use index for or statement. We need to separate the or query into its own syntax and join it together using union or union all (faster). So for query above we separate those two or into this query.

select id from msEmployee where CreatedBy='me'
union
select if from msEmployee where createdbyemployeeid=2

to measure your query performance you can use syntax set statistics on (before execute) and  set statistics off (after execute)

this is the result comparing between union, union all or just or statement (notice that union and union all doesn't seem to be that different however union all will perform faster because it won't force distinct the result)

this was the index definition for that table

ref:

1. https://stackoverflow.com/questions/751838/create-an-index-on-sql-view-with-union-operators-will-it-really-improve-perform
2. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/37adc0b7-e8f3-4fd4-bb0f-8e65d9e13a94/how-to-measure-the-performance-of-query-has-improved-after-index?forum=sqldatabaseengine

a list, survey, discussion board, or document library with the specified title already exists in this web site

This was shown when I was about to activate "SharePoint Server Publishing Infrastructure" in "Site Collection Features". I was pretty sure this happened because I was trying to import a list from another site to this and it failed mid way. Some of the items from those sites had already imported (also from publishing site) and it failed my attempt to activate publishing feature. How do we fix this? Simple by deleting that list/library. There is a catch however, that list wont show up in the "Site Content" menu.
We can use "SharePoint Designer 2013" to remove that list/library. This is what I did to remove that list/library:

1. Open up SharePoint Designer 2013
2. Click on All Files
3. Look for files/folder in the list which correspond to that name (example from picture Suggested Content Browser Locations)
4. If you couldn't find the name maybe it didn't have space in the name or it might have different name.
5. In my example picture above the name was not found in the list. So I searched the internet and figuring it out what it is. The name was PublishedLinks and it was a folder.
6. After you figure it out, delete it and try reactivated it again.

Update: I faced another error and this time there was no information about the name of the list that already exist. Since this was a new site, I solved this by delete the web application and recreate it again (also delete the content DB). This was the screen when that error showed.

Check custom field name for select & filter in API project online

A simple post to remind me how to check name to be used in REST project online web service. For example, I've this custom field "Initiative/Project ID". What would be the name when we want to call REST project online?
We could check the internal names using the following xml from URL:
https://{site collection name}/sites/pwa/_api/ProjectServer/CustomFields

But we couldn't use internal names for calling REST project online. REST project use custom field name and not internal ones.
However, we could get that name from PowerBI desktop.
Here's how I did it:

1. Open PowerBI
2. Click on GetData
3. Click on Other > OData Feed
4. Enter URL https://{site collection name}/sites/pwa/_api/projectdata
5. Mark Projects and Click Load
6. Search for your custom field on FIELDS list on the right
7. You will find your custom field name like this

So "Initiative/Project ID" will become "InitiativeProjectID", we could use this name to call REST project online. This is an URL example on how we could find & retrieve "Initiative/Project ID".

https://{site collection name}/sites/pwa/_api/ProjectData/Projects()?$filter=InitiativeProjectID eq 'PROJECTID001' and ProjectType ne 7&$select=InitiativeProjectID

Using Automapper to upload file from HttpPostedFile to entity File

If you are using Automapper, this is how to upload a file from input type file (HttpPostedFile) to domain entity (File). This is also updating existing entity if exist.

public static void Initialize()
{
    Mapper.Initialize(cfg =>
     {
      cfg.CreateMap<HttpPostedFileBase, File>().
         ConvertUsing((source, dest) =>
         {
          File result = dest ?? new File();
          if (source != null)
          {
              result.FileName = System.IO.Path.GetFileName(source.FileName);
              result.ContentType = source.ContentType;
              using (var reader = new System.IO.BinaryReader(source.InputStream))
               result.FileContent = reader.ReadBytes(source.ContentLength);
          }
          return result;
         });
     });
#if DEBUG
    Mapper.AssertConfigurationIsValid();
#endif
}

As you can see I use ConvertUsing, because I could built entirely new object or update existing destination (the dest variable). ConvertUsing also ignore validation of those type. This is the domain entity File definition.

    public class File
    {
        [Key]
        public int Id { get; set; }
        [Required]
        public string FileName { get; set; }

        [Required]
        [Column(TypeName = "varbinary(max)")]
        public byte[] FileContent { get; set; }

        public string ContentType { get; set; }

    }

And last thing, you need to call mapper using theirs method and not the property. Property will return a new entity, and method return existing one so it can be used to update EF domain.

I was using Automapper version 6.0.2 in an ASP.Net MVC project with code first Entity Framework 6.

Integration test with SQL express DB Entity Framework

There are many ways to do integration testing against database, most people will used moq or Effort on Entity Framework (EF). But there were limitation to what they could do. You need to learn how to use moq, and although there were many post on how to use moq. I haven't been able to mock DbSet Set method in EF6 which I used, which is indication that you need to learn how to use moq properly.

Effort on the other hand, provide an in-memory database from schema created by entity framework. This is nice, however it does have limitation. Effort can only be used to simulate object that goes through entity framework pipeline, specific SQL operation like stored procedure, function, or trigger won't work.

What about SQL Express DB, this is a real database engine and you could easily init those DB with data, and re-init before doing the test again. This also serve as end-to-end testing that will ensure your application will work against database.

Some people do this test using SQL Server also, but using SQL Server required you to set tear up and tear down script running before and after test. Using SQL Express you can always start your test with fresh DB with condition you setup will be exactly the same every time you run it.

This post will showed you how to do this integration testing using SQL Express. Developing tools will be vary, but I'll be using Visual Studio 2015 community edition and the project that I'll be testing on is SharePoint 2013 full trusted code (Visual web part, and other "separation of concern" projects).

Step you need to setup this test:

1. Create full SQL script from your DB
• Open up your database with SQL Server Management Studio
• Right click on it > Tasks > Generate Scripts...
• Click button Next on Introduction screen
• At Choose objects, choose "Select specific database objects", click button "Select All", and click button Next
• Select where you want to store this SQL file. This file will become database initialize for our integration test DB. I put this file under VS test project
• Click Next & Next again to built the script
2. Add database on your test project
• Open up Server Explorer
• Right click on Data Connections > Create New SQL Server Database
• Enter SQL express instance "(localdb)\MSSQLLocalDB" at Server Name (I used SQL Express 2016, if you used SQL Express version lower than 2014 you need different instance based on version, ex: SQL Express 2012 instance will be "(localdb)\v11".
• Enter New database name, and click button OK
• We're going to move this DB to test project, so delete that DB connection.
• Your DB will be reside under default location SQL express that is in the root of the user profile  (C:\Users\<user name>, copy that DB into your test project.
3. Create database test in that test DB
   • Connect to your newly copied DB in test project, by going to Server Explorer
   • Right click on Data Connections > Add Connection...
   • Enter "(localdb)\MSSQLLocalDB" at Server Name
   • Choose Attach a database file > select your copied DB in test project
   • Give a Logical name to make it easier
   • Open up your generated SQL DB > click on icon Connect
   • Select Local > MSSQLLocalDB
   • Under Database Name, select yours DB logical name you've entered before
   • Click Connect
   • Click on icon Execute
   • Take note/fix every error encounter in this execute script, we need that note whenever we want to recreate test DB
4. Create & execute others SQL script that will fill your test DB with Data, or other SQL objects like Linked Server. You can make that under 1 SQL file or separate it, just make sure that you know which one need to be executed before and after.
5. Each time project built or rebuilt, file which has "Copy always" attribute will be checked for versions. If the versions are the same, a checksum comparison is made and will be copied if checksum is not the same. This is how we keep the DB always new before each run test. When we added that DB file, it will have property "Copy always" in "Copy to Output Directory". In case it didn't have that, this is how we set property "Copy always"
   • Right click on SQL DB file > Properties
   • Select "Copy always" on property setting "Copy to Output Directory"
6. Built connection string to that DB in build folder
• the connection string will be something like this "Data Source=(localdb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\<DBFileName>.mdf;Initial Catalog=<DBName>;Integrated Security=True;MultipleActiveResultSets=True;App=EntityFramework"
• Notice that I use keyword |DataDirectory|, that keyword will be replaced by value set with method AppDomain.SetData("DataDirectory", objValue) the default value is application folder (build folder).

That's all you need to create this integration test with SQL express.

If you are on Test Driven Development, you can always start from creating SQL express DB and create script to be used in real environment.

ref:

• https://effort.codeplex.com/
• https://msdn.microsoft.com/en-us/library/dn314429(v=vs.113).aspx
• https://msdn.microsoft.com/en-us/library/0c6xyb66(v=vs.100).aspx
• https://msdn.microsoft.com/en-us/library/cc716756(v=vs.110).aspx
• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc31ea59-5718-49b6-9f1f-7039da425296/where-is-datadirectory-?forum=sqlce

SQL Server AlwaysOn Availability Group (AAG) Automatic Failover in Asynchronous mode

AAG has two commit mode that we can choose: Synchronous mode, and Asynchronous mode.
While synchronous offer zero data loss, this is not intended for disaster recovery site which usually located far from data center. Asynchronous mode offer better performance, however AAG with asynchronous commit mode doesn't allow automatic failover to secondary site.

SQL Server throws error number 1480 whenever there is a change between primary and secondary roles. We can setup alert that capture those error and switch over to secondary site. Since forced failover to secondary DB can be run using this command "ALTER AVAILABILITY GROUP [AAG Name] FORCE_FAILOVER_ALLOW_DATA_LOSS", we can use that command to switch to secondary DB using SQL alert. For more information on how to create that alert, please see reference 2.

Here's a video showing you that it can be done https://youtu.be/PhS4HhDHdFI

Reference
1. https://msdn.microsoft.com/en-us/library/hh781257.aspx
2. http://sqlmag.com/blog/alwayson-ags-and-sql-server-jobs-setting-failover-alerts