Category Archives: Development DBA

MOAR sp_DatabaseRestore

script it…walk away.

people are talking about me again… o_o

However instead of plots to overthrow my kingdom, or curses that would cause me to eat pie and lose weight, or saying my name three times in a mirror so that I become summoned and forced to bust a cap…instead of stuff like THAT…they are props for working on some code!

I made some new changes and fixed an embarrassing bug in my previous contribution for the sp_DatabaseRestore.  In an ideal world I would just skip the part of this post where I point out my mistakes and explain what they were, however Github does a great job at keeping history so…

Continue reading →

Automated Restore Fun (ARF)

script it…walk away.

Over the last a couple of weeks I started reviewing an automated restore solution I had been playing around with for a couple years.  RedGate SQL Backup had thrown some errors and since we couldn’t go without backups while waiting on RedGate support we decided to move ALL backup operations to Ola Hallengren’s scripts.  We also decided to ditch RedGate because they sent us back a list of reported bugs we had submitted and told us they aren’t going to fix it. So in other words “despite the fact that you have multiple licenses of multiple softwares across many servers, we feel your issues as a customer aren’t important to us as a software company.” With that we decided to roll our own solutions that we can customize for our environment…

Continue reading →

Cache Money

18_The_Nuka-Cola_ChallengeI was reading the Voice of the DBA and it referred to a post by Brent Ozar about caching at the database level.  After discussing it with my homey, he thought it sounded a lot like memoization (I like to say it with a 3 stooges voice, mem moy Zay Shun).  According to wiki, Memoization is a specific case of optimization, which seems to be what we are doing when we cache the results of a stored procedure.

Continue reading →

Workload Simulation With SQL Data Generator

testing is science…

Introduction

In college one of my mentors was a jedi  master of simulations and testing.  The guy could write a simulation for just about anything you can think of, and with anything you can think of.  I’m pretty sure that he once made a neural network for simulating dinosaur procreation rates…using sticks and mud (true story).  Simulating workloads can help you thoroughly test ideas and present data to management that will allow them to make informed decisions.  IRL, your career will be much happier if you can test your ideas BEFORE deploying them to production.  No one is asking you to have good ideas all the time, but the business is depending on you to come up with ideas that will work as intended.  The only way to accomplish this responsibly, is to test.

Continue reading →

Reverse Engineering using SQL Profiler

31_Reaver

The best defense is a strong CHAINSAW SWORD!!!

There may come a time in your career when you are tasked with finding out how a 3rd party application functions because some 3rd parties have completely closed source applications, or better yet, APIs that perform poorly.  Microsoft SQL Server Profiler can help you identify what tables are used, procedures and what data is inserted when functions are run.

Continue reading →

Search string across all tables and columns

01_Vault_101_Citizenship_Award

Pipboy level utility!

I came across a wonderful script by SQLDenis at Less Than Dot – Blog. I have been mapping data between a Numara TrackIt 8 system and a ManageEngine ServiceDesk system. While trying to find out how ManageEngine maps fields (with no database documentation, or assistance from their support team), I came across some fields on the web form that I needed to map, but I couldn’t find them in the database. SQL Search by RedGate helped to some degree, but in this case I needed something a little faster.

Using the sp written by SQLDenis at http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/the-ten-most-asked-sql-server-questions–1#2 , I was able to put an identifying string (think codeword) then search it out. This enabled me to find form fields that used columns in the db named “Attribute_301” etc.

Thanks SQLDenis, you may take your place among the real men of genius.

SSIS Package to Insert New Records

2197863-walker_of_the_mojave

Script it…then walk away!

The problem: Create a SSIS package that will update a table with only new records

The quick and dirty solution:…

1. Drop a Data Flow Task
2. Inside 1, Drop (in this order) an OLE DB Source, Lookup, and SQL Server Destination
3. The OLE DB Source is your select statement from data source, for example mine is:
SELECT data1, data2, data3, etc, from sourceTable
4. The lookup contains a query to compare, for example mine is:
SELECT data1, data2, data3, etc, from destinationTable
5. Configure the Error Output on the Lookup to Redirect Row
6. Connect the Red Arrow from the Lookup to your SQL Server Destination