Data Collector Clustering Woes
Posted on March 19, 2013
| 8 minutes
| spaghettidba
During the last few days I’ve been struggling to work around something that seems to be a bug in SQL Server 2008 R2 Data Collector in a clustered environment. It’s been quite a struggle, so I decided to post my findings and my resolution, hoping I didn’t contend in vain.
SYMPTOMS:
After setting up a Utility Control Point, I started to enroll my instances to the UCP and everything was looking fine.
[Read More]dba_runCHECKDB v2(012)
Posted on February 27, 2013
| 3 minutes
| spaghettidba
If you are one among the many that downloaded my consistency check stored procedure called “dba_RunCHECKDB”, you may have noticed a “small” glitch… it doesn’t work on SQL Server 2012!
This is due to the resultset definition of DBCC CHECKDB, which has changed again in SQL Server 2012. Trying to pipe the results of that command in the table definition for SQL Server 2008 produces a column mismatch and it obviously fails.
[Read More]Discovering resultset definition of DBCC commands in SQL Server 2012
Posted on February 26, 2013
| 2 minutes
| spaghettidba
Back in 2011 I showed a method to discover the resultset definition of DBCC undocumented commands.
At the time, SQL Server 2012 had not been released yet and nothing suggested that the linked server trick could stop working on the new major version. Surprisingly enough it did.
If you try to run the same code showed in that old post on a 2012 instance, you will get a quite explicit error message:
[Read More]Extracting DACPACs from all databases with Powershell
Posted on February 13, 2013
| 3 minutes
| spaghettidba
If you are adopting Sql Server Data Tools as your election tool to maintain database projects under source control and achieve an ALM solution, at some stage you will probably want to import all your databases in SSDT.
Yes, it can be done by hand, one at a time, using either the “import live database” or “schema compare” features, but what I have found to be more convenient is the “import dacpac” feature.
[Read More]Using QUERYTRACEON in plan guides
Posted on February 8, 2013
| 4 minutes
| spaghettidba
Yesterday the CSS team made the QUERYTRACEON hint publicly documented.
This means that now it’s officially supported and you can use it in production code.
After reading the post on the CSS blog, I started to wonder whether there is some actual use in production for this query hint, given that it requires the same privileges as DBCC TRACEON, which means you have to be a member of the sysadmin role.
[Read More]Manual Log Shipping with PowerShell
Posted on February 8, 2013
| 8 minutes
| spaghettidba
Recently I had to implement log shipping as a HA strategy for a set of databases which were originally running under the simple recovery model.
Actually, the databases were subscribers for a merge publication, which leaves database mirroring out of the possible HA options. Clustering was not an option either, due to lack of shared storage at the subscribers.
After turning all databases to full recovery model and setting up log shipping, I started to wonder if there was a better way to implement it.
[Read More]Do you need sysadmin rights to backup a database?
Posted on January 9, 2013
| 2 minutes
| spaghettidba
Looks like a silly question, doesn’t it? - Well, you would be surprised to know it’s not.
Obviously, you don’t need to be a sysadmin to simply issue a BACKUP statement. If you look up the BACKUP statement on BOL you’ll see in the “Security” section that
BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. But there's more to it than just permissions on the database itself: in order to complete successfully, the backup device must be accessible: [.
[Read More]Replaying Workloads with Distributed Replay
Posted on November 22, 2012
| 6 minutes
| spaghettidba
A couple of weeks ago I posted a method to convert trace files from the SQL Server 2012 format to the SQL Server 2008 format.
The trick works quite well and the trace file can be opened with Profiler or with ReadTrace from RML Utilities. What doesn’t seem to work just as well is the trace replay with Ostress (another great tool bundled in the RML Utilities).
For some reason, OStress refuses to replay the whole trace file and starts throwing lots of errors.
[Read More]More on converting Trace Files
Posted on November 8, 2012
| 2 minutes
| spaghettidba
Yesterday I posted a method to convert trace files from SQL Server 2012 to SQL Server 2008R2 using a trace table.
As already mentioned in that post, having to load the whole file into a trace table has many shortcomings:
The trace file can be huge and loading it into a trace table could take forever The trace data will consume even more space when loaded into a SQL Server table The table has to be written back to disk in order to obtain the converted file You need to have Profiler 2008 in order to write a trace in the "
[Read More]Convert a Trace File from SQLServer 2012 to SQLServer 2008R2
Posted on November 7, 2012
| 4 minutes
| spaghettidba
Recently I started using RML utilities quite a lot.
ReadTrace and Ostress are awesome tools for benchmarking and baselining and many of the features found there have not been fully implemented in SQLServer 2012, though Distributed Replay was a nice addition.
However, as you may have noticed, ReadTrace is just unable to read trace files from SQLServer 2012, so you may get stuck with a trace file you wont’ abe able to process.
[Read More]