SQL Server 2012 Removed SOAP/HTTP Endpoint

Since our childhood days (pun intended), we have learnt our ancestors telling us that a Database (or any Data-heavy computing layer) should be hosted independent of others. That is, host Database Servers on one/more servers, and don’t share the same servers for other application/web computing. SQL Server® for some reasons, kept the same standard best practice as a recommendation, but at same time rolled out SOAP/HTTP endpoint in SQL Server 2005. But finally, Microsoft® has finally realized for the issue (or it not being adopted much) and has decided to drop off SOAP/HTTP endpoint in SQL Server 2012 (Denali).

To me, SOAP/HTTP has always been a confusing decision (though now, the response for its rollback is equally confusing). You have a .NET based environment and generally accompanied with .NET / WCF Application servers where you could develop full fledged web services. For pure reporting functions, you had SQL Server Reporting Services. So as a Data Architect, it was always difficult to draw the line on where exactly we go about SOAP/HTTP (and I admit, I recommended my teams to keep presentation or decision logic to be on App/Web layers, and keeping Data masking/handling/filtering/logic etc. at Data layer; recommending a clear demarcation among each layer). Not to mention, clear limitations of SOAP/HTTP were also key contributors to the decision. The only viable use that it offered (in my view) was that you could SP directly wrapped within a webmethod directly – but really how many are such cases where you would want that !?

So, am glad a decision has finally been reached to end this confusion. With SQL Server mainstream (for quite some time now in fact), its pivotal to have clear vision and to have efforts spent on right spots, and getting rid of what’s nothing but dead weight. SQL Server Reporting Services is already a robust piece, but yet a lot lacking if we look at other Reporting Tools available in the market. Those who had the understanding / rule of thumb on keeping application functions segregated, I think they shall continue to remember what they already know, and should try forgetting SOAP/HTTP (from within SQL Server).

SQL Server Connection Protocols

Amidst of all the jazzy terms and technical stuff, there are times when we simply miss out taking a note of building blocks on top of which our IT systems are breathing. One such topic is that of SQL Server Connection Protocols. Am sure most of us remember the four key protocols, but at times must have felt a void around what they really mean or what’s their clear line of segregation. That’s what pushed me to put this note together.

Four Key connection Protocols for SQL Server are/were:

- Shared Memory

- Named Pipes

- TCP/IP

- VIA (to be depreciated in future releases).

Below is a quick write up for more details on each of them.

TCP/IP

By default, the default protocol for SQL Server clients is TCP/IP. If the connection cannot be made using TCP/IP, the other enabled protocols are attempted.
Connection String Format:
Alias Name   <serveralias>
Port              <blank or Pipe name>
Protocol        TCP/IP
Server          <servername or IP or Localhost>\<InstanceName>

Named Pipes

Both on Windows and POSIX systems, named-pipes provide a way for inter-process communication to occur among processes running on the same machine. What named pipes give you is a way to send your data without having the performance penalty of involving the network stack.
By setting up additional named pipes, you can have multiple DB servers running, each with its own request listeners. The advantage of named pipes is that it is usually much faster, and frees up network stack resources.
– BTW, in the Windows world, you can also have named pipes to remote machines — but in that case, the named pipe is transported over TCP/IP, so you will lose performance. Use named pipes for local machine communication.
For named pipes, network communications are typically more interactive. A peer does not send data until another peer asks for it using a read command. A network read typically involves a series of peek named pipes messages before it starts to read the data. These can be very costly in a slow network and cause excessive network traffic, which in turn affects other network clients.
For TCP/IP Sockets, data transmissions are more streamlined and have less overhead. Data transmissions can also take advantage of TCP/IP Sockets performance enhancement mechanisms such as windowing, delayed acknowledgements, and so on. This can be very helpful in a slow network. Depending on the type of applications, such performance differences can be significant. TCP/IP Sockets also support a backlog queue. This can provide a limited smoothing effect compared to named pipes that could lead to pipe-busy errors when you are trying to connect to SQL Server.
It is also important to clarify if you are talking about local pipes or network pipes. If the server application is running locally on the computer that is running an instance of SQL Server, the local Named Pipes protocol is an option. Local named pipes runs in kernel mode and is very fast.
Default Pipe for a named Instance: \\<computer_name>\pipe\MSSQL$<instance_name>\sql\query
Default Pipe for a default Instance: \\.\pipe\sql\query
Connection String Format:
Alias Name  <serveralias>
Pipe Name   <blank or Pipe name>
Protocol    Named Pipes
Server      <servername or IP or Localhost>

Shared Memory

When you connect to the Database Engine from a local client (the client application and SQL Server are on the same computer), the connection uses shared memory protocol by default. Formats: “”, “.”, “\”, “(local)\”. “localhost”, “.\namedInstance”

For connecting locally but using a different protocol, use:
“servername.domain.com”, “tcp:*”, “np:\\<computer_name>\pipe\<pipename>” etc.

Named Pipes is a protocol developed for local area networks. A part of memory is used by one process to pass information to another process, so that the output of one is the input of the other. The second process can be local (on the same computer as the first.

VIA

Virtual Interface Adapter (VIA) protocol works with VIA hardware. The VIA protocol is deprecated. This feature will be removed in a future version.
Connection String Format:
Alias Name    <serveralias>
Port        0:1433
Protocol    VIA
Server      <servername >\<InstanceName>

SQL Server 2012 Licensing

The bull is out now.

If you arent yet aware, read the Official Declaration is here. A lot has already been written and speculated so far. I wouldn’t add to it any further.

Key points (read, my interpretations) from Licensing Guide are below.

a. Computing power based licensing now costed on basis of cores, instead of processors. Its to make costing simplified for phyiscal and cloud based setups. (earlier it literally were like two seperate lines, one for physical setup, and other for virtualized setups). And the bummer now. You will have to buy 4-core license, at the least! It only means that you will continue to cost almost same as if using SQLServer2008 when you are using quad-core processors. If you are using more cores, then you will have to pay more.
b. Workgroup, Small Business, Datacenter editions retired. Glad to hear that to be honest, the edition listing was literally too long earlier. You now have Enterprise (all inclusive), Business Intellignce (Std + Corporate BI features), Standard (basic Database Engine + Standard BI), Developer, Express, Compact, and Web. I personally still feel that Express/Compact could have been merged into one, but still, at least some clarity has been looped in. Yet though, key point am not able to get my around is that why BI edition is available in Server+CAL licensing only. If you want to go for Core licensing for BI (for cost benefits), go for EE.
c. Virtualized environments can be licensed in two modes: Individual VMs, or maximum virtualization (in case of highly virtualized platforms).
d. VM based licensing follows standard stand-alone server licensing as in point#b. For intra-serverfarm/host provider/cloud movement for a VM, you will have to procure "Software Assurance" along with core licenses.
e. For Maximum virtualization, one shall procure SQL Server Core licenses + Software Assurance for whole server farm.
f. Current clients for EE can upgrade to SQL2012 at no additional cost, with following caveat:
    – it shall be done by 30/06/2012
    – if you are running SQL2008R2 with >20 cores, contact your MS representative for transitioning licensing mode
g. For moving current processor license to core licensing
    – SE/EE per processor under Software Assurance (SA) will be exchanged with 4core licenses per processor, and
    – DataCenter Edition per processor under SA will be exchanged with 8core EE licenses per processor.

Microsoft Cloud Offering–Windows Azure–Pros and Cons

I guess am a little late to join the party. But nonetheless, I wanted to start with a quick review of what all it has to offer, in what specs, and at what price. Though in this write up I want to concentrate upon the pros and cons of Windows Azure.

What’s the push from MS? Windows Azure does score well on three aspects for sure:

1. Partially, no Server Admin on your premise. And it covers, server hotfix/patching (not in all cases though), license audit taken care of by default and less worries for High Availability of your solution (key here is, how much MS can stick to its offered SLA).

2. Flexible Billing cycles (Pay-as-you-go). But one side note here. We all know how such billing cycles leave behind some clients feeling over-charged. It would be interesting to see how MS works on that because service billing (specifically pay-as-you-go) is quite different from one-time license provisioning. Biggest advantage is for SMBs who can beef up resources as they grow and dont have to fall in OpEx traps (at least from Infra-Licensing point of view).

3. Giving power back to the users to concentrate on Application and take away all their worries from hosting and provisioning point of view. But am sure, excluding SMBs, large scale applications would still be looking upon the ROI aspects, own cloud/datacentre vis-a-vis MS cloud (or any other cloud for that matter).

But nothing comes easy or free in this world ! While you can dream around some advantages as above, be ready to:

1. loose account control to MS

2. Increased competition (yes, your competitors can reach as easy too as you did, plus experienced & specialized development resources requirements ! )

3. MS have calibrations to compute how much you used them, how you plan to validate it !

4. Increased integration avenues / costs

SQL Server Version Listing

Last week we got into a strange issue regarding some SQL Server 2008 post-installation error messages. Technet mentioned that such occurrences are reported when we are using a version prior to Cumulative Update (CU6).  But it wasnt getting clear if a particular build came prior or before CU6 (yes, sounds insane, but we landed there!).

Anyways, I came across a web-resource from SQLSecurity.com available here, which is quite helpful as it lists down complete history of SQL Server builds ever published (public/private, CU, SP, Patches – everything). Now, there is another web-resource from SQLTeam available here, but it enlists only major Releases, CU, SP etc. I used to reference SQLTeam link till sometime back, but given the level of depth on SQLSecurity site, I think that too is quite a useful reference point going forward.

Identifying SQL Server Version

DECLARE @SQLRelease VARCHAR(15);

SET @SQLRelease = 'UNKNOWN'

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='8'

SET @SQLRelease = 'SQLServer2000'

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),1)='9'

SET @SQLRelease = 'SQLServer2005'

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') As Varchar),2)='10'

SET @SQLRelease = 'SQLServer2008'

SELECT @SQLRelease as ReleaseLevel

, SERVERPROPERTY('ProductLevel') AS ProductLevel

, SERVERPROPERTY('Edition') AS Edition;

We are working upon a central Server Inventory solution and needed to capture for SQLServer details. Surprisingly, there are many script-lets out there to capture Product Version, but not much details around what it maps to (unless you want to keep a table running with you to interpret numbers again SQL Server Product version). Hence the quick script above which can tell you Product Version in more humanly manner, along with SP and Edition details.

Script to List SQL Server Database Backup History

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
   bs.database_name, 
   datediff(MINUTE, bs.backup_start_date,   bs.backup_finish_date) AS time_taken,
   CASE bs.type     WHEN ‘D’ THEN ‘Database’         WHEN ‘L’ THEN ‘Log’     END AS backup_type, 
   bs.backup_size, 
   bmf.physical_device_name,  
   bs.name AS backupset_name
FROM   msdb.dbo.backupmediafamily  as bmf
   INNER JOIN msdb.dbo.backupset as bs ON bmf.media_set_id = bs.media_set_id 
WHERE  (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() – 7) 
AND bs.type = ‘D’
/* comment out statement above to report all backups */
ORDER BY 
   bs.database_name,
   bs.backup_finish_date

SQL Server DBCC Deep Dive

DBCC CHECKDB
- Only way to read and test all pages in DB
- By default its CPU bound (full check), but can be made IO bound using WITH PHYSICAL_ONLY
- A resource hog, and uses tempdb as well
- wont take locks unless specified WITH TABLOCK
- New Features starting 2005:
    (a) progress reporting using sys.dm_exec_reports
    (b) Data purity reports e.g. datetime correctness
    (c) last DBCC completed successfully
- New Features starting 2008:
    (a) WITH EXTENDED_LOGICAL_CHECKS

How to Run CHECKDB?
- Returns first 200 errors by default, use ALL_ERRORMSGS
- No harm in letting it complete even if its taking time; if running for too long, check for error 5628 to check if its rescheduling certain check

What CHECKDB Do
(1) primitive check for critical system tables (any problem here – game over)
(2) allocation checks (IAM pages etc)
(3) Logical checks of critical system tables (key order, index mapping, LOB pointer, file stream)
(4) logical check for all tables
(5) Service Broker data validation
(6) Metadata table checks
(7) Indexed view, XML index, spatial index checks (off by default)
NOTE that repairs are done at each stage. CheckDB creates a snapshot and runs the check on that for consistency. You can as well create a snapshot of your own and run DBCC on that (in case default site dont have space)

How CHECKDB checks as above:
- Primitive system table checks
- DBCC CHECKALLOC
- DBCC CHECKTABLE (system tables)
- DBCC CHECKTABLE
- DBCC CHECKCATALOG.
You might want to optionally run DBCC CHECKIDENT and CHECKCONSTRAINTS.
CHECKFILEGROUP: checks file and hosted tables and index. can be opted for after file based operations. 
CHECKTABLE: Dont use WITHOUT INDEX flag.
NOTE: – Size TempDB, use WITH_ESTIMATEONLY flag if needed. – If checkdb running for too long it might mean it has found something to work upon. Run CHECKDN as frequently as can.

Interpreting and Fixing Issue Using CheckDB
- If there are only a couple errors, check MSDN – If there are many errors, use some tips and tricks, as below:
(1) If checkdb doesn’t complete it tells why it couldn’t (also means you cant perform recovery, so restore). e.g. of fatal errors, 7984-7988 critical system table corrupt; 8967 invalid states in checkdb; 8930 corrupt metadata
(2) Are the error messages for NonClustered Index (NCI)? Run index rebuild. When rebuilding indexes, it references older records, so issue might persist. So drop and recreated.  Always rerun DBCC
(3) Unrepairable erros:
    (a) 8909, 8938, 8939 i.e. page header corruption – restore page from backup 
    (b) 8970 invalid data for column – work with BAs
    (c) 8992 checkcatalog error; find object ID, find catalog table having errorenous objectID, shutdown db, startup with -m: param,  delete the entry from errooneous table, rerun DBCC, if fixed startup without -m: flag
    (d) 2570 data purity error – find the page from DBCC CHECKDB output, run DBCC PAGE, try delete or update the records as appropriate

REPAIR_ALLOW_DATA_LOSS
- Beware of using it
- repairs physical strucuture w/o any consideration for data / biz logic; fastest mode of repair.
- Drop create for what cant be corrected
- Doesnt take into account: replication, mirroring, biz logic, data relationship, foreign key constraints
- Take backup before doing this
- After using it, run CHECKCONSTRAINTS and fix up replication topologies

 

Reference: SQL Server MCM Deep Dive Sessions

SQL Server – Why Checksum was Introduced?

Torn Page Detection
- an 8K page = 16*512byte disk sectors
- its possible that a page is partially written when power goes off, i.e. torn page
- You cant detect corruptions within each disk sector, hence page checksum is preferred / used

Page Checksum
- checksum is written last within the same 4byte page header as used by torn page
- checksum is read first when reading a page. Every time a page is read, buffer pool calculates the checksum and compares to value stored in header
- Checksum is enabled by default in new installations, but for upgrade from 2000 it needs enabled manually
- Remember, its error detection, not error correction. – CPU overhead = 1 or 2% 
- Checksum errors lead to error 824. – tempdb page checksum is supported starting SQL2008
- Checksum is referenced for read, checkdb, backup with checksum, or if page is in checksum’-med backup
Note that page checksum starts playing only after its been re-written once with page checksum value (if page already existed).

SQL Server Restore vs Repair

Restore vs Repair
- decision depends upon SLA and your situation
    (a) DB Available: no, then restore from backup
    (b) Working backup available? No, repair or restore from older backup with CONTINUE_AFTER ERROR. Is log damaged? yes, restore or run emergency repair or extract to a new DB
    (c) CheckDB Failed? yes, restore
    (d) Just NCI damaged? rebuild indexes. Un-repairable errors? Restore

Backup Recovery
- Best way to avoid data loss, but not best way to avoid downtime
- Backups have to be available when needed, and have to be valid

Repair
- It doesn’t fix data, it fixes structure and tries to be as fast as it can be.
- It tries to run most intrusive errors first.
- It counts # of errors found and fixed. Though some errors might be masked, and hence checkDB is run again.
- Repair is done offline because its hard to get them working in online mode