How to sign a SQL Server CLR Assembly in Visual Studio 2015

It took me quite a while to figure out how to sign a SQL Server CLR Assembly (like a CLR stored procedure / function / trigger etc) in VS 2015.

If you google “sign a SQL Server CLR Assembly”, you will most likely find out content published 5+ years ago when people are using Visual Studio 2010/2012. You will likely to see some instructions like

You can go to Project>project properties> “Signing” tab

With a snapshot screen like the following

image

Fig 1

But such snapshot simply does not exist in VS 2015.

If you check MSDN about signing an assembly in Visual Studio as documented here, you will have no clue either.

“You sign an application or component by using the Signing tab of the project properties window (right-click the project node in the Solution Explorer and select Properties, or type project properties in the Quick Launch window, or press ALT+ ENTER inside the Solution Explorer window). Select the Signing tab, then select the Sign the assembly check box.”

because in a SQL Server project created in VS 2015,  a project property window will be like the following

image

Fig 2

The “signing” button is actually in the [SQLCLR] tab as shown below

image

Fig 3

So the basic procedure to sign a SQL Server Assembly with strong name key file is:

1. In VS 2015, solution explorer, right click the project name, choose [properties], or in the menu [Project] click [xxx Properties], where xxx is the project name.

2. Fig2 widow will popup, click [SQLCLR] tab, and Fig3 will show up, click [Signing..] button, Fig 4 window will pop up

image

Fig 4

3. click the check box of [Sign the assembly], and then click <New…> in the drop-down list as shown below (of course, if you already have a key file, then you click <Browse…> )

image

4. Fig 5 window will pop up, and you can input the necessary info to generate a key file

image

Fig 5

5. Then click OK, and again click OK to close Fig5 and Fig 4, and then in [File] menu, click [Save All].

You are done to finish signing a SQL Server CLR assembly.

Advertisements
Posted in SQL Development | Leave a comment

CLR Stored Procedure Cannot Be Marked as System Object?

It is an open secret that in SQL Server, we can “create a system object” by using an undocumented stored procedure sp_MS_marksystemobject as explained here.

The benefit is that when you put a SP in the [master] database and mark it as system object, this SP can then be accessed in any other databases without using three-part naming convention.

I recently tried to create a CLR stored procedure and put it into [master] and mark it as system object, but I find this is NOT doable, while on the other hand, a pure T-SQL stored procedure has no problem being marked as a system object.

Here are the two simple examples just for proof purpose , 1st is a pure T-SQL.

use master
drop proc dbo.sp_Test;
go
create proc dbo.sp_Test --(@db sysname) 
as
begin
	print 'Hello World'
end
go
exec sp_ms_marksystemobject 'dbo.sp_Test'
go

and the 2nd is C# code for CLR stored procedure

using System;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure(Name = "sp_test2")]
    public static void sp_test2()
    {
        SqlContext.Pipe.Send("Hello World" + Environment.NewLine);
    }
}

After deploy to [master] database as [dbo].[sp_test2], and run

use master
exec sp_MS_marksystemobject 'dbo.sp_test2';
go

Now if I run the following code in [TempDB], everything is fine though sp_test is actually in the [master] database not [TempDB]

image

However, if I run sp_test2, there will be an error

image

But if I use three-part name convention to run sp_test2 as shown below, everything will be fine just as expected

image

If I check the property of the two SPs

use master
select name, type, type_desc, is_ms_shipped 
from sys.procedures
where name in ('sp_test', 'sp_test2')

select [sp_test]=OBJECTPROPERTYEX(object_id('sp_test'), 'ismsshipped'), 
[sp_test2]=OBJECTPROPERTYEX(object_id('sp_test2'), 'ismsshipped')

I will get the following result:

image

This is an interesting finding, and I think I’d better blog it here for my own reference.

This has been tested in SQL Server 2016 Developer Edition.

Posted in SQL Development | Tagged | 1 Comment

What Is Your Career Investment Portfolio?

Every year, financial advisors will recommend their clients to take a review of their investment portfolio and make necessary changes according to one’s financial reality, like whether you are going to be married, to have a baby soon, to have a new house or to change your career etc, so as to make the best return within the risk tolerance boundary.

Actually, to me, an IT professional should also do something similar annually, i.e. review our professional skills, which is arguably the most valuable asset to a professional, and make corresponding change to their skill investment.

In an investment world, roughly there are three types of portfolios, Conservative, Balanced and Growth, they correspond to different combinations of return and risk.

Portfolio Type Risk ROI
Conservative low low
Balanced medium medium
Growth high high

I think this concept applies to our IT professional skill investment as well, I will use SQL Server DBA skills for illustration later.

I define career investment portfolio as the following (do not pay too much attention to the percentage number, it is just used as an illustration to indicate the relative quantity)

Portfolio Type

Definition

Conservative 100% of your resources (time and budget) on enhancing existing skills and knowledge that are needed to handle the 80% of your daily work.
Balanced 50% of your resources on your existing skills / knowledge that handle 80% of y0ur work, and another 50% on the knowledge and skills that are required for the 20% of your daily work.
Growth 100% of your resources on the knowledge and skills that are required for the 20% of your daily work OR those not directly related to your current work.

 

For example, as a DBA, if I choose Conservative approach, I will invest my resource to deepen my SQL Server internal knowledge, research more on SQL Server Extended events, understand better about various physical/logical operators in Query Plan, practice new T-SQL features etc. work more on service brokers, High Availability setup / troubleshooting, XQuery to manipulate XML, explore more about new features in SQL Server 2016 even if I am only working on SQL Server 2014 (or below) currently.

If I choose Balanced approach, I will also spend time on improving my skills on SSAS/SSIS/SSRS trouble-shooting, development and I may even spend time in master data service. I will also spend time in PowerShell to make my administration more automated or spend time in C# to learn how to create various CLR objects.

If I choose Growth approach, I will spend most of my resources in learning some trendy IT knowledge which may or may not help me as a DBA, such as development in iOS, Android or Linux (SSRS is now supporting mobile report, while SQL Server vNext will run on Linux), or Web development using various Google Frameworks (Google chart is my favourite topic and can be useful for DBAs when we need chart presentations), or other stuff like Docker, Chef, Hardoop or various AWS / Azure services. The new skills may greatly improve a DBA’s productivity in one way or another, but the resources needed and the risk that there is no ROI for your current work is also big.

All the investment targets are dynamic in nature. For example, if 10 years ago, learning PowerShell would be considered an investment in Growth portfolio because there is some risk to its ROI, but now it is mature and is adopted and hailed by the IT community with MS keeping on investing on this technology, so it should be considered as a good investment in Balanced portfolio for DBAs.

In career investment, there is always an opportunity cost. I believe everyone needs to make the decision based on one’s job stability, work requirements, personal aspiration and investment time window (long term or short term).

Posted in Career | Tagged | Leave a comment

SQL Server 2016 Bug in R Integration?

 

I am studying the latest R integration with SQL Server 2016, and I believe I encounter a bug when using sp_execute_external_script. So here is the re-play of the bug;

If I run the following code, I will get an error.

 

-- this will return an error

exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
, @output_data_1_name = N'result' -- the position of this param caused problem
with result sets ((test varchar(100)));

/* -- the following error will occur
Msg 8144, Level 16, State 2, Line 12
Procedure or function  has too many arguments specified.
*/

However, if I put @output_data_1_name in front of @params , everything works fine.

-- this will work fine 
exec sp_execute_external_script @language=N'R'
, @script = N' result <- as.data.frame(c(i, j));'
, @output_data_1_name = N'result'  -- correct position !
, @params = N'@i varchar(100), @j varchar(100)'
, @i = 'hello'
, @j = 'world'
with result sets ((test varchar(100)));

/* -- this will return the expected result

test
----
hello
world
*/

So in short, it seems a named parameter’s position may cause the stored procedure to fail?

My environment is SQL Server 2016 Developer Edition with the latest SP1 + CU1, as shown below

select @@version

-- returns
---------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2016 (SP1-CU1) (KB3208177) - 13.0.4411.0 (X64) 
	Jan  6 2017 14:24:37 
	Copyright (c) Microsoft Corporation
	Developer Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: )
Posted in Administration | Tagged | 1 Comment

A DBA’s Developer Experience and After-Thoughts

After 10+ years of a dedicated DBA, for the past 17 months, I have purposely chosen to work as a database developer. I decided to do this mainly for two reasons:

1. I want to have a career travel to see whether the grass is greener on the other side.

2. I want to dedicate more time to sharpening my scripting skills in PowerShell, T-SQL and C# and some peripheral items such as XML, CSV and Log processing, which are related to DBA work in one way or another.

Now looking back, I feel the time is well spent. I actually find a lot of fun in developing various solutions for business, and some solutions may easily be transferable for DBA work,

In the first 6 months, I was working in an “Entity Framework Code First” environment, it was interesting initially, but later I felt uncomfortable when data model (from DBA perspective) kept changing somewhat on the fly when each developer team was responsible for a set of tables themselves and each team had the authority to make changes to the underlying tables with high-level agreements among team leaders. But I was the person who needed to migrate the data from old system to the new system thus I relied on underlying tables. With frequent changing of tables (from table name change to column changes to even tables merging and dropping), I kept on changing my code, re-test and then change again and again and again. It is not only me to do all this work, my business analyst had to schedule meetings with me to explain to me the changes and what I should do accordingly etc. It was very chaotic to say the least.

The “code first” principle is so different from my previous experience with data model centered approach, and IMHO, “code first” is probably best fit for small projects with few developers, and little dependency or coupling among product features when using the underlying tables..

In the next 11 months, I was involved in a project that dealt with various files, CSV, Excel, flat text files, XML files etc, and I used PowerShell and SQL CLR functions together with t-sql, bcp utility to process these files, it was fun and challenging. Currently in SQL Server 2016, we can run R script inside a t-sql script, I really hope someday, we can run PowerShell script (or C# script) inside a t-sql script too, this way, we will have one common interface, i.e. T-SQL for all sql server related programming tasks, like stored procedures and functions.

What have I learned as a database developer (after being long time DBA) ?

1. There is a bigger and fun world outside. Lots of SQL Server features can only be appreciated thoroughly when you see they are used to solve tough business issues, such as Service broker, encryption, fancy SSRS reports, columnstore index and various T-SQL windows functions.

2. Under deadline pressure, a developer always puts function/feature as first priority, while treating others, like performance, maintainability etc as secondary consideration. So now when I see some spaghetti codes, like in a select statement, each column is a subquery or select * from (select * from) etc, I somehow do not feel that frustrated as before.

My observation is that majority of developers, whose main program language are not SQL, are writing t-sql codes which are far from the expectations a DBA wants in terms of quality. That may explain why some architects whose skills are in C# / .Net may favour “Entity Framework Coding First” approach to handle business projects.

3. The best DBA is usually ignored by management while the best developer always catches attentions from management because DBAs are proud of “no news is good news” while developers enjoy broadcasting an implemented feature or achieving an critical milestone.

At the end of the day, I was asked which role is more interesting, developer or DBA? My thought is:

DBA is more suitable for people who enjoy keeping on polishing a craft, to make it better and better.

Developer is more suitable for people who like to create new crafts, one after another, non-stop.

So I’d say the grass is equally green on both sides. But with SQL Server vNext goes to Linux/Unix, SQL Server DBAs may find themselves in higher demands in market.

I personally believe it is beneficial for a DBA to plunge into a developer role once every few years. This experience will not only enrich one’s skills but also improve one’s understanding on the other side.

Posted in Uncategorized | 6 Comments

IT People’s Technical Debt and Reputation Credit

In Canada and USA, when people apply for a credit card or a loan/mortgage, the lending institutions will always do a credit history check against the applicant, and based on the result, they will decide whether to approve the application or decide what loan rates the applicant will be charged. This practice no doubt greatly reduces the potential default risks the financial institutions may take.

On the other hand, because of this practice, most people are very careful about their financial well-being, and everyone tries the best to be responsible for their financial decisions so they can avoid a bad credit history. This is such a good win-win scenario from both social and personal perspectives.

I have been in IT industry long enough to see lots of bad results because of improper trust bestowed to people who are assigned or hired to take on some tasks. The most disturbing thing to me is these people can leave for another job without any “credit trace” left behind for whatever “damage” they have done to their projects / work and may repeat their faults again (because they may not even realize their faults in some cases, I will give an example later).

I define “Technical Debt” as the negative impact (to either an employer or a community involved) an IT professional has caused through his professional work, and “Reputation Credit” as the positive impact. I always dream that if there exists an organization that can collects all IT professionals reputation credit history and with some formula, can assign a credit score to each person, then there will be far fewer failed IT projects and far less wasted time and money in various IT activities. For example, it will be easier for companies to hire people and make the justified compensation package based on one’s credit score just like banks will charge you less rate for a loan when you have a high credit score, and it will also be much easier for decision makers to decide whose recommendations may be less risky to follow thus less possible to have a project slipped into mess.

Here are the two true stories that I believe if we had such “Reputation Credit Bureau”, there would be different outcomes:

1. A Canadian IT company has a new CEO hired, the company has been service-oriented by providing out-sourcing work on developing applications and consulting service and has been profitable all the time, the new CEO, for whatever reason, tried to change the company’s business model by developing a hardware product, and as such, spending millions in setting up a new office in US, after two years, it seems the hardware product does not achieve the expected response from the market, now the CEO left the company and got hired in another start-up company in Silicon Valley. The Canadian company had to wrap up the product project and dissolve the US office and also laid off 40% staff in Canadian office. It left a bad taste to all people affected.

I bet the CEO should get a very bad score (to the brink of bankruptcy) for her technical debt owed to this Canadian company and to all those laid-off people both in Canada and US offices. I also bet the start-up company in Silicon Valley will have a second thought if it run a “reputation credit history” report on this candidate.

2. A company started a multi-million dollar project by using some fancy technologies, in the initial discussion of the technical design, some proposed design points, for example, data modelling, are strongly objected by the DBA team who have been dealing with data modelling and sql server technologies all their career lives, but decision makers finally takes the opinion of the developer team, in which the technical architect is a member (who himself has very little knowledge of the complex data modelling). The result is a huge productivity underperformance, and the project was delayed again and again. The funny part here is those who strongly recommended the design and the methodology already left the company within 1 year, and those who remain are struggling to deal with the issues caused by such a flawed design. I’d believe those who initially proposed such architect design truly believe this is the best choice, but what puzzles me is how one can (or dare) stick to their opinions when someone else with more domain knowledge on the topic are against their opinions? In an analogy, it is like I dare to stick to my t-sql solution proposal when Itzik Ben-Gan recommended otherwise after reviewing my proposal. It is just beyond my understanding how this could ever happen.

I keep on thinking that if there is such technical debt thing, those who do not have enough domain knowledge will be very cautious to propose something that they are not sure of themselves, knowing there will be “penalty” (i.e. low reputation credit score) down the road if things go south, and as such, the correct recommendation from the DBA team may be adopted (for the data modelling part), thus avoiding the current headaches.

Of course, IT industry is not a financial industry, so this “reputation credit bureau” will not come to reality any time soon, but I still believe with spending in IT industry so big (thus big stake in financial well-being to companies), someday, such “reputation credit bureau” organization will be created in one form or another, and it will be beneficial not only to employers(who can hire the best people they can afford) but also to individuals (who will know that they will be fairly compensated). Such “bureau” can sustain itself by charging for any credit history queries just as the current credit bureau does (Equifax / TransUnion).

I am looing forward to that day…

Posted in professional development | Tagged | 11 Comments

New Year Wish List for SQL Server Features

 

With SQL Server 2016 to be officially released this year, I want to make a wish list for the features I really dream of, and see when / whether this list will come true down the road.

1. Revolutionize SSMS with features like those in MS Office Word / Excel

With MS announced their intention of release SSMS independently of SQL Server database engine in June 2015, I wish SSMS, for its t-sql editor part, can be as capable as current MS office Word and Excel applications. For example, I can arbitrarily set color / fonts to any portion of codes. The business reason behind is that I can make codes easier to carry coding convention or business rules. For example, I can make all “Delete” as red and big sized font.

On the other hand, when we query a table, we get a spreadsheet-styled result set, which is similar to Excel, but in Excel, we can automatically create a graph based on a result set. Isn’t it wonderful that we can do similar things in SSMS ? i.e. when we do a query, we get both the query result and along side is the graph (like a pie or line) based on the result set?

2. Revolutionize SQL help

I always enjoy the style and simplicity of help mechanism in PowerShell, all you need to do is run:

Get-Help –name <KeyWord>

Where KeyWord can be a cmdlet name (either system or user defined) or some specific word, like about_operators.

In SQL Server world, if you create an object (a SP, trigger, function etc), you can use sp_help to find the metadata of the object, but there is no way for you to see any user-created information in the object (unless you use sp_helptext to read the object body script)

Besides this shortcoming, another one is we cannot display any syntax for regular T-SQL statement. For example, you cannot use sp_help ‘create index’ to find the syntax of “create index”

It would be very productive if we can see syntax of t-sql just within SSMS itself, and we can build a help framework to allow developers put the “help” info in the stored procedure/functions/views to facilitate the understanding of these objects.

3. Incorporate PowerShell into T-SQL

Just like SQL server 2016 supports R language, it will be great if we can mix t-sql with PowerShell, that will open a door to many innovative scripts. For example, currently, to delete any backup files older than X days before starting the backup is not too simple. (We need to use SSIS package or sql agent job to handle this.)

4. Create Conditional Foreign Keys

Currently foreign key (FK) constraint serves the referential integrity check. But as a data architect, I find traditional FK constraint implementation is pretty “ugly” in real world design.

Think about this example: 

I have a [CUSTOMER] table, and each customer may have multiple phones, and so we will have a [Phone] table, inside which, we have a Customer_ID column referencing to [CUSTOMER] tables’s PK, i.e. [Customer_ID].

Now I also have a [EMPLOYEE] table, and each employee may have multiple phones as well, so I have to create another [Phone2] table, inside which, we have a Employee_ID column referencing to [EMPLOYEE] tables’s PK, i.e. [Employee_ID].

Now let’s say I also have [ServiceProvider], [ATTORNEY], [TRUSTEE] tables, each may have multiple phones, so strictly speaking, we need to create a [phone] table for each corresponding owner table. This is a mess. But let’s say, I’ll create a brand new table [phone] as follows:

CREATE TABLE dbo.PHONE (
  phone_number varchar(20)
  phone_type varchar(10) — = mobile, home, office, fax etc
, owner_id int
, owner_type varchar(20) — = Customer, Employee, ServiceProvider, Trustee, etc
)

This table has a problem in implementing FK constraint, i.e. there is no way for me to set up a FK relationship using [owner_id] to reference a PK in [CUSTOMER] or [EMPLOYEE] table.

What I really dream of is that SQL server can create a somewhat “conditional” FK, i.e FK is set up based on a condition. In this case, when [owner_type]=’Customer’, owner_id will reference to [CUSTOMER].[Customer_ID], when [owner_type]=’Employee’, owner_id will reference to [EMPLOYEE].[Employee_ID], and so on so forth.

Using pseudo code, it should be like the following:

Alter table dbo.Phone add Constraint [fk_phone_owner] Foreign Key
with [owner_id] reference to
case [owner_type] when ‘Customer’ then [Cusomter].Customer_ID
                  when ‘Employee’ then [Employee].Employee_ID
                  when ‘Trustee’  then [Trustee].Trustee_ID
end

Currently, this type of foreign key integrity relationship can be implemented via triggers, but every DBA knows trigger is best to be avoided whenever possible. So if SQL Server engine can implement such conditional FK integrity, it is great to solve lots of real world headaches.

5. Introduce parallel maintenance for SQL Server instances.

These days, most of sql server environments contain multiple big databases and the maintenance of these databases, such as backup / restore / index maintenance can be very time-consuming if doing things in sequence. Isn’t it nice if we can do all the backups in parallel with one t-sql like the following (pseudo code)

Backup Database [A], [B], [C], [D] to disk=’d:\a.bak’, ‘d:\b.bak’, ‘d:\c.bak’, ‘d:\d.bak’

and then there are four sessions (or threads) that do the backup at the same time?

6. Enhance handling mechanism for blocking

Blocking is so common to every DBA, it is annoying yet unavoidable. To me, one of the most annoying case is that during index maintenance, one index reorganizing (or rebuilding) is being blocked for long time that may exceed out of the maintenance window, thus cause the minimum accomplishment and a waste of the maintenance window. Starting SQL Server 2014, “alter index” has the option to kill itself or the blocker during blocking after waiting for some user-defined time (check <low_priority_lock_wait> option).

But from a DBA perspective, I think this option should be extended to many other DML T-SQLs, like Insert / Delete / Update, and any other T-SQLs that may be a blockee (such as Truncate, Select, or partition related operations etc).

What’s more, I want to know who is the blocker and better, the whole blocking chain. My imagined pseudo option should be as follows:

<low_priority_lock_wait>::=

{ WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,  ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } ) ,

Save_Blocker_Info = <xml> }

Where <xml> can be an XML variable that records the blocking chain info. This info should be populated once MAX_DURATION is achieved.

With this blocking chain info, DBAs may come up with more creative ways to manage blocking and minimize the negative blocking impacts.

I hope some of the items will come true before 2020. God bless SQL Server. 

Posted in Uncategorized | 10 Comments