In a nutshell Endpoints are ways that people, or applications can connect to the SQL Server. There are several different kinds of end points which can be created; four to be specific. Two are system specific, the SERVICE_BROKER and DATABASE_MIRRORING endpoints can only be used for the SQL Service Broker and Database Mirroring respectively. The other two are for general use. They are the SOAP and TSQL endpoints.
Without knowing it you use an endpoint to connect to the SQL Server each time you connect. There are actually 5 endpoints created by default on each instance of SQL Server. You can check then out by querying the sys.endpoints DMV. When you connect to the SQL Server using TCP (port 1433 by default) you are using the TSQL Default TCP endpoint. By default all users have the rights to connect to this endpoint. You can create other TCP endpoints on different ports for specific users to connect to. This would be handy if you have several applications coming into the SQL Server from a single server, and you wanted to be able to separate there traffic through the firewall so that the network admins could watch the traffic in the event of an issue. You could create a TCP endpoint for each application, and assign only that application IP rights to use that endpoint. You then have the application specify the port number that it will be connecting through in the connection string.
The SOAP endpoints are used in a similar way, but instead of allowing regular TSQL connections they allow SOAP calls to be made directly against the database. (I’m not that up to speed on SOAP so that’s about all I’ve got on that topic.)
Endpoints are created with the CREATE ENDPOINT command with various switches depending on what kind of endpoint you are creating and how much security you require on the endpoint.
The endpoint that I’ve used the most would have to be the service broker endpoint. It’s used to allow SQL Server service broker on one SQL Server to talk to the Service Broker of another SQL Server.
One thing to remember about endpoints is that they are used for inbound connections only. Outbound connections do not require or use an endpoint.
Well, this has absolutely nothing to do with SQL Server, but I just had to post this anyway.
We were having dinner at Chick-fil-A and the Chick-fil-A cow was there. My wife snapped this photo, that I just had to share with everyone. How totally awesome is this. (Comments are very much welcome.)
I’ve published a new tip over on SearchSQLServer.com entitled “Configuring SQL Server memory settings“. In it I talk about how to correctly setup the memory settings for SQL Server to get SQL setup correctly.
When using 64bit SQL Server getting the memory settings right is pretty easy. Simply set the maximum to what you want and you are good to go.
Getting them right in the 32bit versions of SQL Server is a bit harder. You have to deal with the OS level of enabling the Physical Address Extensions (PAE) and the 3GB switches. You then need to enable AWE within SQL Server and then set the max memory setting.
If you are using Windows 2003 SP1 or later PAE will enable for you automatically. The /3GB switch however won’t. Since I have to add the /3GB switch I like to add the /PAE switch in there as well. My theory is why make Windows decide to do something automatically when I can simply override the logic and turn it on every time, especially when it’s something that I’m going to want enabled every time the server boots.
Now as to the max memory setting for SQL Server… There are pretty much two prevailing schools of thought.
- Give Windows between .5 and 1 Gig of memory and give SQL the rest.
- Give SQL 75% of the physical memory and leave the rest for Windows.
I’ve tried both and both seam to work fine. If you have less memory to work with you will probably want to stick to option 1. When you start working with huge amounts of RAM (64 Gigs plus) is when Option 2 starts to look more workable.
These rules obviously all start to change when you have more than one instance installed as you need to balance your max memory between the instances.
If you are using less then 2 Gigs of RAM for the instance don’t enable AWE on the instance. I’ve seen it lead to SQL Server acting strangely and performing very strangely. When setting your max memory setting for more than one instance don’t forget to add up the max memory for all the instances and make sure to leave Windows room to work with or your server will suffer.
Don’t forget about my post on setting the min server memory setting in SQL Server.
Thanks to all who attended this morning webcast ‘Under The Hood of SQL Server – Checking Out Internals’. This mornings webcast was a complete success. The number of people attending was fantastic, and some excellent questions were asked. If I remember correctly there were about 140 people in attendance. For those who weren’t able to attend the webcast this morning, it was recorded and as soon as it’s published I’ll post the link to the video.
If you have a question specific to the webcast please email my good friend Andy Grant with your questions and he will route them to myself or Jason Hall.
Don’t forget to check out the tech brief which we were talking about.
For those that don’t get the Tech Net news letter Microsoft and Seagate are producing a new comic strip called Heroes Happen Here. It’s published on the Tech Net blog here. You can also grab the RSS feed in your favorite RSS reader here.
So far it shows some promise and I’ve gotten a good laugh out of it a few times; I am a pretty major dork some times though.
The folks at Quest Software asked me to write a paper for them discussing the differences between managing a SQL Server using just the Microsoft provided tools and their Spotlight on SQL Server Enterprise product. That paper has just been released and can be downloaded here. We’ll also be discussing this paper (among other things) at a web cast Thursday 2/7/2008 at 8am which you can read about here and here.
Well today is my birthday. Since it’s a regular posting day I figured I’d skip the technical posting and just say hi. (I’ve got this thing against working on my birthday. I’d much rather spend the day with family and friends. It’s my day and I’ll do what I want, as long as it’s ok with the wife at least :-).)
On Thursday Feb 7, 2008 at 8am Pacific (11am Eastern, 4pm GMT) myself and Jason Hall, Systems Consultant at Quest Software will be co-hosting a webcast looking into the challenges in seeing exactly what is happening within SQL Server (including SQL Server 2008) using Spotlight® on SQL Server Enterprise compared to the native tools.
For those who are interested in attending the URL is: http://www.quest.com/events/listdetails.aspx?contentid=6732&technology=&prod=&prodfamily=&loc=4
FIRST TIME USERS: To save time before the meeting, check your system to make sure it is compatible with Microsoft Office Live Meeting: http://go.microsoft.com/fwlink/?linkid=52354
I look forward to seeing you all there.
The SoCal Code Camp was this last weekend. I had a great time attending and speaking at the code camp this time around. I hope that everyone who attended my sessions had as much fun attending the sessions as I did speaking at them. I did my best to make them as much fun and interactive as I could.
As promised here are the slide decks and sample T/SQL code from the four sessions. Everything is within a single ZIP file. I’ve included two copies of each slide deck. One in the Office 2007 format, and one in the Office 97-2003 format. They are identical to each other, but I wanted to include both so that people with the older version of Microsoft Office can see the deck without having to download and install the patch which allows Office 2003 to view Office 2007 files.
The sample scripts which I’ve included are all run against the AdventureWorks database or the AdventureWorksDW database (check the USE commands at the top of the scripts). If they need a different database they will create the new database.
Session 1 – SQL Server Query Tuning (SQL 2000+)
Session 2 – SQL Server 2008 Resource Governor (SQL 2008 CTP5+ only)
Session 3 – SQL Server Service Broker in the Real World (SQL 2005+) (I’ve fixed the problem with the single server script that we were having at the Code Camp. Turns out I had left the route in place which is why the message never showed up. The first script didn’t run correctly because I had run the server to server script on my virtual machine and the route was left by accident.)
Session 4 – SQL Server 2008 What’s on the Horizon (SQL 2008 CTP5+)
If you have any questions about these slide decks or sample code feel free to post a comment here, or drop me an email.