1,707 reputation
66
bio website ashdar-partners.com
location Downingtown, PA
age 47
visits member for 4 years, 10 months
seen Apr 4 at 21:04

I have been a principal at Ashdar Partners since 2003, a SQL Server DBA since 1998, a developer since the 1980s and a nerdy computer enthusiast since the 1970s. I've probably used more obsolete computer systems than you have.


Jul
11
comment Backing up database fully from remote server, and restoring to another remote server
The disk clause of a RESTORE command takes UNC paths, and drive mappings tend to get confused since they are per-user and not per-machine. (IOW, the X: that you see might not be the X: that the SQL instance sees if it sees one at all.) So, rather than "DISK = 'X:\MySiteDB.bak'", you can use "DISK = '\\PRODMACHINE\BACKUPSHARE\MySiteDB.bak'". This presumes that your SQL instances are domain accounts, running with enough read/write permissions on those files and shares. Also, don't share out the root of your C:, make a folder to put the backups into and share that to minimize the attack surface.
May
5
comment SQL Server 2012 backup Maintence plan per dataabse
How are you going to ensure that the jobs run serially and not all at the same time? As databases get bigger, they will take longer and longer to backup. You will have to tinker with individual backup job start times. Why are the jobs failing now? Focus on fixing that-going to maintenance plans is a step backwards IMO.
Apr
26
comment MSSQL Datafile on DFSR
All of the above, but it's even worse than that. If your solution isn't backing up your LDF file, you don't have a solution. SQL Server relies on being able to read the LDF when the 'other' nodes starts up after a failover. Not all committed transactions are immediately written to the MDF file. They may only be in the LDF file. If you don't have a good copy of the LDF file, you may loose transactions that the database claimed were committed.
Feb
25
comment Can I move all DB files on SQL server 2008 to a different logical drive and then change the drive letter?
+1 for this method. I've this many, many times. The only thing is that you want to be sure to copy the security ACLs for the files and folders onto the new drive as well or the server may not be able to open the files.
Feb
14
comment SQL Server Replication Requires the actual server name
Possible duplicate: stackoverflow.com/questions/8212032/…
Feb
13
comment if I select more than 15 fields, I get catastrophic failure
1st hit from google: social.technet.microsoft.com/Forums/da/winserverpowershell/… His powershell script uses System.Data.Odbc and tries whatever connection string you provide. You need to replace the values of the $connectstring and $sql variables with something that fits your situation. The $sql variable is up to you. Getting $connectionstring is a little trickier. connectionstrings.com/pervasive gives a "standard" Pervasive ODBC string as Driver={Pervasive ODBC Client Interface};ServerName=myServerAddress;dbq=@dbname;
Feb
11
comment if I select more than 15 fields, I get catastrophic failure
Another troubleshooting trick I use is to write a small program that I can run directly on the server (using vbscript or powershell; there are many examples to be found through google) that uses the driver to see if I can run the same query outside of the SQL Server Linked Server scheme. Sometimes, I find that the problems are login-related (ie. my account versus the SQL Server service account), sometimes the error messages are a little more detailed.
Feb
10
comment if I select more than 15 fields, I get catastrophic failure
Is the problem related to the number of columns you select or to one particular, problematic data type? IOW, using your example, perhaps the driver has a problem with the data type of field3 and SELECT field1,field2,field4 ... might work. Does SELECT field3 by itself work OK?
Feb
7
comment Separate Database For SQL Server Reports
Also relevant: blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/…
Feb
7
comment Separate Database For SQL Server Reports
"mirroring" != "availability group". Availability groups are a SQL 2012 thing. You would be looking at something more like this-> msdn.microsoft.com/en-us/library/ms187110(v=sql.105).aspx
Feb
7
comment Separate Database For SQL Server Reports
You say that indexing and maintenance are done frequently, then go on to say that there is little fragmentation in the indexes. Have you thought about using Hallengren's index maintenance scripts so that indexes with little fragmentation and not unnecessarily reindexed? What kind of lag could be tolerated on the reporting side if you separate it from the OLTP side? Why can't you use the tlog backups generated by "Data Protection Manager" to restore to another database on a different server?
Jan
29
comment Is it possible for MSSQL 2012 & 2008
I'd be very careful when installing SQL 2008 after SQL 2012 is already installed, especially if the 2012 is already a production instance. If you do go down that path, make sure you have solid backups of your server (and not just your databases) before you start. (If it were me, I'd find another machine/VM/something, and install 2008 there.) If you are installing SQL2008 first, then SQL2012, then forget I mentioned it.
Jan
13
comment Missing MSSQL10.SQLEXPRESS folder
Look in the windows event log to see why the service isn't starting.
Nov
5
comment Will more memory help my CPU-peaking SQL Server 2008 R2
Does "single cpu" mean single socket, multiple core? None of the large hardware vendors has sold single-core servers for many, many years. More RAM might help if you see lots of I/O, not if you are seeing lots of processor utilization. (In fact, adding RAM might make your cpu problem seem worse.) I would look into optimizing my queries, index tuning, ensuring that object owners are specified in procedures, etc. If that server is more than five years old, you should look into updating it.
Nov
5
comment ASP.NET MVC incorrect connection string when connecting to mssql server
The error message implies that your app can't connect at all to the SQL instance, as opposed to connecting and then failing for a bad login/password. Normally this means DNS, firewall, instance-down or SQL Browser problems. Are you saying that you can connect via SSMS? Is there an instance name? Can you post your connection string, with any sensitive/identifying info changed? Just to be sure, your production iis and sqlserver are both at hostgator and on the same box, right?
Oct
13
comment cannot connect to sql server express from sql server standard
SQL Server Agent doesn't play a part in managing connections or validating logins, so it shouldn't affect what you are trying to do. If SQL Agent is stopped, you won't be able to run scheduled tasks. You want to be sure that the SQL Server engine itself is running, and that the sqlbrowser is running.
Oct
10
comment Optimize SAP SQL Server database using DTA
If your data gets updated, re-indexing should be part of your regular database maintenance, don't let anyone tell you different. The question is "how often to reindex", not "should I reindex?". With online reindexing, this doesn't have to be very painful, I suggest that you look at implementing Ola Hallengren's scripts, which are very popular with DBAs. They can be configured to skip tables that aren't significantly fragmented. ola.hallengren.com
Oct
10
comment Optimize SAP SQL Server database using DTA
+1 for all of the above. If you decide to go forward on your own, be sure that you have a script that can remove your changes and put the database back to the way SAP expects it to be. If you don't, and the next upgrade process fails, you will be in trouble. I have been in the spot where an upgrade to the database fails because the client "improved" the database. If SAP declare that you are off of support because of something you have done, your boss may use your salary to pay SAP to fix things.
Oct
10
comment Database Engine Services Required to install ssrs?
I presume that there is some icon that you can see and click, as the originator of the question, that I can't see. All I can find is this: meta.stackoverflow.com/questions/5234/…, which talks in generalities and does not seem to answer "How do I mark an answer as accepted?"
Oct
10
comment Setting Timeouts: SQL Server 2008/IIS 7.5
I'm not 100% on protocol for this, either. I've never edited a question posed by another, but I did just alter the title and tags and "submit the change for peer review". We'll see if I get scolded.