Post moved to:
schema-compare-multiple-databases-at-once
Thanks!
8/15/2016
Min and Max Server Memory in English
This post has been moved to:
http://dallasdbas.com/min-and-max-server-memory-in-english/
Thanks,
Kevin3NF
http://dallasdbas.com/min-and-max-server-memory-in-english/
Thanks,
Kevin3NF
8/14/2016
8/11/2016
Relax...its ok...
Sometimes its good to sit back, listen, nod and hear what is being said before speaking. Actually, that is almost always the best idea.
Case in point:
I am the team lead here (small team of 3...SQL, Windows and storage admins...we overlap.).
I cam back from lunch yesterday and one of my guys very passionately launched into "We need to have a meeting!", "The developers want too many deployments!", "We need change management!", etc.
All of his points were true. This is a small team with very few procedures and practises, and our job is to get a handle on this. We are also at the end of the development process for v1.0 of an internal application...which is being demonstrated today. Not the best time to suddenly change things.
So I listened while he made his case, agreed with most of what he said and asked some questions when he was done:
1. What problem are you trying to solve by forcing change windows today that don't exist?
2. How many "deployments" are we being asked to do each day? (A deployment here could simply be ALTERing a stored proc, and the target is a Pre-Production database)
3. Should we be focusing on the other issues here we have already identified? Where does this rank in the list? (Backups, security, perf, etc. all rank higher and are more actionable)
What it boiled down to is that we don't really have a problem...he just got hit with three requests in a short time frame, due to the upcoming demo to the executive staff.
We get maybe 2 requests a day from the Devs, and have 3 people capable of deploying them. At this time, on this project...all a forced window will do is alienate 10 of the 15 team members. Yes, it is a good idea, but lets phase it in for better acceptance, when the team is not under the gun. Production release is only a month away...
Sometimes its best to relax, look at the bigger picture and make the best decision for the team.
Imma buy this guy lunch, with fries :)
Kevin3NF
8/10/2016
Refreshing SQL Server development databases
Refreshing the Dev environment
I started a new contract recently. This is a global company, but I am on a relatively small product team within it. 10 developers, 3 admins (SQL, O/S, Storage), 2 Business Analysts.
The company has a formal Change Management process, which is great. However, this team manages and operates outside of that structure...not so good. Devs have sysadmin to production. For now.
I and the other DBAs are working on all of the things you would expect to be missing from this sort of environment...consistent backup strategy, documentation, proper security, etc.
As with most places our Developers have a Dev copy of the database and are told not to touch the Pre-Prod version. But, since we are test loading data there, Dev is way out of date. Also, there are objects in Dev not moved to Pre-Prod yet. So, they regularly point their queries, procedures and even the website to the PP database. And then forget what they are connected to after lunch.
This makes for interesting hallway conversations!
The solution of course is to refresh the Dev db from Pre-prod, without losing any Dev code or users.
DBAs that have been around for awhile know where I am going with this...
Basic steps:
1. Backup the Production (or Pre-prod in my case) database. Or pull from your overnight backup. You DO have an overnight backup, right? RIGHT?!?!? ;)
2. Restore that backup to the Dev server as MyDB_Refresh:
-- Get the logical and physical filenames
Change the database names if you need to do the same on MyDB_Refresh. You can use the same offline/online code when you are doing an emergency restore in prod and the app servers won't let go of their connections.
Compare a select of tables between Prod and Dev to make sure the data is refreshed, and double check using schema compare that Dev is different than Prod (likely, since that is what Devs do...).
This is a basic process, best done in a maintenance window, early in the morning or late at night. And you can automate most of this. I have not tried to automate schema compares other than using SQLPackage.exe in the SSDTools to "upgrade" a target data-tier application, or create a Drift report. But that is way beyond the scope of this post. Maybe another time :)
Please feel free to post up your processes, or enhancements to this one that some new DBA or Windows admin may be able to use 3 years from now when he finds this post on Google.
Have a fantastic day!
Kevin3NF
I started a new contract recently. This is a global company, but I am on a relatively small product team within it. 10 developers, 3 admins (SQL, O/S, Storage), 2 Business Analysts.
The company has a formal Change Management process, which is great. However, this team manages and operates outside of that structure...not so good. Devs have sysadmin to production. For now.
I and the other DBAs are working on all of the things you would expect to be missing from this sort of environment...consistent backup strategy, documentation, proper security, etc.
As with most places our Developers have a Dev copy of the database and are told not to touch the Pre-Prod version. But, since we are test loading data there, Dev is way out of date. Also, there are objects in Dev not moved to Pre-Prod yet. So, they regularly point their queries, procedures and even the website to the PP database. And then forget what they are connected to after lunch.
This makes for interesting hallway conversations!
The solution of course is to refresh the Dev db from Pre-prod, without losing any Dev code or users.
DBAs that have been around for awhile know where I am going with this...
Basic steps:
1. Backup the Production (or Pre-prod in my case) database. Or pull from your overnight backup. You DO have an overnight backup, right? RIGHT?!?!? ;)
2. Restore that backup to the Dev server as MyDB_Refresh:
-- Get the logical and physical filenames
Restore filelistonly
From disk ='B:\Backup\FULL\MyDB_FULL_20160810_000012.bak'
---Restore, not overwriting the
existing Dev db
Restore Database MyDB_Refresh
From disk ='B:\Backup\FULL\MyDB_FULL_20160810_000012.bak'
With move 'MyDB' to
'B:\Data\MyDB_Refresh.mdf'
,move 'MyBD_log' to
'B:\Log\MyDB_Refresh_log.ldf'
,recovery
3. Use SQL Server Data Tools/Visual Studio, or a 3rd party tool such as Red Gate SQL Compare to compare and synchronize the schema in MyDB (Dev copy) to MyDB_Refresh. SSDT works well for this if you don't have a license for a vendor tool, or don't want to download a trial version. The Refresh database is the Target...
4. Once you have the tables, procedures, USERS, and other objects exactly the same as the old Dev database, you need to rename both databases:
--Make sure all connections are closed:
Use master;
Go
exec Sp_renameDB 'MyDB', 'MyDB_Old'
exec sp_renameDB 'MyDB_Refresh', 'MyDB'
If you are having trouble getting exclusive use because Devs or apps keep re-connecting, you need to use a 2-step process (run both at the same time):
--Take the database offline and kill
connections
Alter database MyDB Set offline
with rollback immediate
--Bring it back online and rename all
at once
Alter database MyDB set online
use master;
go
Sp_renamedb 'MyDB', 'MyDB_Old'
Compare a select of tables between Prod and Dev to make sure the data is refreshed, and double check using schema compare that Dev is different than Prod (likely, since that is what Devs do...).
This is a basic process, best done in a maintenance window, early in the morning or late at night. And you can automate most of this. I have not tried to automate schema compares other than using SQLPackage.exe in the SSDTools to "upgrade" a target data-tier application, or create a Drift report. But that is way beyond the scope of this post. Maybe another time :)
Please feel free to post up your processes, or enhancements to this one that some new DBA or Windows admin may be able to use 3 years from now when he finds this post on Google.
Have a fantastic day!
Kevin3NF
8/09/2016
8/04/2016
Login Failed for user...
This post has been moved to my site:
http://dallasdbas.com/login-failed-for-user/
Thanks for visiting!
http://dallasdbas.com/login-failed-for-user/
Thanks for visiting!
6/01/2016
SQL Server 2016 Stretch Database
New to SQL 2016 is the ability to send archived data off-premises to MS Azure storage, in the form of a "Stretch Database." Sounds like a great idea in theory but do your testing and bust out the calculators before you put production data in the cloud.
Things I like:
- Not buying storage, especially on a maxed out server
- Easy to query full dataset across on-prem and Azure stretch
- Ummm...all editions is a good thing instead of Enterprise only
- Nope. That's it.
Not fond of:
- "You are billed for each hour the Stretch database exists, regardless of activity or if the database exists for less than an hour, using the highest rate that applied during the hour."
- Lowest performance rate is $1.25/hr or just under $1K/mo. Only goes up from there
- "Stretch Database currently does not support stretching to another SQL Server. " Azure only
- Lame/minimal filters...you have to roll your own functions, and they must be deterministic...no "Getdate() - 30". This GUI is only slightly better than the horrible nightmare that was Notification Services...
I'm pretty sure I could roll my own "stretch" function into a Azure SQL Database, and I'm an admin much more than a developer.
Maybe down the road this will be better, but right now its an expensive alternative to a USB drive from Fry's, or a NAS/SAN upgrade.
Are/were you planning to use Stretch? Have a differing opinion? Let's hear it!
Kevin3NF
2/19/2016
Dudes...really?
I was participating in an email interview about my SQL career and opinions and one of the questions was basically "What's the worst SQL thing you've inherited?"
I gave them this list:
o SQL
2008 R2, RTM
o Incorrect
Memory configuration
o Full
recovery model on data that only changes once a week at most
o ZERO
documentation
o New
data is imported to a new table and a UNION ALL is modified to add that table
o ZERO
documentation
o Stored
Procedures have no comments, poor formatting and developer names in the name of
the sproc
o Autogrow
is 1MB, data imports are hundreds of MB each
o Everyone
is sysadmin, probably including you…
o Change
control process is intentionally shortcut and ignored on this internal
production system
o Ownership
changed to me in December, then was yanked back 3 weeks later with developers
overwriting my fixes in prod.
Really....all that on one server!
Upgrade your fries broseph!!!
Kevin3NF
10/30/2015
SQL 101 Blocking vs. Deadlocking - in English for the Non-DBA
This post has been moved to:
http://dallasdbas.com/sql-101-blocking-vs-deadlocking-in-english-for-the-non-dba/
Thanks,
Kevin3NF
http://dallasdbas.com/sql-101-blocking-vs-deadlocking-in-english-for-the-non-dba/
Thanks,
Kevin3NF
10/26/2015
2/11/2015
Unsupportable...thats what you are....
With apologies to Nat King Cole :)
Database backup failing
Write on "D:\backup\MyDatabase\MyDatabase_20150208104103883_D.bak" failed: 665(The requested operation could not be completed due to a file system limitation) [SQLSTATE 42000] (Error 3202)
Hey, customer...let not put backups on O/S compressed drives please.
Oh...and why do you have your MDF/LDFs on the same drive...also compressed?
Resolution - punt it back to them and tell them SQL Server on compressed drives is unsupported config by Microsoft and by me. Uncompress and expand or add a drive and uncompress.
Sheesh. How many worst practices can you get in one server?
Database backup failing
Write on "D:\backup\MyDatabase\MyDatabase_20150208104103883_D.bak" failed: 665(The requested operation could not be completed due to a file system limitation) [SQLSTATE 42000] (Error 3202)
Hey, customer...let not put backups on O/S compressed drives please.
Oh...and why do you have your MDF/LDFs on the same drive...also compressed?
Resolution - punt it back to them and tell them SQL Server on compressed drives is unsupported config by Microsoft and by me. Uncompress and expand or add a drive and uncompress.
Sheesh. How many worst practices can you get in one server?
5/03/2013
Monitor for blockings...
Short and sweet...
Ticket for error 1105. Teammate response: No blockings on server, monitor 4 days.
Ticket for error 601. Teammate response: No blockings on server, monitor 4 days.
Ticket for login failed. Teammate response: No blockings on server, monitor 4 days.
I. Kid. You. Not.
Someone needs to be fired. Or congratulated for making me look like a SQL SERVER GENIUS!!!! by comparison.
That is all.
Kevin3NF
Ticket for error 1105. Teammate response: No blockings on server, monitor 4 days.
Ticket for error 601. Teammate response: No blockings on server, monitor 4 days.
Ticket for login failed. Teammate response: No blockings on server, monitor 4 days.
I. Kid. You. Not.
Someone needs to be fired. Or congratulated for making me look like a SQL SERVER GENIUS!!!! by comparison.
That is all.
Kevin3NF
6/07/2011
4/15/2009
3/11/2009
When to Modify Somebody Else’s Code
I was tagged by Brent Ozar on this question that has been floating aroung the Tweeterverse and blogosphere the last few days...
In keeping with the short-but-useful-or-amusing style of this blog:
When you have an OK in writing from the vendor or internal group that wrote it AND from the supervisor you got the approval from on your side.
Yeah...make darn sure your suggestion actually works before you even ask, do backups, keep scripts, etc.
Else you get no fries. You get fried. :)
Kevin3NF
Tag: Lee Everest
I was tagged by Brent Ozar on this question that has been floating aroung the Tweeterverse and blogosphere the last few days...
In keeping with the short-but-useful-or-amusing style of this blog:
When you have an OK in writing from the vendor or internal group that wrote it AND from the supervisor you got the approval from on your side.
Yeah...make darn sure your suggestion actually works before you even ask, do backups, keep scripts, etc.
Else you get no fries. You get fried. :)
Kevin3NF
Tag: Lee Everest
1/29/2009
You did what?
This one comes from inside....
We got an alert that the Log Shipping was out of sync for a customer. Our front-line guy looked and saw that the LS Restore job was still running after 35 minutes when it normally takes less than 30 seconds.
He decided it was hung and cancelled it. Restarting the job caused immediate errors.
Why? The restore was working on a 1.4 gb t-log backup file, instead of the normal 4-5mb. Cancelling it puts the database in a suspect state. had to drop the db and re-initialize from a full database backup.
Ugh. Glad it wasn't any bigger than 7gb...since the file/restore had to move halfway across the country.
No fries for you "M from M"...you owe me some!
Disclaimer: this is a pretty sharp guy and I'm just harassing him here...
Kevin3NF
This one comes from inside....
We got an alert that the Log Shipping was out of sync for a customer. Our front-line guy looked and saw that the LS Restore job was still running after 35 minutes when it normally takes less than 30 seconds.
He decided it was hung and cancelled it. Restarting the job caused immediate errors.
Why? The restore was working on a 1.4 gb t-log backup file, instead of the normal 4-5mb. Cancelling it puts the database in a suspect state. had to drop the db and re-initialize from a full database backup.
Ugh. Glad it wasn't any bigger than 7gb...since the file/restore had to move halfway across the country.
No fries for you "M from M"...you owe me some!
Disclaimer: this is a pretty sharp guy and I'm just harassing him here...
Kevin3NF
1/15/2009
North Texas SQL Server User group
I live, work and play in the DFW area and used to be a regular attendee at the local user group monthly meeting. I was even a board member. The topics became increasingly over my head or out of my area of daily need, so I started slacking and eventually quit going altogether.
No more!
Tonight I start the first of 5 or 6 months of free Power Shell beginner classes that happen before the SSUG meeting starts, taught by a local expert. I don't even know what Power Shell is...some sort of scripting language is all I gather.
In addition, my co-worker Trevor Barkhouse is the presenter this evening. He will cover "Refactoring T-SQL Code for Better Performance". I've seen him do this here in the office to help a couple of our problem customers resolve nagging issues that were frustrating them.
More about NTSSUG
Kevin3NF
I live, work and play in the DFW area and used to be a regular attendee at the local user group monthly meeting. I was even a board member. The topics became increasingly over my head or out of my area of daily need, so I started slacking and eventually quit going altogether.
No more!
Tonight I start the first of 5 or 6 months of free Power Shell beginner classes that happen before the SSUG meeting starts, taught by a local expert. I don't even know what Power Shell is...some sort of scripting language is all I gather.
In addition, my co-worker Trevor Barkhouse is the presenter this evening. He will cover "Refactoring T-SQL Code for Better Performance". I've seen him do this here in the office to help a couple of our problem customers resolve nagging issues that were frustrating them.
More about NTSSUG
Kevin3NF
12/16/2008
A series of conversations from several days...compacted into one nightmare. Enjoy:
Front line dude: Acme has a drive space issue...can I shrink the log file?
Kevin3NF: Give it a shot (this is SOP for Acme on this drive)
FLD: Didn't work
Kevin3NF: Give me the ticket, I'll take a look.
Kevin3NF: Hey ACME developer...you have a 2 day old transaction taking up all your T-log space...
Acme: ok...let me truncate the log
Kevin3NF: WAIT A SECOND!
Acme: Did that help?
Kevin3NF: No. You just invalidated the T-log backup stream and started causing the backup to fail
Acme: Why?
Kevin3NF: Cuz that big open transaction is still there, and SQL Server thinks there is no full backup now.
Kevin3NF: Hello?
Kevin3NF: (Sees pictures of crickets in his Inbox)
Acme: (hours later): go ahead and run a full backup tomorrow afternoon
Kevin3NF: You know you don't have a valid backup to recover to, right?
Acme: Yes, but we can't backup now...
(tomorrow afternoon):
Acme: Hey...who told you to run a full backup?!?!?
Kevin3NF: You did...I have the email.
Acme: (complaint to Kevin3NF's bosses)
Time passes...repeat scenario.
Big transaction, full log file, fill drive, lather, rinse, repeat.
Customer: FAIL. >:(
Acme me owes ME some fries...but I get paid to deal with stuff like this every day :)
Kevin3NF
Front line dude: Acme has a drive space issue...can I shrink the log file?
Kevin3NF: Give it a shot (this is SOP for Acme on this drive)
FLD: Didn't work
Kevin3NF: Give me the ticket, I'll take a look.
Kevin3NF: Hey ACME developer...you have a 2 day old transaction taking up all your T-log space...
Acme: ok...let me truncate the log
Kevin3NF: WAIT A SECOND!
Acme: Did that help?
Kevin3NF: No. You just invalidated the T-log backup stream and started causing the backup to fail
Acme: Why?
Kevin3NF: Cuz that big open transaction is still there, and SQL Server thinks there is no full backup now.
Kevin3NF: Hello?
Kevin3NF: (Sees pictures of crickets in his Inbox)
Acme: (hours later): go ahead and run a full backup tomorrow afternoon
Kevin3NF: You know you don't have a valid backup to recover to, right?
Acme: Yes, but we can't backup now...
(tomorrow afternoon):
Acme: Hey...who told you to run a full backup?!?!?
Kevin3NF: You did...I have the email.
Acme: (complaint to Kevin3NF's bosses)
Time passes...repeat scenario.
Big transaction, full log file, fill drive, lather, rinse, repeat.
Customer: FAIL. >:(
Acme me owes ME some fries...but I get paid to deal with stuff like this every day :)
Kevin3NF
Subscribe to:
Posts (Atom)