Transcript Installing_Third-Party_Applications

Third-Party Application Deployment
DISPATCHES FROM THE TRENCHES
John Harp
About Me: John Harp
Support Manager for SQL Server products at Idera
I’ve worked with SQL Server and SQL-based
enterprise software – and not coincidentally Idera –
since 2004
This is my first SQLSaturday presentation.
Not a SQL ‘expert’ – I’d consider myself ‘seasoned.’
About Me: John Harp
I’m also apparently a zombie cocktail waiter for-hire.
Something about goats goes here.
Non-SQL interests also include classical and
electronic music, and toddler survival skills.
Twitter: @sekainohaten
Email: [email protected]
3
The Band of Brothers
This is my team of product specialists. Without them,
this presentation would not exist.
4
Thank you Sponsors!
5
So you’ve downloaded/bought/been tasked with
deploying that third-party application….
6
What could go wrong?
• Installation environment doesn’t meet requirements
Environment meets requirements *now*, but next month, or
next year?
SQL Server complicates things
Technologies that aren’t SQL Server complicate things
There’s an outright bug that prevents installation or product
function
7
The Four Kinds of Third-Party Tools
8
IIS?
SCOM?
Driver?
Lightweight
9
•
•
•
•
•
Single install
Scaling not usually a factor
May have to install the product on
multiple servers
No/minimal metadata collection
Basic hardware requirements
•
•
Common issues
• Mostly installer or single-feature
Examples:
• Most free tools
• “Toolset tools”
Repository Driven
10
•
•
•
•
Maintains meta-data in a SQL database
Scaling is a significant factor
Install may or may not include agents
Must consider hardware requirements for
both the application and the repository
•
•
Common issues
• SQL-related install issues
• Scaling
Examples:
• Auditing/monitoring tools
• Tools for tracking change over time
Agent/Distributed
11
•
•
Usually an extension of a repositorybased design
Further distributes components to target
servers for
•
•
•
•
Security
Workload distribution
Failover/availability distribution
Additional possible points of failure
•
•
Common issues
• Complications in agent deployment
• How do you know if it’s running?
Examples:
• Backup utilities
• SCOM
• More pervasive auditing and
monitoring
Special Cases
IIS?
SCOM?
12
Driver?
•
Additional components such as
• OS-level Filter Drivers
• Internal or external web hosting
• Integration with even more thirdparty applications
•
•
Common issues
• Rely on technologies outside of the
DBA wheelhouse
• May introduce points of failure in
the OS
Examples:
• Browser applications
• Applications doing “magic”
Dispatch #1: Failure to Launch
- “Help! I got it almost installed, but it failed at the end
saying some component wouldn’t start.”
OR
- “The application installed without a hitch, but now I’ve
got these connection and communication errors.”
13
The Big Three
Permissions
14
Network Configuraiton
Software Requirements
Permissions
Remember to check permissions for *all* components.
Will a failing component run under an Administrator or Local
System account?
Double-check to see if a failing component is reporting
anything in the Windows Application or System log.
BONUS: *Minimum* permissions are not always
documented. Don’t be afraid to ask for those specifically.
15
Network Configuration
Where are the endpoints?
-
Netstat -a –b –o –n
Ports
- Can you telnet to the endpoint?
- telnet server port
- Can you connect in SSMS?
16
Software Requirements:
The minimum requirements for the operating system and
SQL Server are the one thing that you can rely on to be
documented. But….
17
Dispatch # 1B: The SQL Servers, they are a-changin’
- “I just applied the latest service pack to SQL Server, and
now the software is giving errors about identifying the
SQL Server version. This was just working!”
18
Turns out, Microsoft made a very minor change
SELECT @@version used to return the version like this
Microsoft SQL Server 2008 (SP1) 10.0.2841.0 (X64)
• In SQL Server 2012, Service Pack 2, Cumulative Update 7,
though, it’s …
Microsoft SQL Server 2012 (SP2-CU7)
(KB3072100) - 11.0.5623.0 (X64)
• Consider a test environment for SQL upgrades on your
third-party applications, too.
• Knowing the minimum and maximum supported software
versions can save you some heartache.
19
Dispatch #2: “It’s software that makes a fast machine slow.”
“It's hardware that makes a machine fast. It's software that
makes a fast machine slow.”
- Craig Bruce
20
- “I love this product, but lately it’s been so slow.”
Repository Scaling
• How big is your data going to get?
Secondary symptoms of poor scaling:
- T-SQL timeouts due to frequent autogrowth
- Exhaustion of disk space
21
How do you test scaling?
Don’t hesitate to ask the vendor if they have a white paper
on scaling.
Having an application test environment that mirrors your
live environment comes in really handy.
22
If you can get some values for how much growth occurs per
managed server, per week, you can make some basic
estimates.
How do you test scaling?
You can configure the Management Data Warehouse and
get collection set reports on disk usage
https://technet.microsoft.com/enus/library/bb933864%28v=sql.105%29.aspx
https://technet.microsoft.com/enus/library/cc280506%28v=sql.105%29.aspx
Indicates growth of files, which may not help if you’ve
already configured autogrowth
23
How do you test scaling?
• Lighter options
- Query sys.dm_db_file_space_usage via SQL Agent job
- Or sp_spaceused
24
- If the application itself is a solution for database
monitoring, let it do that work for you.
Don’t forget about ‘grooming’ features
‘Grooming’ and ‘archiving’ features are intended to manage
scaling
Questions to ask:
- Does the application have data grooming for historical
data?
- How is it configured?
- How do you know it’s working? (Is there reporting or
error handling specific to grooming?)
25
Knowing is only half the battle
• Ensure that sufficient disk space exists to accommodate the
expected size of the files
• Configure autogrow for data and log files
- Once you know how quickly the data is growing, you’ll
want to resize the files to avoid overly-frequent growth
- Set autogrowth as a contingency for unexpected
database growth
• Keep an eye on tempdb when testing scaling, too
- If you haven’t customized files and sizing for tempdb to
accommodate other applications, consider SQL Server
best practices and watch its growth.
26
Dispatch #3: Can I tweak it?
- “I found a great way to get your report to run in less than
a second where it was taking at least 15 seconds for
me.”
27
OR
- “Does your database really have to be in this recovery
mode/collation/use a single filegroup?”
Dispatch #3: Can I tweak it?
• Database recovery mode
- Affects what kinds of backups you can do
- May be set to SIMPLE to avoid log file growth
- Ask the vendor: “Can it be changed?”
• Database maintenance
- Do you need to reindex yourself?
- What’s a reasonable backup schedule?
28
Dispatch #3B: Collation Conflict
- "The console (or installer) returns the error, "Cannot
resolve the collation conflict between
“SQL_Latin1_General_CP1_CI_AS” and
“Latin1_General_CI_AS” in the equal to operation."
29
Dispatch #3B: Collation Conflict
• In the install, this could result from a mismatch of collations
between system databases
- Specifically, a situation where ‘model’ or ‘tempdb’
doesn’t match ‘master’
• More likely to appear in an application console
- Watch out for collation. It’s not necessarily the case that
a third party database’s collation is going to match the
server’s.
- Be sure to contact the vendor before you try to change
this.
30
Performance Tweaks
Depending on disk structure some tables may make sense
to put into different filegroups
This is *usually* transparent to install and upgrade
operations, but check with your vendor
So, what about adding indexes/stored procedures?
31
Dispatch #4: The unexpected database object
- “I had the application running like a dream, and then
when I upgraded, performance tanked on me.”
OR
32
- “I can’t finish an upgrade. Every time I try get, "Error
executing SQL script database.sql. Line 333. The
statistics [….] is dependent on column [….]”
Dispatch # 4: The unexpected database object
• So you customized the database with a stored procedure or
•
index or statistics
- Why?
- Additional reporting on metadata
- Enhance either same reporting or general table
performance
- Maybe the Database Tuning Engine Advisor did it?
The problem: It’s pretty common for the metadata database
to be upgraded or changed during a product upgrade
- What if the upgrade doesn’t know about your object,
and re-creates the table?
- Object is generally gone and has to be re-created
- What if the upgrade modifies the table structure?
33
Dispatch # 4: The unexpected database object
• Keep track of any database customizations you do on a
third-party product, because the vendor probably doesn’t
know about them.
• Use the object catalog view sys.stats in the case of the
second error to identify the statistics and where they came
from
34
Dispatch #5: Enormous errors at the last minute
- "When it gets to the database creation step, the install
just fails with, " INSERT failed because the following
SET options have incorrect settings:
'ANSI_WARNINGS'."
35
Dispatch #5: Enormous errors at the last minute
Normally, an “ANSI Warnings” error means that we need to
go back and consider the SET options specified in object
creation, but…
Have you looked at your ‘model’ database lately?
Most installs are going to do a CREATE DATABASE, which
means that objects in ‘model’ are going to be created in the
third party database.
Triggers (sys.triggers)
Indexes (sys.indexes)
-
36
Dispatch #5: Funny, it worked the first time…
- “I’m reinstalling the application, and even though it
worked the first time, now I’m getting, “Cannot create
file….”
37
Dispatch #5: Funny, it worked the first time…
Two errors:
- “CREATE FILE encountered operating system error
5(Access is denied) while attempting to open or create
the physical file [PATH].”
- “Cannot create file [PATH] because it already exists.”
Second error is more common, and tells us what is really
going on
“Access Denied” could result from a restore attempt
(migration)
Files could be associated with another local instance
I’ve also seen this in a situation where someone pre-created
the database, and our installer tries to create the same file.
38
Dispatch #6: Weak ephemeral Diffie-Hellman public key
OR
39
Dispatch #6: Weak ephemeral Diffie-Hellman public key
You’ll run across situations where the error is pretty clearly
not handled by
- SQL Server
- The OS
- The third-party application
You can Google it for information overload
You might have a local expert
Don’t forget that the vendor can be your first line of defence
even in cases like these
40
Dispatch #6: Weak ephemeral Diffie-Hellman public key
Firefox:
- Navigate to about:config
- security.ssl3.dhe_rsa_aes_128_sha = false
- security.ssl3.dhe_rsa_aes_256_sha = false
Chrome:
- "…\chrome.exe" --cipher-suiteblacklist=0x0088,0x0087,0x0039,0x0038,0x0
044,0x0045,0x0066,0x0032,0x0033,0x0016,0x
0013
- Create shortcut
41
QUESTIONS?
THANK YOU!
42
- John Harp
@sekainohaten
[email protected]