Hoeist!? Blog of Colin Raaijmakers.

Hoeist!? Blog of Colin Raaijmakers.

Top 10 features of Microsoft SQL 2008 R2

Development

 

Microsoft SQL Server 2008 R2 is the latest release of SQL Server. This article will introduce the top 10 features and benefits of SQL Server 2008 R2.

Report Builder 3.0

Report Builder 3.0 is a report authoring environment for business users who prefer to work in the Microsoft Office environment.

SQL Server 2008 R2 Datacenter

Built on SQL Server Enterprise, SQL Server 2008 R2 Datacenter is designed to deliver a high-performing data platform that provides the highest levels of scalability for large application workloads, virtualization and consolidation, and management for an organization’s database infrastructure. Datacenter helps enable organizations to cost effectively scale their mission-critical environment.

SQL Server 2008 R2 Parallel Data Warehouse

Microsoft SQL Server 2008 R2 Parallel Data Warehouse (previously code named project "Madison") is a highly scalable appliance that delivers performance at low cost through a massively parallel processing (MPP).

StreamInsight

Data volumes are exploding with event data streaming from sources such as RFID, sensors and web logs across industries including manufacturing, financial services and utilities.  The size and frequency of the data make it challenging to store for data mining and analysis.  The ability to monitor, analyze and act on the data in motion provides significant opportunity to make more informed business decisions in near real-time.

Master Data Services

Master Data Services helps enterprises standardize the data people rely on to make critical business decisions. With Master Data Services, IT organizations can centrally manage critical data assets companywide and across diverse systems, enable more people to securely manage master data directly, and ensure the integrity of information over time.

PowerPivot for SharePoint

Microsoft SQL Server PowerPivot for SharePoint extends SharePoint 2010 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks that you publish to SharePoint.

Data-Tier Application

A new single unit of deployment, Data-tier Application, packages database schema (database, tables, stored procedures, etc) with deployment requirements and can be extracted from existing applications or created in Microsoft Visual Studio®—accelerating deployments and upgrades.

Unicode Compression

Unicode compression in SQL Server 2008 R2 uses an implementation of the Standard Compression Scheme for Unicode (SCSU) algorithm to compress Unicode values that are stored in row or page compressed objects. For these compressed objects, Unicode compression is automatic for nchar(n) and nvarchar(n) columns. The SQL Server Database Engine stores Unicode data as 2 bytes, regardless of locale. This is known as UCS-2 encoding. For some locales, the implementation of SCSU compression in SQL Server 2008 R2 can save up to 50 percent in storage space.

SQL Server Utility

SQL Server customers have a requirement to manage their SQL Server environment as a whole, addressed in this release through the concept of application and multiserver management in the SQL Server Utility.

Multi Server Dashboards

Investments in application and multi-server management will help organizations proactively manage database environments efficiently at scale through centralized visibility into resource utilization and streamlined consolidation and upgrade initiatives across the application lifecycle—all with tools that make it fast and easy.

Read more on: http://www.databasejournal.com/features/mssql/article.php/3857466/Top-10-Features-of-SQL-2008-R2.htm
Read more on: http://cio.nl/internationaal-nieuws/20712/review-microsoft-sql-server-2008-r2.html

Read more about, Top 10 features of Microsoft SQL 2008 R2

Tags: microsoft, sql, 2008, r2, windows, features

Published: Monday, November 15, 2010

Drop All Indexes and Stats in one Script

Development

I am not sure why someone would want to do this, but it was asked on the forums, and I figured I would post the code I created to perform such a nightmarish operation.  As with any post that I make providing code that could be potentially damaging and dangerous, if you use it, you do so at your own risk.  Don't send me emails complaining that you got fired for deleting all the indexes with the scripts on this post.  I am not going to be able to help you fix it, and my recommendation is going to be restore a backup, and start scripting them all off if you still have a job.

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR

SELECT indexes.name, objects.name, schemas.name
FROM sys.indexes
JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE indexes.index_id > 0
  AND indexes.index_id < 255
  AND objects.is_ms_shipped = 0
  AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
ORDER BY objects.OBJECT_ID, indexes.index_id DESC

SELECT * FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql  
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes

GO

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropstats CURSOR FOR

SELECT stats.name, objects.name, schemas.name
FROM sys.stats
JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE stats.stats_id > 0
  AND stats.stats_id < 255
  AND objects.is_ms_shipped = 0
ORDER BY objects.OBJECT_ID, stats.stats_id DESC

OPEN dropstats
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql  
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
END
CLOSE dropstats
DEALLOCATE dropstats

Read more about, Drop All Indexes and Stats in one Script

Tags: sql, function, index, stats, drop, delete

Published: Wednesday, September 16, 2009

Calculate distance within SQL

Development

I have a Users table in one of my SQL server databases. Each user record has a lat and long pair on it that indicates the location of that user.

My goal is to be able to call a sql stored proc and provide it a lat/long of the location, and have the stored proc query the users table and perform the calc and only return the users who are within a certain distance.

Create a new Function within SQL server:

-- just set @InKilometers to 0 for miles or 1 for km
-- ex:  SELECT dbo.CalcDistanceBetweenLocations (30.123,27.1,28.14,32.23, 0)

CREATE FUNCTION dbo.CalcDistanceBetweenLocations
      (@LatitudeA       FLOAT = NULL,
       @LongitudeA       FLOAT = NULL,
       @LatitudeB       FLOAT = NULL,
       @LongitudeB       FLOAT = NULL,
       @InKilometers      BIT = 0
       )
RETURNS FLOAT
AS
BEGIN
      
      DECLARE @Distance FLOAT

      SET @Distance = (SIN(RADIANS(@LatitudeA)) *
              SIN(RADIANS(@LatitudeB)) +
              COS(RADIANS(@LatitudeA)) *
              COS(RADIANS(@LatitudeB)) *
              COS(RADIANS(@LongitudeA - @LongitudeB)))

      --Get distance in miles
        SET @Distance = (DEGREES(ACOS(@Distance))) * 69.09

      --If specified, convert to kilometers
      IF @InKilometers = 1
            SET @Distance = @Distance * 1.609344

      RETURN @Distance

END

Read more about, Calculate distance within SQL

Tags: sql, function, calculate, distance

Published: Friday, July 03, 2009

How to prevent SQL Injection in ASP

Development

What is SQL Injection?

SQL injection is a type of security exploit in which the attacker adds Structured Query Language (SQL) code to a Web form input box to gain access to resources or make changes to data. An SQL query is a request for some action to be performed on a database. Typically, on a Web form for user authentication, when a user enters their name and password into the text boxes provided for them, those values are inserted into a SELECT query. If the values entered are found as expected, the user is allowed access; if they aren’t found, access is denied. However, most Web forms have no mechanisms in place to block input other than names and passwords. Unless such precautions are taken, an attacker can use the input boxes to send their own request to the database, which could allow them to download the entire database or interact with it in other illicit ways.

The risk of SQL injection exploits is on the rise because of automated tools. In the past, the danger was somewhat limited because an exploit had to be carried out manually: an attacker had to actually type their SQL statement into a text box. However, automated SQL injection programs are now available, and as a result, both the likelihood and the potential damage of an exploit has increased enormously. In an interview with Security Wire Perspectives, Caleb Sima, CTO of SPI Dynamics spoke of the potential danger: “This technology being publicly released by some black hat will give script-kiddies the ability to pick up a freeware tool, point it at a Web site and automatically download a database without any knowledge whatsoever. I think that makes things a lot more critical and severe. The automation of SQL injection gives rise to the possibility of a SQL injection worm, which is very possible. In fact, I am surprised this hasn’t occurred yet.” Sima estimates that about 60% of Web applications that use dynamic content are vulnerable to SQL injection.

According to security experts, the reason that SQL injection and many other exploits, such as cross-site scripting, are possible is that security is not sufficiently emphasized in development. To protect the integrity of Web sites and applications, experts recommend simple precautions during development such as controlling the types and numbers of characters accepted by input boxes.

How to prevent from SQL injection?

In ASP, you can create a function to check all string or parameters passed through URL as get method, there are various way to prevent SQL injection which you can use in your website for different methods to prevent it that is suitable to you, here is one simple example to do it.

<%
Private blackList As String() = {"--", ";--", ";", "/*", "*/", "@@", _
  "@", "char", "nchar", "varchar", "nvarchar", "alter", _
  "begin", "cast", "create", "cursor", "declare", "delete", _
  "drop", "end", "exec", "execute", "fetch", "insert", _
  "kill", "open", "select", "sys", "sysobjects", "syscolumns", _
  "table", "update"}

Public Function CheckInject(ByVal BefString As String) As String
  Dim CheckString As String = Replace(BefString, "'", "''")
  For i As Integer = 0 To blackList.Length - 1
    If (BefString.IndexOf(blackList(i), StringComparison.OrdinalIgnoreCase) >= 0) Then
      CheckString = Replace(CheckString, blackList(i), “”)
    End If
  Next
  Return CheckString
End Function
%>

Read more about, How to prevent SQL Injection in ASP

Tags: sql, injection, asp, detection, prevention, exploit

Published: Thursday, May 28, 2009