I regularly deliver a presentation called “Mastering TempDB”. I wrote it based on what I wish I knew about TempDB “growing up” in my career as a SQL Server DBA. It really is a breakdown of what TempDB is, how it is used, what problems actually occur within it, and how to best configure it. With advice from my colleagues, I have decided to break that down into this article to educate you.
What is TempDB?
TempDB is a global resource used by everyone and everything inside SQL Server. Think of it as a dumping ground for anything that doesn’t fit in memory. It is always database_id number two in sys.databases.
It’s non-durable, meaning that the database is recreated every time SQL Service restarts. A new set of data and log files are recreated each time. So, what does this mean to you? First of all, you shouldn’t put any objects in the TempDB database that you need to be persisted. Don’t use TempDB as a development database (except for maybe a quick throwaway test), because once services restart, you will lose your work.
TempDB works in a round-robin fashion known as proportional fill; it writes data in all files based on free space in each file and alternates between files to find the best place to do its work. I’ll talk more about this later in the article…..
Read the full article here at Red-Gate’s Simple Talk
The post Mastering TempDB: The Basics appeared first on A Shot of SQLEspresso.
Contact the Author | Contact DCAC
Unequivocally, yes on-premises SQL Server Instances are still relevant.
While I’m a firm believer that the cloud is not a fad and is not going away, it’s just an extension of a tool that we are already familiar with. The Microsoft marketing slogan is “It’s just SQL” and for the most part that is indeed true. However, that does not mean that every workload will benefit from being in the cloud. There are scenarios where it does not make sense to move things to the cloud so let’s take a look at a few of them.
The cloud can cost a lot
There is no such thing as a free lunch and the cloud is not excluded. I am sure that we’ve all heard horror stories of individuals leaving resources active which in turned costed large sums of money. While the cloud offers up a wide range of capabilities in aiding the day-to-day life of IT professionals everywhere, it might not be cost effective for your given workload or data volumes. Compute resources and all things associated with that cost money. If you need higher CPU, more money. If you need terabytes of storage, more money. If you need a higher CPU to memory ratio for that virtual machine, more money. All of the resources the cloud offers you essential rent and the bigger the space, the more money it takes. Of course, all of this is dependent on your organizational requirements and associated workloads.
By having an on-premises environment you can implement a lower cost of ownership for hardware. This being said, the cloud offers up more efficient means of upgrade and scaling which is usually limited with on-premises ecosystems which can actually save you money. It’s a trade-off that organizations have to weigh to see if moving to the cloud makes sense.
You want control of all things
Most things in the cloud require that organizations relinquish control. That is just a plain fact and that’s not changing. We are trading speed and agility from an infrastructure perspective for a lower ability to control certain aspects of the architecture. For example, with Azure SQL Database (Platform as a Service), database administrators no longer can control database backup method or frequency. In exchange for this loss of control, though, backups are taken automatically for us. In my opinion, this is a more than fair exchange and I sleep better knowing that a tried and vetted backup process is taking care of things without my intervention.
You have specific compliance or regulation requirements
While most of the players in the public cloud space (Azure, Amazon, Google) are all certified for a multitude of compliance regulations, it’s possible that you have a very specific one that the provider is unable to meet. If this is the case, then your ability to move to the cloud is limited and you are forced to remain on-premises. Regulations could also impose issues when moving to that cloud. These regulations could be imposed by the governing body of the organization or be sourced from various places. If this is the case, it’s possible that the cloud is not a viable solution for your organization.
I do suspect that as cloud technology continues to advance, regulations and compliances will slowly be brought into the fold and allow for appropriate cloud implementations.
You do not have the expertise
Put simply, you do not have the knowledge internally to successfully migrate to the cloud nor do you have the budget to hire someone to move you to the cloud. Shameless plug, this one of our core competencies here at Denny Cherry & Associates Consulting. We help organizations (big or small) get into the cloud to help push their data ecosystem forward. However, not every organization can afford to hire consultants (short or long term) to help them with such a project. In this instance, until you can get the expertise to help you are left with either staying on-premises or trying to figure it out on your own. In some respects, the cloud opens new security exposures that must be accounted for when moving to it. If these are not accounted for the organization severe issues could arise so I recommend not going down the “we’ll figure it out as we go” method without some level of guidance.
Your workloads do not perform in the cloud
Even though I am a huge fan of Azure, some workloads just won’t perform well unless you break out your wallet (see the first paragraph). Even with proper performance tuning, the performance comparison between on-premises and the cloud is not going to be a true apples to apples comparison. The infrastructure is just too vastly different to really get that “exact” level of comparison. Organizations must find that sweet spot between performance infrastructure costs and frankly, sometimes that sweet spot dictates remaining with on-premises hardware.
There are probably many other reasons why on-premises infrastructures will continue to be relevant. Each organization may have unique requirements that having SQL Server on their own hardware is the only solution. Remember, regardless of where you deploy SQL Server, it is just SQL and it’ll behave the same (mostly). This does not mean that you should not continue to expand your skill sets. Make sure to continue to learn about cloud technologies so that when your organization is ready to make the leap, you can do so in a safe and secure manner.
© 2020, John Morehouse. All rights reserved.
The post Is On-premises SQL Server Still Relevant? first appeared on John Morehouse. Contact the Author | Contact DCAC
Does your server look like this?
Many of us have inherited a SQL Server instance that has all SQL Services installed. Someone, maybe even you, went through the SQL Server installation process using GUI and checked every option available to them, then just clicked Next, Next, Next and then Install. If this is your environment, please take a moment to evaluate and decide which of these services that are required.
From a performance tuning perspective, it is important to only run the services that you need. Each of these services can consume resources on your server. Sharing resources reduces what you SQL Server Engine, SQL Server (MSSQLSERVER) or named instance SQL Server (ServerName\NamedInstance) can consume to run your workload efficiently. It is highly recommended that all other services not associated with the engine be run on a separate server.
Sometimes due to licensing concerns, this not a viable solution, and that is understandable. However be sure you are knowledgeable about how each configuration setting, like memory, that may need to be adjusted with other services sharing these resources. As I stated in a prior blog the max memory set inside SQL Server does not impact other services like SSIS, SSAS or SSRS. Those services take their memory from the operating system allocation, which in turn can take memory from the SQL Server engine. Be sure to allocate enough to each service. (Note: you may end up needing to spend more in RAM, than licensing, especially if you can run standard edition for BI tools like SSRS or SSIS).
Take a moment and look to see what services you are running versus what you are using. If you find that SSIS, for example, is running and you are not actually using it in your environment turn off the services. Take the time to set it to DISABLED and not just STOP the service. Stopping the service isn’t enough if it is set to start automatically. When the server is rebooted, the service will diligently start, and you do not want that. Make sure to disable the service to prevent that from occurring.
Contact the Author | Contact DCAC
Bob Pusateri (B|T) tweeted a quote image that really struck a chord with me and elicited a strong reaction from myself. It got me thinking I need to write a blog on this, so here we go.
Many times, over the years I’ve had conversations with people in which they have said things like below.
“I can’t learn anything new because my work won’t send me to training”
“I can’t learn about xyz because my boss or coworkers won’t sit down with me to show me how”
“I don’t have time to learn anything new”
“We’ll never go to the cloud, so I don’t need to know that”
“I’m too busy just putting out fires, I can’t leave the office to go to training”
“I can’t learn anything new because my work won’t pay for training”
“My free time is my own. When I’m not at work, I don’t want to think about databases”
“I’m too burned out, to learn anything new”
“It’s just not a priority for me right now”
If you can hear yourself saying any of these, I want you to stop right here and reread the image above several times. If you make excuses like these, you will get nowhere fast and likely become disgruntled which can lead to feeling trapped. If you wish to have a career and not just a 9-5 job it is critical you invest in your own training.
Now many of us don’t have the money to pay for things like week-long conferences or expensive hands-on training, I get that. There are other ways to get training, it’s not necessary to set your target on those. Start small. I challenge you to read one blog a day, that’s it. By doing that simple thing you vastly increase your knowledge. I’ll list a few of my favorites at the end of the blog.
Next, be sure to attend your local user groups, Code Camps, or SQL Saturdays. These are usually free to attend in-person training opportunities on various topics, not to mention it’s your chance to network with other technology professionals. At these events, talk and engage people, as you never know when one of those conversations may lead to your next career opportunity. That’s a win-win. An added benefit is that usually these types of events provide you with motivation to further your own professional development. You’ll be surprised on how they will affect your outlook on work and learning. If you can’t make it out of the office or home for training, did you know there are many virtual training opportunities available like PASS’s virtual user groups ? These are live training sessions, much like those at SQL Saturdays, given during lunch hours or after work. They are also often recorded so you can watch them when its more convenient.
Lastly, do yourself a favor and join Twitter. So many of us in the SQL Community are out there learning from each other every day. Bloggers tend to leave breadcrumbs for learning.
We post links to our newest blogs.
We note things like Currently Reading: xyz Blog Topic with a link to that blog.
Follow the #SQLHELP hashtag. We are all out there helping each other trouble shoot issues and providing references to answers. This is a fantastic place not only to find help, but to learn.
These are just a very few things you can easily do to start investing in yourself and your training. Below I have listed sites for free or low-cost training, links to great blogs you may want to start with. However, you start… the point is to START. Stop complaining and making excuses and just go for it.
I started out working at the Port of Virginia with ZERO SQL Server Database Admin experience straight out of college. They sent me to a SQL Server bootcamp, (the only training I EVER got from them in 12 years) in which after, I took my certification exams and I was off and running with no more training. As we know, when you attend training like that, it’s really just a ton of topics thrown at you, an exam is taken, and nothing is really digested. In order to be successful and keep the Port’s databases running as their only DBA, I had to invest in myself and my own training. If it wasn’t for me taking the time to self-train I would not be where I am today and my tenure at the Port would have been disastrous.
SQLPass – pass.org
SSWUG – https://www.sswug.org/
LinkedIn Learning- https://www.linkedin.com/learning/
Microsoft Virtual Academy – https://www.microsoft.com/en-us/learning/training.aspx
MS SQL Tips – MSSQLTips.com
SQL Server Central- https://www.sqlservercentral.com/ (stairways series are good for beginners)
Denny Cherry and Associates Consulting- https://www.dcac.com/publications/blog
SQL Skills- https://www.sqlskills.com/sql-server-training/online-training/
MS SQL Tips- https://www.mssqltips.com/
Simple Talk- https://www.red-gate.com/simple-talk/
SQL Performance- https://sqlperformance.com/
C-SharpCorner – https://www.c-sharpcorner.com/
Each day I learn something new, I take the time to invest in my knowledge and career. No one has to tell me to do this. I want more out of a job, I want a career, so I make one. As the quote above say, I am unstoppable, there is no reason you can’t be too. I understand that this is a personal choice and it is perfectly fine not to want this, but don’t complain while doing nothing. While there are exceptions to the rule, most companies don’t care about your career trajectory. HR is there to protect the company from lawsuits, not to help you. You are the only one responsible for managing your skills and career, and if your company isn’t investing in your training, you need to do it yourself.
Contact the Author | Contact DCAC