DallasDBAs.com

Explaining SQL Server in plain english

Header Right

Install

IRL #2: Cannot Connect to SQL Server

by Leave a Comment

IRL #2 – Connectivity Issues

Problem: Application owner is trying connect to SQL Server to create a database, connection failed.  Error message suggests checking Instance Name and “Allow Remote Connections” option set to True.

Background: This client has a wide variety of SQL Servers in Prod, Test and Dev.  Many different versions and editions, quite a few coming from 3rd party vendors.  This was in a new test environment for an application already up and running in Prod.

Resolution Attempts:

  • Try to Connect via IP address – failed
  • Verify Instance name – verified
  • Verify “Allow Remote Connections” is set to 1/True – it is
  • Verify SQL Browser Service Running – discovered Set to Disabled
  • Verify TCP/IP network protocol enabled – discovered disabled

Solution:

  • Enable TCP/IP for Named Instance SQLEXPRESS03
  • Restart Instance
  • Set SQL Browser Service to Automatic and start
  • Test logon via UDL file – success

Lessons you can benefit from:

  • SQL Express default install only enables Shared memory connections
  • Error messages are not always going to give you complete information
  • Have a process for testing

This entire process took about 10 minutes, including the instance restart.  There are 3 other Express edition installs on this server.

 

Other posts in the IRL – In Real Life series:

https://dallasdbas.com/category/sql/irl/

Thanks for reading!

Kevin3NF

Your SQL Servers are misconfigured

by Leave a Comment

So are some of mine.

So are 90% of the servers I run across, especially in mid-size businesses ( <1000 employees, or 10-20 servers typically).

Its probably not your fault.

There are a bunch of instance level settings in SQL Server that need to be set that are specific to your environment and the workload you are going to run against it.  Most of the defaults that are in the SQL Server installer are set up to make sure the performance is acceptable on a very modest server.  Until version 2016, the installer had not changed much.

The problem is that in a large number of organizations, the person doing the installation of SQL Server is a Developer, or a SysAdmin running a script.   Both are great at their core jobs, but does either of them know what Cost Threshold for Parallelism is?  It is not in the installer…it has to be configured post install or left at a very old default number that most likely makes no sense for you.

Likewise, if you are running SQL Server installed from a script that came with a 3rd party software product, you very likely have the “safest” possible settings in place.

Some of the most common things I see everywhere:

  • Instant File Initialization not enabled
  • Cost Threshold for Parallelism not set correctly
  • MAXDOP set to 1 or ALL CPUs
  • MAX Memory left at default
  • MAX Memory left at default on a multi-instance server
  • Default file locations for system databases, or all
  • No maintenance processes

That is the short list.

Sales Pitch:  I will give you the first hour (one server) free to check on your configuration.  This involves me running a set of scripts, analyzing the results and sending back some recommendations.  Yes, I want to implement them for you as well.

Alternatively, a lighter weight version of this is the Constant Care offering from Brent Ozar Unlimited.  I can take his results and explain what they mean so that we can make intelligent decisions together.  We are not affiliated with Brent Ozar Unlimited, other than as part of the SQL Community.

Let’s talk.  Click HERE to set a meeting on my Calendar.

Thanks for reading!

Kevin3NF


SQL Saturday Minnesota 2017

by Leave a Comment

Now that the official schedule has been published, I am pleased to announce that I will be doing a pre-con and a regular session at SQL Saturday Minnesota on October 6-7. 2017.

The Friday pre-conference will be “DBA Fundamentals for the Junior, Accidental and non-DBAs.”  This will be a wide ranging day-long discussion on the underlying fundamental pieces of SQL Server Administration that are beneficial to both new DBAs, and those that have to work with or fill in for them.  Please see the link for details, and feel free to contact me with any questions.  There is a $110 (plus service fees) charge for this, which is split between me and the local SQL User group organizing the event, after expenses.

The Saturday regular session will be “Backups for new/non-DBAs…the Why, not the How”, where we will cover Disaster recovery scenarios and the part SQL Server plays in the recovery.  We won’t spend much time on specifically defining backup types, etc.    Currently this is scheduled for the 1-2pm time slot, but that may change.

Edit: my Saturday session is now 10:15am – 11:15am 🙂

I’m very excited to be coming to the MSP area!  I even have a friend that lives in the area that I get to see for the first time in 3 years…plus all of the new friends I will make at the events!

Thanks,

Kevin3NF

Installing SQL 2016 Developer – Extreme Basics

by Leave a Comment

If you are a student, a developer or a non-dba wanting to get started with learning more about SQL Server and you need an install to play with, you have a couple of free choices:

  • SQL Server Express – free, but has some size and performance restrictions. Also, no SQL Agent, so no job scheduling.
  • SQL Server Developer – also free, full code of the Enterprise Edition, but cannot be used legally in production. Because its a dev edition.

If you’ve never installed SQL server before and are not sure what to do, this video is for you. It is targeted to new SQL users, installing on their laptops/workstations. I take all the defaults and describe why. I install just the database engine (and the SQL Agent that comes along with that)…no Reporting services, no Analysis Services, etc.

This is just so you can see it before you do it. Enjoy!

Thanks for watching!

Kevin3NF

T-SQL Tuesday: Still dealing with the same problems

by 2 Comments

t-sqltuesday

This post is my contribution to the monthly blog party known as “T-SQL Tuesday.”  This month is being hosted by Andy Mallon, and covers  the topic We’re still dealing with the same problems.

The topic was inspired by a Tweet from Allan Hirt.   Allan is exactly right.

I entered the IT field officially ( meaning full-time) in 1998 as an Access Developer.  I’ve done desktop support, database dev, network, phone systems and for the last 17 years I’ve been working exclusively with SQL Server, v. 6.5 – 2016.

In all these years, we still have users that don’t know to reboot a frozen laptop, store passwords anywhere but a sticky note and call me to “fix the internet” (pro tip…its broken.  Go read comments on any news article.)

Narrowing this down to my slice of the IT world (SQL) and then more to Admin, and even further took some time.   The thing that I see and deal with the most came down to “blindly clicking OK and accepting all the defaults, all the time.”

I get it.

On my first SQL installation, I clicked OK and yes right through the install wizard and had a functional SQL 2000 install on my desktop.   On the C drive, which was almost full already.

Installation defaults that are going to bite you (not version specific, and the installer is getting better):

  • Files all on the C drive
  • One TempDB data file (improved in SQL 2016)
  • Backups on C drive
  • No automated backups
  • Allow SQL to use ALL the memory
  • Allow SQL to use ALL the CPUs
  • Builtin\Administrators group not default*
  • Compressed backup set to OFF

If you have any of the above, please research each and sort out why I listed it…you will learn more along the way than I can teach you here.  Each of these is documented extremely well by both Microsoft and the SQL Community.

* let the religious debate start in 3…2…1…

Another issue that is not done at install time, but shortly thereafter when you create your first database is the VERY common failure of setting up a new Database using the default FULL Recovery model, without a Transaction Log backup plan in place.

There are hundreds, maybe thousands of forum posts asking the same question: “My database is 1GB, but my T-log is 500GB and filled the drive.”

I had this question brought to me just 2 weeks ago at SQL Saturday Dallas by developer turned accidental DBA.  We spent 30 minutes discussing his database restore failures and why it took so long.   At the very end he mentioned that the .LDF file was HUGE compared to the .MDF file, and then walked off to the next session.  That HUGE .LDF is taking most of the restore time due to writing zeros into it well after the data is written.

Long story short:  Make sure you understand the defaults before you install, and implement a proper backup plan.  You can learn these easily and I’m expensive if I need to come fix them for you 🙂

Kevin3NF

 

 

 

 

Min and Max Server Memory in English

by Leave a Comment

This one is for the new DBAs…

There is a lot of confusion on memory settings in SQL Server.  Specifically Min and Max settings found in the Properties of an instance:

There are dozens, if not hundreds of blog postings that will go into beautiful detail about all of the intricacies of SQL Server memory…after all, SQL lives there!   Thus, it is very important to have a basic understanding early in your SQL DBA career….especially if you are an accidental DBA.

In the screenshot above, I have set the Min and Max on my laptop to 2GB and 14GB.  Its a 16GB laptop, so I left 2GB for the O/S…pretty basic stuff.

Max Server Memory is fairly obvious…when SQL Server reaches this point, it stops allocating and starts paging to disk as necessary.

This is pretty much the same as setting a throttle control on a car.  Or a restrictor plate in NASCAR.  Without it you can run the car engine all the way to the max if you want, but eventually the other systems are going to fail (Cooling, transmission, brakes, etc.).  Same thing if you don’t leave memory for the O/S.

Min Server Memory seems to get the most bad information spread around.   SQL Server does NOT automatically grab memory up to the Min setting when it starts.  However, once it gets there, it doesn’t give back.

Back to the car analogy…if you start up and head off down the road at 20 mph, you are above the default (0), but not at the max (100+, depending on the car and the tires…).  If you set the cruise control, you can accelerate up and down above 20, but you won’t go below that unless you hit the brakes.

So that’s it…by default, SQL installs at idle and full speed ahead.  Its your job to turn on the cruise control and not redline the engine until it blows.

There are holes in the analogy if you dig deep enough, but this should give you a real-world base to work from as you grow in your skills.

Kevin3NF