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

Anonymous Access to SharePoint 2013 Document Library

I want to share my experience when testing and creating a public share document on an intranet site that can also be accessed from internet.

Step to create

• Go to Central Administrator > Manage Web Applications
• Select web application you want to enable
• On the ribbon menu click on Authentication Providers
• Click on the Zone you want to enable anonymous access
• Mark Enable anonymous access and click Save

 

This will allow site to be share to anonymous user, but we need to specified which site to share.

• Go to the site you want to share
• Head to Site Settings > Site Permissions
• On the ribbon menu click on Anonymous Access

 

Then the library we want to share

• Select Lists and libraries and click OK to save this setting
• Head to document library settings Permissions
• On the ribbon menu click on Anonymous Access
• Mark View Items than click OK

 

Thinks to know

1. Anonymous user cannot access home site with this setting, so you need to send them link to that document library.
2. Anonymous can be set for 1 document library or list. But despite what this link told me, I didn't find how to share only 1 item.
1. If you Set Site Permission to Nothing, you won't find option to allow access view to anonymous user in document library.
2. To share only 1 item, you need to enable anonymous access to those library and break others items permission that you don't want to share
3. If you still ask for login to document library
1. Disable site collection feature "Limited-access user permission lockdown mode"
2. Enable anonymous to entire site
3. Enable inherited permission in target document library
4. Test access to site and document library (both should be accessible)
5. Enable anonymous to List and libraries
6. Disable inherited permission in target document library
7. Grant view to anonymous user
8. Test access to site (it should ask for login)
9. Test access to document (it should be accessible without login)
10. Enable site collection feature "Limited-access user permission lockdown mode"
11. All above combination did the trick for me.

 

Reference that I used to find this:

• http://www.dotnetsharepoint.com/2013/08/how-to-enable-anonymous-access-in.html
• http://blog.cloudshare.com/blog/2012/10/15/how-to-enable-anonymous-access-to-a-sharepoint-2013-site
• http://www.sharepointblogs.be/blogs/vandest/archive/2013/01/26/sharepoint-2013-and-anonymous-users-accessing-lists-and-libraries.aspx