Managed Code Inside of SQL Server

What roles do you perform as the DBA for your organization?  Many times we find ourselves doing data related tasks that are thrust upon us because of software acquisitions, executive level business requirements, or a myriad of other reason.  Whichever the case, we end up working on some common repetitive tasks and some very specific unique ones.  One of those specific tasks that you may find yourself needing to implement is using managed code in your SQL environment. 
 

This article is not for everyone, that is to say managed code integration is not a common task like backup or index management, rather it is for administrators who find themselves in one of the following two situations; a) you have applications in your environment that were developed and supported in house, and/or b) you work closely with those who develop code to enhance or modify 3rd party applications.
 

In a typical n-tier application, we have front end code, business logic, and the data repository.  In fact we could have a data repository that supports many different front/middle tiers.  In such a case, we could develop stored procedures, triggers, or functions, on the data layer for shared use at the application layer.  We have heard the benefits of centralized administration of networks from Microsoft for years, we can think of the aforementioned approach as centralized development.
 

One problem that could arise from this approach is that an application may (and likely will) already have code written in it for its own use.  Therefore developing it again at the data layer, is time consuming, costly, and may have scaled down functionality due to the limitations of T-SQL.  SQL Server 2008 has the ability to create, what I will term as function “proxies” that are created inside of the SQL Server environment, but are defined in managed code.  Now, if your development team has developed some function that their application needs and placed it into a DLL, you as a SQL professional can centralize that function by simply calling a SQL Server function that calls out to that DLL for its implementation. 
 

Whether the reason for using this feature, is applying centralized coding, or combating the limitation of the data developer not knowing the same business logic specifics that the application developer knows, or that managed code is more capable of handling the business logic tasks than T-SQL, or leveraging the skills of each area of your business to minimize the development cycle – accessing managed code is a great option to explore.
 

Assume your business line is the building of bridges and you frequently need to calculate the density of steel at various elevations.  You may have this function defined in one of your applications on the middle tier, and you may find that you now need to access that function from different applications and possibly inside SQL Server.  Without managed code integration, you would have to recreate the body of the function inside of SQL server or inside of the other application.  You now have the ability to create the function inside of the SQL environment, point the body of the SQL Server function to the DLL function, and simply consume the SQL function, as you normally would.  Of course, there is a little bit of “wire-up” code that you need to provide, as well as some re-architecting from the application standpoint.  You may find the benefit of the code existing in one place makes implementing managed code worth it.  However, a cost analysis to determine if this change would be of benefit would have to be conducted.  However, designing code in the first place to be able to benefit from this capability is a great way to move forward without the cost of retooling any current code.
 

The specifics of how you chose to implement this feature are as varied as the infrastructure of your company, but the benefits of now being able to reuse .NET developed code inside of SQL server is a great way to reduce total cost of ownership in a measurable way.

- Ed Dressler, SQL Server Expert; CTT+, MCDBA, and MCITP LinkIn with Ed

How To: Set Up Windows Sever 2008 & R2 Accidental Delete Protection

Windows Server 2008 and Windows Server 2008 R2 have the ability to protect objects from accidentally being deleted. This feature is extremely helpful in environments where there are numerous Administrators making many changes. This feature may also be useful in a smaller environment with less experienced Administrators.

To use this feature, simply launch Active Directory Users and Computers by typing dsa.msc and press Enter, in the Search Programs and files box, as demonstrated below:

To create a new Organizational Unit and protect it from being deleted, simply right-click on the location for the new OU and name it and remember to select the Protect container from accidental deletion as displayed below:

If you have users or groups that you would like to protect against accidental deletion, you can add this protection.   First you will need to turn on the Advanced Features of Active Directory Users and Computers.  To accomplish this select the View menu and then select Advanced Features as illustrated below: 

 

Then open the properties of the user or group that you would like to protect.   Click on the Object tab and select the Protect object from accidental deletion box.  The sample below is of a user object.

Essentially, this feature denies the Everyone group the Delete all child objects permission in the DACL (Discretionary Access Control List).  This disallows Everyone, including members of the Administrators group, from deleting any object that is set to be protected from accidental deletion.  The permission can be reversed by a user that possesses the Full Control permission to the object.  The user would simply remove the check in the box to the left of Protect object from accidental deletion or would  remove the Access Control Entry from the DACL.    An example of the Access Control Entry is below:

It is a great feature that the system has that will prevent some human errors from occurring.  Even though it is possible to delegate the permissions to control who can delete objects, it is still possible for accidental deletions to occur.  This feature forces an administrator to re-evaluate whether an object should be deleted.  If it should be deleted then the administrator can set the permissions appropriately and delete the object. 


- Janet Nichols, Windows Server Expert; MCT, MCSE, MCITP: Enterprise Admin, Server Admin; Windows 7 EDA, MCTS LinkIn with Janet

MS Word Tip: Easily align text using tabs

From here........................................ to here 

Above is an example of a right tab with a dot leader set at the 6” mark.

1. If necessary, show the ruler below by clicking the check box on the View tab of the ribbon in the Show/Hide group. Also verify that your document is in Print Layout view.

2. It is helpful to turn on the Show/Hide button in the Paragraph group on the ribbon. This will allow you to see the non-printing arrows that appear when you touch the tab key on your keyboard.

3. Use the button on the left end of the ruler below the ribbon to select the basic tab type (left, center, right, decimal, etc.).  Click the button several times to cycle through the different tab types. This shows a right tab type selected.

4. With your mouse, left click on the ruler where you want the tab to be. This shows a right tab set on the ruler at the 6” mark.

5. To see all tab options (position,  type, dot leaders, etc.) you must open the Tab options dialog box. The easiest way to do this is to point your mouse at the tab you have set on the ruler. When the tool tip appears, double click the selected tab.

The Tabs dialog box will appear displaying all tab options.

This shows a tab set at the ½” mark and at the 6” mark. The 6” tab is selected and is a right tab with a stye 2 dot Leader.

You can make changes to tabs you have already set, set additional tabs, or clear extra tabs here.  Select the tab from the list and change the Alignment, set the Leader, change the position, or clear the tab.

Click the OK button when you are done!

_________________________________________________________

Alternate instructions: The tabs dialog box can also be accessed by clicking the dialog box launcher at the bottom right corner of the Paragraph group on the ribbon. When the Paragraph  dialog box appears, click the Tabs button on the bottom left corner.

- Irene Heckert, Microsoft Office Expert; CTT, MCAS, ITIL Certified, MOS LinkIn with Irene