Category Archives: SQL

All about Structured Query Language

SQL 2012 and PowerShell 3 talk at Florida.Net UG

After some tuning I re-presented my talk on SQL 2012 and PowerShell 3 for the Florida.Net user group.

They let me ramble on much longer, so I had the chance to do more demos and step through the code more thoroughly.

Recieved some really great feedback, and saw more than a few people may have caught the PowerShell bug 🙂

The updated slides are on SlideShare.net.

If I get five or more requests via email or my contact form, I’ll post the code as well. Just want to check whether this internet thing is working….

SQL 2012 and Powershell 3 SFSSUG Talk

Spoke last night on SQL 2012 and Powershell 3 at South Florida SQL Server User Group today. Thanks guys for having me!

I had lots of content, and was ready to dive into changes from PowerShell 2 to 3, new SQL 2012 Cmdlets, and coding with SQL Management Objects. However most of the group was just starting out with PowerShell, so I had to shift direction, ended up talking more than demoing. 🙂  

Would be interested in doing a PowerShell 101 class for any user group interested in the future. PowerShell is just an incredible tool that any DBA, Server Admin or Developer should learn and apply.

I’ve posted the slides on SlideShare.net

I’ll post the demo code as well shortly if requested, it needs some cleanup before it’s ready to share.

SQL Alert when you’re low on disk space

I found this gem while troubleshooting a SQL Server with a backup drive that filled up often. It uses the WMI provider will alert when ANY drive on the server goes below 10GB. You can change the value ‘10000000000’ below to adjust this. You’ll also want to change your operator from ‘Support’ to your operator name. 

Note that this requires Database Mail to be set up with an operator and valid email. Should work for SQL 2005 thru 2012 as is, mine was set up on SQL 2005.

The ability to use WMI Provider in SQL Alerts seems very useful, I have seen uses for WMI SQL Alerts, but they involved more code than just creating the alert. If you’re interested in exploring the metrics WMI exposes to you, check out the WMI Tester included in Windows. There’s also more information in WMI at the Microsoft site.

Been using it for a while and thought to share it. I couldn’t locate the blog where I found it originally. If that’s you, let me know so I can thank you. If you have any other useful SQL Alerts that take advantage of WMI, please let me know as well.

USE [msdb]
GO
EXEC msdb.dbo.sp_update_alert @name=N'Disk Less that 10GB',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=1800,
@include_event_description_in=0,
@database_name=N'',
@notification_message=N'',
@event_description_keyword=N'',
@performance_condition=N'',
@wmi_namespace=N'\\.\ROOT\CIMV2',
@wmi_query=N'SELECT * FROM __InstanceModificationEvent WITHIN 600 WHERE TargetInstance ISA "Win32_LogicalDisk" AND TargetInstance.FreeSpace < 10000000000', @job_id=N'00000000-0000-0000-0000-000000000000' GO --Replace Support with your Operator Name EXEC msdb.dbo.sp_update_notification @alert_name=N'Less that 10GB', @operator_name=N'Support', @notification_method = 1 GO

Copying indexes from Oracle to SQL Server

I’m working with a client that has access to an Oracle 10g database containing transactional data that they copy to their new SQL Server 2012 as a data warehouse. (We recently migrated the straight from SQL 2000 into SQL 2012, but that’s another story.) They already had scripts to copy the tables and data to a staging database, and then copy new records to their data warehouse. Queries were slow against this data, as no indexes were created on the target tables.

I wanted to copy the Oracle indexes to SQL Server, but found no simple way to do this, so I created a script to do so.

I used Toad for Data Analysts to connect to the Oracle database, which is a really fantastic program for working with Oracle.

After perusing online Oracle documentation (which is an adventure in itself), I determined that DBA_INDEXES and DBA_IND_COLUMNS have the data I needed.

The DBA_* tables roughly correspond to the sys.* tables in SQL server, these are the tables that hold the meta data for the Oracle database.

These tables hold more info than I wanted. Here’s a query you can run to see the index columns in your Oracle database. You’ll want replace OWNERNAME with the table owner in your environment.

Also note that I limit to 100 records in this example.

--Example 1, show index columns for tables owned by OWNERNAME. Replace OWNERNAME with the table owner in your environment
SELECT INDEX_OWNER,INDEX_NAME,TABLE_OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_POSITION,DESCEND
FROM DBA_IND_COLUMNS I
WHERE ROWNUM< 100 --just for display purposes, the Oracle equivalent of SELECT TOP 100 --ignore internal indexes I don't want AND SUBSTR(I.TABLE_NAME,1,3) <> 'BIN'
AND SUBSTR(I.TABLE_NAME,1,4) <> 'TEMP'
--replace OWNERNAME with your owner
AND I.TABLE_OWNER='OWNERNAME'

Some things to note about this query:

  • Each index column has a DESCEND value which indicates ASC or DESC, determining the index sort. In my environment all columns were ‘ASC’ so I didn’t need logic to handle this, but added it anyway to the script for your use 🙂
  • The COLUMN_POSITION determines where the column is in the index. The script accounts for up to 16 indexed columns, so it needs 16 table aliases in Example 5.

To determine the largest value for COLUMN_POSITION, I ran this query:

--Example 2, show maximum number of columns in any index owned by OWNERNAME. Replace OWNERNAME with the table owner in your environment
SELECT TABLE_OWNER,MAX(COLUMN_POSITION) AS LARGEST_INDEX_COLUMN_COUNT
FROM DBA_IND_COLUMNS I
WHERE SUBSTR(I.TABLE_NAME,1,3) <> 'BIN'
AND SUBSTR(I.TABLE_NAME,1,4) <> 'TEMP'
AND I.TABLE_OWNER='OWNERNAME'
GROUP BY TABLE_OWNER
ORDER BY MAX(COLUMN_POSITION) DESC

In my case the largest column count for any index was 13, but I went ahead and scripted for a maximum of 16 columns, which is the maximum number of index columns in SQL Server.

I considered that this task is an ideal use case for a SQL PIVOT statement, but Oracle 10g lacks support for this very useful feature. So I needed to brute force it with 16 table aliases, one for each column of the index.

--Example 3, show indexes and columns in order, in any index owned by OWNERNAME. Replace OWNERNAME with the table owner in your environment
SELECT I.TABLE_OWNER
, IX.INDEX_TYPE
, I.TABLE_NAME
, I.INDEX_NAME
, I.COLUMN_NAME AS COL1
, I2.COLUMN_NAME AS COL2
, I3.COLUMN_NAME AS COL3
, I4.COLUMN_NAME AS COL4
, I5.COLUMN_NAME AS COL5
, I6.COLUMN_NAME AS COL6
, I7.COLUMN_NAME AS COL7
, I8.COLUMN_NAME AS COL8
, I9.COLUMN_NAME AS COL9
, I10.COLUMN_NAME AS COL10
, I11.COLUMN_NAME AS COL11
, I12.COLUMN_NAME AS COL12
, I13.COLUMN_NAME AS COL12
, I14.COLUMN_NAME AS COL14
, I15.COLUMN_NAME AS COL15
, I16.COLUMN_NAME AS COL16
, I.COLUMN_POSITION
--, DESCEND
--, I.*
FROM DBA_INDEXES IX
JOIN DBA_IND_COLUMNS I ON IX.INDEX_NAME = I.INDEX_NAME
--note the LEFT JOIN for all subsequent columns, which may not exist
LEFT JOIN DBA_IND_COLUMNS I2 ON I.INDEX_NAME = I2.INDEX_NAME AND I2.COLUMN_POSITION = 2
LEFT JOIN DBA_IND_COLUMNS I3 ON I.INDEX_NAME = I3.INDEX_NAME AND I3.COLUMN_POSITION = 3
LEFT JOIN DBA_IND_COLUMNS I4 ON I.INDEX_NAME = I4.INDEX_NAME AND I4.COLUMN_POSITION = 4
LEFT JOIN DBA_IND_COLUMNS I5 ON I.INDEX_NAME = I5.INDEX_NAME AND I5.COLUMN_POSITION = 5
LEFT JOIN DBA_IND_COLUMNS I6 ON I.INDEX_NAME = I6.INDEX_NAME AND I6.COLUMN_POSITION = 6
LEFT JOIN DBA_IND_COLUMNS I7 ON I.INDEX_NAME = I7.INDEX_NAME AND I7.COLUMN_POSITION = 7
LEFT JOIN DBA_IND_COLUMNS I8 ON I.INDEX_NAME = I8.INDEX_NAME AND I8.COLUMN_POSITION = 8
LEFT JOIN DBA_IND_COLUMNS I9 ON I.INDEX_NAME = I9.INDEX_NAME AND I9.COLUMN_POSITION = 9
LEFT JOIN DBA_IND_COLUMNS I10 ON I.INDEX_NAME = I10.INDEX_NAME AND I10.COLUMN_POSITION = 10
LEFT JOIN DBA_IND_COLUMNS I11 ON I.INDEX_NAME = I11.INDEX_NAME AND I11.COLUMN_POSITION = 11
LEFT JOIN DBA_IND_COLUMNS I12 ON I.INDEX_NAME = I12.INDEX_NAME AND I12.COLUMN_POSITION = 12
LEFT JOIN DBA_IND_COLUMNS I13 ON I.INDEX_NAME = I13.INDEX_NAME AND I13.COLUMN_POSITION = 13
LEFT JOIN DBA_IND_COLUMNS I14 ON I.INDEX_NAME = I14.INDEX_NAME AND I14.COLUMN_POSITION = 14
LEFT JOIN DBA_IND_COLUMNS I15 ON I.INDEX_NAME = I15.INDEX_NAME AND I15.COLUMN_POSITION = 15
LEFT JOIN DBA_IND_COLUMNS I16 ON I.INDEX_NAME = I16.INDEX_NAME AND I16.COLUMN_POSITION = 16
WHERE I.COLUMN_POSITION = 1
--replace OWNERNAME with your owner
AND I.TABLE_OWNER='OWNERNAME'
--hide system indexes
AND SUBSTR(I.TABLE_NAME,1,3) <> 'BIN'
AND SUBSTR(I.TABLE_NAME,1,4) <> 'TEMP'
ORDER BY I.TABLE_OWNER, I.TABLE_NAME, I.INDEX_NAME, I.COLUMN_POSITION;

I then switched over to SQL Server Management Studio, created a single column index and scripted it, so I’d have the template code I need to build indexes. This is done in SQL 2012, but is adaptable to any SQL version. You can do this from Object Explorer in SSMS by opening the tree under Databases, Your Database, Tables, Your Table, Indexes, Right-Click, New Index, Choose Non-Clustered Index.

--Example 4, CREATE INDEX TEMPLATE
CREATE NONCLUSTERED INDEX [IX_MYTABLE] ON [dbo].[MYCOLUMN1],[MYCOLUMN2]--can add
(
[MYCOLUMN1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

You may want to modify the settings in the WITH clause. They are documented here.

So I took the results from Example 3 and modified them to generate the text of the statement in Example 4. Here’s what I came up with:

--Example 5, Generate CREATE INDEX script for Oracle indexes in any index owned by OWNERNAME. Replace OWNERNAME with the table owner in your environment
SELECT
'CREATE NONCLUSTERED INDEX '
|| I.INDEX_NAME -- You may want to prefix this with 'IX_', I decided to preserve the index names.
|| ' ON dbo.' --Assumes all target objects in the DBO schema
|| I.TABLE_NAME
|| '('
|| I.COLUMN_NAME || ' ' || I.DESCEND
|| DECODE(I2.COLUMN_NAME,null,'',',' || I2.COLUMN_NAME || ' ' || I2.DESCEND)
|| DECODE(I3.COLUMN_NAME,null,'',',' || I3.COLUMN_NAME || ' ' || I3.DESCEND)
|| DECODE(I4.COLUMN_NAME,null,'',',' || I4.COLUMN_NAME || ' ' || I4.DESCEND)
|| DECODE(I5.COLUMN_NAME,null,'',',' || I5.COLUMN_NAME || ' ' || I5.DESCEND)
|| DECODE(I6.COLUMN_NAME,null,'',',' || I6.COLUMN_NAME || ' ' || I6.DESCEND)
|| DECODE(I7.COLUMN_NAME,null,'',',' || I7.COLUMN_NAME || ' ' || I7.DESCEND)
|| DECODE(I8.COLUMN_NAME,null,'',',' || I8.COLUMN_NAME || ' ' || I8.DESCEND)
|| DECODE(I9.COLUMN_NAME,null,'',',' || I9.COLUMN_NAME || ' ' || I9.DESCEND)
|| DECODE(I10.COLUMN_NAME,null,'',',' || I10.COLUMN_NAME || ' ' || I10.DESCEND)
|| DECODE(I11.COLUMN_NAME,null,'',',' || I11.COLUMN_NAME || ' ' || I11.DESCEND)
|| DECODE(I12.COLUMN_NAME,null,'',',' || I12.COLUMN_NAME || ' ' || I12.DESCEND)
|| DECODE(I13.COLUMN_NAME,null,'',',' || I13.COLUMN_NAME || ' ' || I13.DESCEND)
|| DECODE(I14.COLUMN_NAME,null,'',',' || I14.COLUMN_NAME || ' ' || I14.DESCEND)
|| DECODE(I15.COLUMN_NAME,null,'',',' || I15.COLUMN_NAME || ' ' || I15.DESCEND)
|| DECODE(I16.COLUMN_NAME,null,'',',' || I16.COLUMN_NAME || ' ' || I16.DESCEND)
|| ') WITH ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]' -- Your values in the WITH clause may vary.
AS STATEMENT
FROM DBA_IND_COLUMNS I
LEFT JOIN DBA_IND_COLUMNS I2 ON I.INDEX_NAME = I2.INDEX_NAME AND I2.COLUMN_POSITION = 2
LEFT JOIN DBA_IND_COLUMNS I3 ON I.INDEX_NAME = I3.INDEX_NAME AND I3.COLUMN_POSITION = 3
LEFT JOIN DBA_IND_COLUMNS I4 ON I.INDEX_NAME = I4.INDEX_NAME AND I4.COLUMN_POSITION = 4
LEFT JOIN DBA_IND_COLUMNS I5 ON I.INDEX_NAME = I5.INDEX_NAME AND I5.COLUMN_POSITION = 5
LEFT JOIN DBA_IND_COLUMNS I6 ON I.INDEX_NAME = I6.INDEX_NAME AND I6.COLUMN_POSITION = 6
LEFT JOIN DBA_IND_COLUMNS I7 ON I.INDEX_NAME = I7.INDEX_NAME AND I7.COLUMN_POSITION = 7
LEFT JOIN DBA_IND_COLUMNS I8 ON I.INDEX_NAME = I8.INDEX_NAME AND I8.COLUMN_POSITION = 8
LEFT JOIN DBA_IND_COLUMNS I9 ON I.INDEX_NAME = I9.INDEX_NAME AND I9.COLUMN_POSITION = 9
LEFT JOIN DBA_IND_COLUMNS I10 ON I.INDEX_NAME = I10.INDEX_NAME AND I10.COLUMN_POSITION = 10
LEFT JOIN DBA_IND_COLUMNS I11 ON I.INDEX_NAME = I11.INDEX_NAME AND I11.COLUMN_POSITION = 11
LEFT JOIN DBA_IND_COLUMNS I12 ON I.INDEX_NAME = I12.INDEX_NAME AND I12.COLUMN_POSITION = 12
LEFT JOIN DBA_IND_COLUMNS I13 ON I.INDEX_NAME = I13.INDEX_NAME AND I13.COLUMN_POSITION = 13
LEFT JOIN DBA_IND_COLUMNS I14 ON I.INDEX_NAME = I14.INDEX_NAME AND I14.COLUMN_POSITION = 14
LEFT JOIN DBA_IND_COLUMNS I15 ON I.INDEX_NAME = I15.INDEX_NAME AND I15.COLUMN_POSITION = 15
LEFT JOIN DBA_IND_COLUMNS I16 ON I.INDEX_NAME = I16.INDEX_NAME AND I16.COLUMN_POSITION = 16
WHERE I.COLUMN_POSITION = 1
--replace OWNERNAME with your owner
AND I.TABLE_OWNER='OWNERNAME'
--hide system indexes
AND SUBSTR(I.TABLE_NAME,1,3) <> 'BIN'
AND SUBSTR(I.TABLE_NAME,1,4) <> 'TEMP'
ORDER BY I.TABLE_OWNER, I.TABLE_NAME, I.INDEX_NAME, STATEMENT

Some things to note about this statement:

  • ‘||’ is the Oracle equivalent of ‘+’ in SQL Server for concatenating string values
  • DECODE is a powerful Oracle function similar to IIF in MS Access, or the CASE statement in SQL Server. I use it to return the column name if it is NOT a null value, with a comma in the proper place.
  • In my environment I created a new filegroup to hold the indexes so I could manage their storage separately. If you do this, you’ll replace the [PRIMARY] keyword in the above statement with the name of your filegroup.

I used the above statement to generate over 400 CREATE INDEX statements, which I copied and pasted to a SQL script to run in my SQL Server.

I did a small amount of hand editing in Notepad++ to add a GO statement between each statement in case one failed, but amazingly the statement completed in under 30 minutes. The client reported that all the queries that used to take 10-20 minutes all ran in just a few seconds, so the indexes are working. I’ll check whether SQL Server recommends any missing indexes and create them in the near future.

And that’s how I copied indexes from Oracle 10g to SQL Server 2012.

I’ve neglected to mention that prior to this step, I also scripted the primary keys as clustered primary keys, and foreign keys as well. If there’s any interest I’ll share those in a future post.

Any questions, or better approaches? I would love to hear them.

The great adventure: Migrating from SQL 2000 to 2012

I worked with a client that migrated from SQL 2000 32 bit database straight up to SQL 2012 64 bit! Quite a challenge, and quite the journey over 12 years and 5 releases (2000,2005,2008,2008R2,2012) of SQL.

Some of the complicating factors include:

Backup and Restore

It is not possible to restore a SQL 2000 backup into SQL 2012. The solution is install an intermediate SQL instance (2005,2008,2008 R2), restore to that instance, then detach and attach into the SQL 2012 instance. For smaller databases, I was able connect to the SQL 2000 database from my SQL 2012 instance and use ‘Generate Scripts…’ in the context menu to script the objects and data.

DTS

SQL 2000 DTS packages no longer supported. Found the solution on MSDN from Jyoti Grove’s comment. This required locating and installing the SQLServer2005_BC_x64.msi file on my 2008 R2 install disk. I created a SSIS project in BIDS 2008, and migrated the dts packages individually into a SQL 2008 R2 SSIS project. Was able to migrate this project into SQL 2012 VS 2010 project. Some legacy tasks like ActiveX and older connection managers need to be rewritten, but it’s a great start.

Linked Server to Oracle

Installed the Oracle 11g 64 bit client, exported the connection info using the Oracle Net Manager (version 9) on the old server, imported using the same tool (version 11g) on the new server, set up an ODBC System DSN and I was able to talk with Oracle! They have an instant client available, but it wasn’t instant for me to set up. This blog post helped me quite a bit.

32 and 64 Bit driver side by side 

Client was using Toad as an IDE for Oracle and SQL, but it’s built on Delphi, and only 32 bit. I downloaded the zip of the  instant client 32 bit version, and during the install was able to point to the instant client zip file. Once installed, added the environment variable for oracle, and started toad and was able to find the Oracle server.

Still to do

Will need to rework parts of the SSIS packages, and recreate the SQL Agent jobs.

Client exports report results to Excel, may be some issues to work around there.

Results

Got this client migrated successfully. It was the same client that had the data warehouse with tables copied from Oracle, so I figured out how to script the indexes from Oracle into SQL Server. The database is fast and they’re happy.

The SQL Saturday experience!

This weekend I attended and volunteered at SQL Saturday #141, hosted by the awesome folks at Nova University. I didn’t speak this year, but helped out with parking, food, and giving directions to the attendees throughout the day. It was a great experience.

If you’ve enjoyed SQL Saturdays, I highly encourage you to volunteer for one in the future. I met so many great people, and was proud to be a part of such a great community event.

I kept running into old friends and acquaintances, some of whom just happened to be experts with aspects of SQL Server that I had questions on… It’s great how that works.

Thanks to all the sponsors and volunteers who make events like this happen. If you’re a techie person that works with SQL, but haven’t interacted with the South Florida SQL community, do yourself a favor and get involved. Find a local user group meeting in your area, just go and introduce yourself and learn something new. You’ll be glad you did.

Free hands-on SQL 2008 training from Microsoft!

While the folks at Microsoft have offered these for a long time, I’m sure most people don’t know about it.

Technet Virtual Labs offers focuses 90 minute virtual labs so you can play with new features in the latest Microsoft technologies just using Internet Explorer.

Check out the SQL Server 2008 section to learn about features that interest you… like the two clustering labs… before they release SQL 2012 🙂

DIY Hyper-V, Active/Active Clustering and SQL Server – Demo and Step by Step Instructions

Recently spoke at South Florida IT Camp Saturday on using Hyper-V, Windows Server 2008 R2 SP1 Enterprise, Microsoft iSCSI Target 3.3, and SQL Server 2008 R2 SP1 to create an Active-Active SQL Cluster.

I actually managed to cover all the cool parts in an hour, and got some great feedback.

If you’re interested in implementing this demo yourself, I’ve posted:

Network Diagram (Overview of the virtual server components in the SQL Cluster)

Step by Step Instructions (The steps I took to create the Active/Active SQL Cluster)

Hyper-V SQL Clustering Worksheet (Planning the values in this worksheet in advance makes this process simple.)

My Slide Deck (Because everyone loves PowerPoint)

I need someone who’s interested to volunteer as my ‘technical editor’.. To walk through these steps, help me clarify or correct anything unclear. If you’re interested, or have other questions, please  contact me.

I’d love to hear from you!

-David

SQL cluster on the cheap with Hyper-V and iSCSI

Speaking on how to set up SQL clustering in a virtual environment, for development and testing. Discuss options for production deployment.
Now, how to compress 9 hours of setup into a session…
Expect some smoke and mirrors along with some neat technology.

Does anyone have some SSDs I can borrow? 🙂

July 23rd http://itcampsaturday.com/southflorida/

Tentative outline:

  • Setup Hyper-V Host
  • Install two virtual networks (lan,iscsi)
  • Create OS template
  • Deploy and install 3 servers
  • Configure iSCSI storage and targets
  • Configure Windows Cluster
  • Install SQL Node 1
  • Add SQL Node 2
  • Test
  • Discuss production deployment options
  • Q & A