Most Developers Have No Business In The Database World

I have been around superb developers that understand the concept of approaching efficient code that needs to be ran on the database, and will seek help from database developers/DBAs to ensure that is the case. Unfortunately, most do not really understand the need and approach a solution to certain requirement with one thing in mind: effectiveness.

Yes, If business folks request a page that will show up all kinds of fancy data and drop downs, they will do it. It does not matter if the code behind the scenes goes through an expensive loop to render each record one-by-one.

The database is the most difficult and expensive Tier to scale, and code needs to be written in a way so it does the less amount of reads to get the answer to certain request. Developers need to understand that effectiveness needs to be tied with efficiency whenever they are coding.

If they do not understand/do not know how to make efficient code then seek help from a Database Developer/DBA. For example, why code a process that updates one row at a time instead of performing a bulk operation that updates all at once? Didn’t they know that a nested loop can be the worst route instead of hash or merge join to perform an update? Reducing trips to the I/O subsystem really makes a difference.

Now, there are other type of developers that think they know the answer for improving performance to any database without even looking at their code. I have experienced situations where I was told “we need more indexes” or “the database is timing out” when the issue was another heavy process taking long I/O requests and the developer had an uncommitted transaction respectively.

If developers have a problem, work as a team. Ask for help to someone that really knows their business so they can perform a health check. Imagine if a DBA goes and tells a developer how to write their application tier code.

I have not seen any DBA that has morphed onto being a developer. Most cases are the opposite, where developers become DBAs. I have experienced it myself as a former developer (yeah the procedural days) and DBA. If DBAs become developers this will be a perfect world, as the efficiency techniques will be considered while coding application and web tier code.

My final thoughts are, DBA’s own the database; it does not matter what the developer says. If a developer has an idea, then express it as it; do not demand what needs to be done to a database as they are most likely wrong.

And no, creating a single index on each field will not make the database faster. Let the DBA collect statistical facts and go from there.

Kerberos Double Hop and Delegation between different Active Directory Accounts [SQL Server]

In the past, when I had bare knowledge of Active Directory, I dealt with double hop queries using SQL Server Authentication.

What is the problem with SQL Authentication? Maintenance. The DBA team can literally spend hours maintaining usernames/passwords, linked servers, and of course giving appropriate grants/roles to a new user added to several database instances. There are also security concerns which is outside the scope of this post.

So what is a double hop query? It is a query ran from Machine A that requests data from machine B that at the same time requests data from Machine C.

For example:

If I execute a query on my local machine (A) connecting to Server1 (B) that requests data from Server2 (C):

1
2
3
4
5
-- From my desktop:
SELECT a.*
FROM OPENROWSET('SQLNCLI',
'Server=server2.domain.com\instance;Trusted_Connection=yes;',
'SELECT * from master.sys.objects') AS a;

The solution: Active Directory. A user can be added to the DBA Domain Group, and that group can be granted the sysadmin role on every production database server for example. Once that is defined, we can add/remove members of the group without even touching the database instances.

Now, there is a consensus of having each database instance use its own Active Directory Account as SQL Server Service user. Some shops have 1 account, others a few. It depends on the policy adopted.

Our challenge: Allow double hop queries between 2 database instances running different Active Directory Accounts.

The solution was similar to what we have done with 1 single account.

This is how me made it work for 1 account:

Prerequisites:

  1. You need domain admin or access to Active Directory under the domain managed
  2. The person in charge of making the change has access to the domain controller or has Remote Server Administration Tools installed locally.

Steps:

  • Via Command Prompt, execute the SETSPN command for the server, port and domain account assigned for the SQL Server Instance.
1
setspn -a MSSQLSvc/server1.domain.com:1433 DOMAIN\srvc_sqlaccount
  • For clustered solutions, run it with and without port number. It does not matter if it is a named instance.
1
2
setspn -a MSSQLSvc/serverclust1.domain.com DOMAIN\srvc_sqlaccount
setspn -a MSSQLSvc/serverclust1.domain.com:1433 DOMAIN\srvc_sqlaccount
  • This needs to be repeated for all the SQL Server Instances that you need to trust delegation
  • Open Active Directory Users and Computers, search for the domain account just used (DOMAIN\srvc_sqlaccount) in the example, double click and open the properties window.
  • Enable “Trust this user for delegation to specified services only”, “Use Kerberos Only”, Add, User and Computers, type in the account used (e.g. srvc_sqlaccount), select the machine(s) you want to trust delegation, OK, OK.

This is how we made it work with 2 accounts:

You need to follow the same exact process, but when enabling Kerberos delegation, you need to open the properties of the first domain account and add Kerberos Delegation to the second domain account.

Assuming non-clustered instances:

Steps:

  • For the first account and the first instance. Via Command Prompt, execute the SETSPN command for the server, port and domain account assigned for the SQL Server Instance.
1
setspn -a MSSQLSvc/server1.domain.com:1433 DOMAIN\srvc_sqlaccount_1
  • For the second account and the second instance. Via Command Prompt, execute the SETSPN command for the server, port and domain account assigned for the SQL Server Instance.
1
setspn -a MSSQLSvc/server2.domain.com:1433 DOMAIN\srvc_sqlaccount_2
  • Open Active Directory Users and Computers, search for the first domain account (DOMAIN\srvc_sqlaccount_1) in the example, double click and open the properties window.
  • Enable “Trust this user for delegation to specified services only”, “Use Kerberos Only”, Add, User and Computers, type in the first account used (e.g. srvc_sqlaccount_1)
  • Select the machine you want to trust delegation (server1.domain.com in the example), OK, OK.
  • Click Add again, User and Computers, type in the second account used (e.g. srvc_sqlaccount_2)
  • Select the machine you want to trust delegation (server2.domain.com in the example), OK, OK.
  • Wait enough time for the Domain Controllers to propagate the changes (15 minutes should be fine)

Verify Logins are Connecting Via Kerberos:

Run the following query, paying special attention to the auth_scheme column. If Kerberos authentication was successful, you should see the auth_scheme reflect Kerberos instead of NTLM:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
s.session_id
, c.connect_time
, s.login_time
, s.login_name
, c.protocol_type
, c.auth_scheme
, s.HOST_NAME
, s.program_name
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id

Now we can have multiple Domain Accounts for multiple SQL Database Instances, each one delegating for double hop.

Hope this helps.

SQL Server: Truncating Dates

TRUNC is a function I used a lot when I worked with Oracle and helped me slicing my data in several different ways, and keeping the columns in date format.

That function is not available in SQL Server unless you work it around by using CONVERT or DATEPART. But both change the content to VARCHAR.

Well, several years ago I found a way to workaround the issue and keep the data in DATE format (and it took me this long to post it):

1
2
-- Truncate to the current hour
SELECT DATEADD(HH, DATEDIFF(HH,0,GETDATE()), 0)
1
2
-- Truncate to the current day
SELECT DATEADD(DD, DATEDIFF(DD,0,GETDATE()), 0)
1
2
-- Truncate to the current month
SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()), 0)

As per BOL, we can use any of these datepart arguments:

  1. year (or yy, yyyy)
  2. quarter (or qq, q)
  3. month (or mm, m)
  4. dayofyear (or dy, y)
  5. day (or dd, d)
  6. week (or wk, ww)
  7. weekday (or dw, w)
  8. hour (or hh)
  9. minute (or mi, n)
  10. second (or ss, s)
  11. millisecond (or ms)
  12. microsecond (or mcs)
  13. nanosecond (or ns)

You can come up with all kinds of GROUP BYs, and filtering and keep the data in Date format at the same time.

A couple of days ago, while browsing the AskSSC community forum, a question came up: “How to get quarter start date and end date”. Because I wanted to exercise my brain a little, decided to help out and posted:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE @min_date DATETIME, @max_date DATETIME;
SET @min_date = '1/1/2009';
SET @max_date = '12/31/2010';

-- Working Vars
SET @max_date = DATEADD(QUARTER, DATEDIFF(QUARTER,0,@max_date), 0) + 1;

WITH CTE_dummy AS (
    SELECT TOP(DATEDIFF(QUARTER, @min_date, @max_date) + 1) ROW_NUMBER() OVER(ORDER BY object_id) - 1 AS sequence from sys.columns
)
SELECT CASE WHEN (DATEPART(QUARTER, @min_date) + sequence) % 4 = 0 THEN 4 ELSE (DATEPART(QUARTER, @min_date) + sequence) % 4 END AS [Quarter],
    DATEADD(QUARTER, sequence, DATEADD(QUARTER, DATEDIFF(QUARTER,0, @min_date), 0)) AS [Start_Date],
    DATEADD(QUARTER, sequence, DATEADD(QUARTER, 1, DATEADD(QUARTER, DATEDIFF(QUARTER,0, @min_date), 0)) - 1) AS [End_Date]
FROM CTE_dummy;

For the solution I used the ROW_NUMBER() windowing function, but deals with dates truncation to tackle the core question.

Hope it helps.

Figuring out Android internals

My first task was to change the ringtone to music I used to have in my Windows phone. A simple search let me to a guide with this information:

Music/ – Media scanner classifies all media found here as user music.
Podcasts/ – Media scanner classifies all media found here as a podcast.
Ringtones/ – Media scanner classifies all media found here as a ringtone.
Alarms/ – Media scanner classifies all media found here as an alarm sound.
Notifications/ – Media scanner classifies all media found here as a notification sound.
Pictures/ – All photos (excluding those taken with the camera).
Movies/ – All movies (excluding those taken with the camcorder).
Download/ – Miscellaneous downloads.

These are under the media folder located in the microSD card. As soon as the Android phone is plugged to a PC, a USB Mass Storage device is detected and drivers are installed.

My second task was to have the ability to browse the storage card within Android (kind of File Explorer on Windows Phone). I found Astro file manager and installed it.

My third task was to find applications I used often in other environments and install them into the device. TweetDeck, Foursquare, Skyfire, Retro Radio and Google Reader came to mind. Facebbok and Twitter apps came pre-installed.

Fourth task. Figure out why is my device losing signal in my house. This is ironic because I used to laugh at the iPhone 4 when all the articles of bad reception popped up a week after it was launched. Several sites started posting funny pics about it. Well it is just bad luck as my Windows Phone has better reception. I figured out spots where I can leave it and let it get some signal.

The situation prompt me to ask a question on Twitter. 2 mentions came back quickly pointing out about WiFi calling. This is why Twitter is so awesome!

So my fifth task was to research about Wifi Calling and try to make it work. Well this wasn’t as trivial, as I needed to follow these steps:

  1. Download the Android SDK and install
  2. Install USB drivers – but seems that this method did not work properly in my machine
  3. Download proper X64 USB drivers
  4. Download the T-Mobile Wifi-Calling App
  5. Plug in the G2 to my PC
  6. Enable usb debugging option in the device
  7. Open the Device Manager, find out the new device that has no drivers and update it with the ones you just downloaded
  8. Open the command prompt window and change the folder to “tools” under the android SDK folder
  9. Issue adb commands:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Oscar>cd C:\Users\Oscar\Downloads\android sdk\tools

C:\Users\Oscar\Downloads\android sdk\tools>adb devices
* daemon not running. starting it now on port 5037 *
* daemon started successfully *
List of devices attached
HT09XR207992    device

C:\Users\Oscar\Downloads\android sdk\tools>adb devices
List of devices attached
HT09XR207992    device

C:\Users\Oscar\Downloads\android sdk\tools>adb install WiFiCalling.apk
2565 KB/s (3478662 bytes in 1.324s)
        pkg: /data/local/tmp/WiFiCalling.apk
Success

C:\Users\Oscar\Downloads\android sdk\tools>

I installed the Wi-Fi application but seems that T-Mobile has not enabled it on their end. I will wait and post as soon as I get any success using it.

Enjoy your [soon to be] Android phone.

From Palm to Windows Phone to Android

I have been a hardcore Windows Phone user (formerly Windows Mobile, and CE) for several years, mainly because it was very easy to  synchronize directly with Outlook and Microsoft Exchange. The GUI has been pretty nice since the first releases and resembled Windows in a way.

Windows Phone let me transition from a Windows CE PDA (Toshiba E335) which was a great jump from the Palm m125; years 2006, 2003 and 2001 respectively. By the end of 2005 I wanted a true PDA/Phone that wasn’t as bulky as the HP iPaq. T-Mobile launched the SDA (HTC Torando) which to me was a godsend. It had Wi-Fi (WPA-PSK support) and bluetooth,  running Windows Mobile 5.0. It could play MP3s, Video and synchronize with Outlook/Exchange. The form factor was similar to a dumbphone. It was one of the best phones I have ever owned and prompted me to create a web page with streaming broadcasters posted here.

In early 2007 I bought a T-Mobile Dash (HTC Excalibur) and was able to live with it for 2 years running several cooked Roms (thanks to XDA forums). I had the opportunity to run Windows Mobile versions 5.0, 6.0, 6.1 and 6.5. The latest was the most stable one and the fastest considering the outdated hardware. Many people thought it was a Blackberry.

In 2009 I bought the HTC Touch Pro 2 (HTC Rhodium) Runing Windows Mobile 6.0. I wanted to keep it stock as much as I could for warranty purposes but  had problems with the clock and was never able to sync up properly. I decided to go ahead and install a cooked ROM. Since then It as been running different releases of the Energy Rom. I was happy about the capabilities of the phone but had too many OS reliability issues. A reboot every day was not out of the norm, and in some cases several of them on the same day.

Last week I decided to pursue the Android route; T-Mobile just delivered me a brand new HTC G2. The phone is by far the fastest and the most intuitive phone I have ever owned. It is running Android 2.2 and integrates seamlessly with Exchange, Gmail, Facebook and Twitter. There are tons of applications for Android and includes Text-to-Speech directions integrated within Google Maps. Android is light years ahead of Windows Phone 6.5.

I still will try a Windows Phone 7 device and have very high expectations about it.

Next post will be about the Android SDK and how to root the phone to push an application.