Database Administration

Database Tricks that Every Developer and DBA MUST Know

Wednesday, October 14, 2015
10:00am Pacific / 12:00pm Central / 1:00pm Eastern

It is not unusual for a server to perform poorly causing developers and system administrators to scratch their heads while attempting to troubleshoot. Regardless of how great the hardware specifications, there can be many other issues that can cause the server to responds slower than expected. 

Join SQL evangelist Pinal Dave and the Director of Software Consultants at Embarcadero Scott Walz as they provide tips and techniques to help DBAs and Developers manage and maintain their servers at optimal performance.

From this session you will learn:

• How to improve database server implementation
• Tips and techniques on improving server performance
• How to avoid database performance problems before they happen

DBArtisan and Rapid SQL 2016 are here! -- Join the Revolution! DBArtisan & Rapid SQL 2016 are available today with a new, collaborative, team-connected environment and a revolutionary new performance solution called Performance IQ that you must see. Put an end to siloed database development and administration Historically, DBAs and developers have worked in highly siloed environments which don't facilitate shared knowledge and resources. With new DB Team Server in DBArtisan and Rapid SQL 2016, you get the shared resources, analytics communication and collaboration links that have been completely in database tools - an industry first! Introducing Performance IQ Other DBMS performance products provide comprehensive metrics on database performance. However simply showing metrics is no longer good enough. NEW Performance IQ provides a deep investigative probe into the underlying causes of the performance degradation and visually highlights the root causes. Alerts and hints are integrated seamlessly across DB Artisan and Rapid SQL features that you already use. Join the Revolution and Get Started Today Download DBArtisan 2016 Download Rapid SQL 2016 Key Enhancements for DBArtisan and Rapid SQL 2016 with Performance IQ: Embedded Performance Metrics & Analytics SQL Analytics 24/7 Wait-based Monitoring & Alerting Streamlined Communication and Knowledge Sharing with DB Team Server Register for the launch webinar on Monday, 28 September 2015  08:00 San Francisco | 11:00 New York | 16:00 London | 17:00 Berlin | 18:00 Istanbul to learn more and see the new features for this release! Embarcadero Technologies' DB PowerStudio and ER/Studio are industry-leading database management and modeling tool suites that help thousands of organizations and database professionals effectively manage their data across the entire ecosystem, from modeling and management to development and optimization.

See What's New in 
DBArtisan and Rapid SQL 2016

Monday, September 28, 2015
8:00am Pacific / 10:00am Central / 11:00am Eastern

Embarcadero continues to innovate with the latest game-changing release of DB PowerStudio 2016 including DBArtisan, Rapid SQL, DB Optimizer and Change Manager. These products will take performance management to the next level.

Register to attend this webinar to learn:

• About the new features being included in the launch
• About the solutions to challenges that data professionals struggle with
• How to join the Embarcadero Data Revolution

Register now!

 

Here are 10 quick SQL Server tips and tricks for DBAs compiled by SQL Server expert Pinal Dave.

1. RAISERROR in the format RAISERROR integer 'string' is discontinued from SQL Serevr 2012.
2. SQL Server should be behind the firewall not exposed to Internet.
3. We can check the network packet size, by querying the system catalog view sys.confiurations.
4. Audited events can be written to the audit log by using the new sp_audit_write procedure.
5. SQL Server error logs can reveal a great deal of information about your server.
6. Use "allow only encrypted connections" only if needed for end-to-end encryption of sensitive sessions.
7. Enable only the optional features that you will immediately use.
8. The tcp/ip packet consists of a header that is at least 20 bytes in size.
9. The header of the ping packet is 28 bytes plus the size of the buffr you specify.
10. In the event of a system crash, indirect checkpoints provide potentially faster, more predictable recovery time.

Try the premier cross-platform database administration solution today

Embarcadero® DBArtisan® is the premiere database administration toolset helping DBAs maximize availability, performance and security across multiple DBMSs. This essential toolset consistently boosts productivity, streamlines routine tasks, and reduces errors.

Try DBArtisan | Learn more about DBArtisan | Contact Us

DBArtisan XE6 version 9.7.5 is now available.

Embarcadero DBArtisan is the award winning DBA tool for enterprise-wide database administration. DBArtisan empowers you to manage all of your critical databases running on current versions of IBM DB2, Oracle, PostgreSQL, Microsoft SQL Server, MySQL, Sybase and Teradata systems, from a single, consistent, user-friendly interface.

• Customers with current maintenance agreements can download the new version from the DBArtisan registered users download page
• Click here to download a free trial of DBArtisan if you aren't a current user

If your maintenance has expired, contact Embarcadero Sales. If you have installation questions, you can contact Embarcadero Support. Be sure to follow any policies your company may have on moving to new versions of the tools for production work.

Here is what is new in DBArtisan XE6/9.7.5

PostgreSQL Utilities

The following Utilities menu features are now available against PostgreSQL datasources:

• Database Monitor - provides details on locks, processes, and prepared transactions.
• Database Backup - generates a pg_dump script, backing up one or more tables or views of a database.
• Database Restore - generates a pg_restore operation, restoring data from backup.
• Data Unload - lets you generate a pgadmin export data call, dumping database data to a file.
• Data Load - lets you build and submit a COPY... FROM... call, copying data from a file to a table.
• Schema Extraction - walks you through the process of building an extraction script, executing it for storage of the database or object schema in a file.
• Schema Migration - migrates schema and table data from one DBMS platform to another.
• Schema Publication - lets you generate HTML reports documenting the details of your schema, databases, and database objects.
• Reverse Engineering - lets you analyze, control, and document existing database objects.

PostgreSQL Object Management

The following database object-based features are now available against PostgreSQL datasources:

• Object Wizards/Editors - You can now create and edit objects of the following types: check constraints, domains, exclusion constraints, foreign keys, functions, indexes, primary keys, roles, rules, schemas, tables, tablespaces, triggers, unique keys, and views.
• Object Actions - A new Refresh Materialized View action is available against views created using the CREATE MATERIALIZED VIEW statement. It lets you build and submit a REFRESH MATERIALIZED VIEW statement, to replace the contents of a materialized view. The Create Like action is now available against roles, rules, and tables.
• Datasource Explorer - A Performance node is now available in the Datasource Explorer. As with other DBMS platforms, the PostgreSQL Performance node has Locks and Processes subnodes. A Prepared Transactions subnode provides details on transactions that have been prepared for two-phase commit.

  In addition, a new Server > Logs node provides access to PostgreSQL logfiles. The associated Rotate Log action signals the log-file manager to immediately switch to use of a new output file.

PostgreSQL Updates to the SQL Editor

The Paste SQL Syntax and Paste SQL Statement features are now available against PostgreSQL datasources.

PostgreSQL Tools Menu Updates

The following Tools menu features are now available against PostgreSQL datasources:

• Database Search lets you search for objects whose DDL contains a specified character string, across multiple databases.
• Query Builder lets you construct, structure, and manipulate queries using a graphical interface.
• Data Editor lets you edit table data in real time.

Welcome Window

The Welcome Window helps customers to get information about product features, news and upcoming events. This window also includes direct links to useful DBArtisan's features like New Project, Open Project or links to Recent Datasources.

Project Management, Version Control, and Script Library

DBArtisan project management facilities act as a repository to maintain all source code for a database project. When you create a new project (File > New > Project), you can initialize the project using one of four methods:

• From an existing database
• From one or more files (SQL/query scripts, DDL files, ER/Studio files, procedures, Java, Javascript or VB Script files, or text or HTML files)
• From a version control project
• Uninitialized.

A project opens in a dedicated Project window. In addition to basic project operations (New, Open, Close), Project menu and right-click options provide basic file and folder options. Other key options let you

• Specify a build order for your project scripts
• Execute the build.

The Project menu also provides access to version control support, also new for this release of DBArtisan. DBArtisan supports any source control product that uses the MSSCCI plug-in interface. Basic version control functions are provided: Get Latest Version, Check In, Check Out, Undo Check Out, Add to/Remove From Version Control, Show History, Show Differences, Version Control Properties, Share From Version Control, and Refresh Status.

NOTE: For the 64-bit version of DBArtisan, a 32-bit version of the MSSCCI Provider can be used for version control access. The feature is controlled from the Options Editor's Version Control tab (File > Options > General > Version Control).

The Script Library (View > Script Library) provides a drag-and-drop library interface of all supported DBMS syntax, SQL syntax, built-in functions, optimizer hints, and SQL-conditional syntax. Additionally, it provides the ability to create custom folders to store commonly-used code for quick and efficient access or execution, as needed.

Datasource Registration Updates

For connectivity purposes, DBArtisan is packaged with a native driver as well as a set of one or more JDBC drivers for each DBMS platform. In previous releases, the native driver for each platform was the default connection option when registering a datasource. The Options Editor's Connection page now has a tab for each supported DBMS platform. Options on each tab introduce two enhancements:

• You can now designate one of the JDBC drivers as the default connectivity option for a DBMS platform.
• You can universally change the definition for all existing, currently disconnected datasources for a platform to use either the native driver or a JDBC driver as the connection option.

Trace File Logging

As a new diagnostic tool, you can now generate a trace file consisting of the sequence of application event messages. The Options Editor’s Logging page now lets you activate and deactivate, specify a location for, and select a severity level of messages logged. In descending order of severity (and ascending order of total messages logged), Trace Level options are Fatal Errors, Errors, Warning, Info, Debug, Trace, and Max.

DBMS Platform Updates

 

• Sybase IQ 16 - Functionality available for previous Sybase IQ releases is now available against Sybase IQ 16 datasources.
• Apache Hive - This release presents a technical preview of Apache Hive support. As with other technical previews, minimal functionality is provided. Features include datasource registration and connection, a minimal Navigator/Explorer tree, and a minimal set of object management features.

Explorer and Database Monitor Updates

Improved numeric column filtering in Explorer and Database Monitor. Users can now filter numeric columns by using operators (>, =, <, <=, >=). When no operator is used, >= is assumed.

ISQL Windows Backup

For Windows Vista and higher, the auto save feature of DBArtisan is now connected to the Restart Manager. Also, unlike the previous version (o Windows XP) all modified ISQL windows will have a backup saved at a regular interval, even those ISQL windows that do not have a file name associated with them. In the event of a crash, those ISQL windows will automatically open up on the next restart. If the Restart Manager is able to detect the crash, it will attempt to automatically restart the application for you. On Windows XP, this feature will function as it did in past versions.

Connectivity Improvement

Connectivity has been improved to have a central location in the options page to set the driver for all datasources of a certain type to use. An individual datasource can override this setting, but by default all datasources will obey the global setting. No longer will customers have to change each individual datasource if they want to use a particular driver for their system.

Performance Analyst for SQL Server Improvements

Performance Analyst improves the space usage functionalities adding information about the database and object that are taking up the most space on the system.

Java Update

DBArtisan has updated to Java 1.7.

Improved Multi-Monitor Support

Users can now detach any MDI tab from the main application window. They can group these detached tabs together or keep them separate.

 

The Getting the Most Out of Your Embarcadero Licenses webinar is now available on demand. The info shared in this webinar applies to DB PowerStudio, DBArtisan, Rapid SQL, DB Optimizer and ER/Studio.

On-Demand Webinar:
Getting the Most Out of Your 
Embarcadero DB PowerStudio Licenses

Your organization made a good decision when purchasing one or more of the DB PowerStudio tools. The tools are valuable and scalable to fit any size organization’s needs. We want to make sure that you are getting the most out of your investment by showing you what options are available to you and tips to help you keep an eye on your usage scenarios.

Watch Scott Walz, Director of Embarcadero Software Consultants as he provides insight and guidance on Embarcadero’s DB PowerStudio licensing.

From this on-demand webinar, clients will get a better understanding of:

• The different license types and upgrade paths
• Signs that your organization is ready for a license upgrade
• The ROI behind the upgrade
• Next steps to get your upgrade

This session benefits anyone that has or is considering a DB PowerStudio product.

 

 

DATABASE NEWSFLASH

April 2015, Issue 1       ON-DEMAND WEBINAR: 5 Steps to Being a Top DBA – Learning Automation in SQL Server Watch Joseph D’Antoni, SQL expert and evangelist and Scott Walz, Director of Embarcadero Software Consultants as they provide techniques that help you reduce the amount of manual effort in your job and keep you three steps ahead of your users. In this on-demand webinar, you will learn: Fully automating SQL Server installations Automating performance monitoring Syncing jobs and logins between Availability Group members Patching SQL Servers automatically And more! Watch the webinar     FREE TRIALS: Try the Latest Embarcadero Database Tools If you are only using one or two of the popular database tools from Embarcadero, you may be missing out. Try any of these tools free for 14 days and be entered to win an Android tablet when you download and activate your trial. Try DBArtisan® - The premier cross-platform database administration solution Try Rapid SQL® - The intelligent IDE for SQL development Try DB Optimizer™ - Automated SQL profiling & tuning for optimized performance DB PowerStudio trial and promotion rules     WEBINAR SERIES: DBA and Developer Skill Sprints - Beginning April 28th Have you ever wondered how to get more out of your DB PowerStudio tools, but just don’t have the time to “tinker” around and explore?  Starting on April 28th, we will be hosting bi-weekly DBA and Developer Skill Sprints that provide tips, tricks and techniques on how to get the most from your DB PowerStudio tools.  Register for Skill Sprints     TECHNICAL TIPS: Latest Articles and Resources from the Embarcadero Community Beginning Joins with SQL Top 3 Sample Databases for PostgreSQL What You Know, but Don’t Know – Path to the DBA World DBA and Developer Career Tips eBook More from the Embarcadero Community     ON-DEMAND WEBINARS: Missed any of our recent webinars? Watch them now! Congratulations, You’re a DBA… Now what? with Mike Walsh and John Sterrett Find it. Fix it. Real-World SQL Tuning Cases with Karen Morton PL/SQL Tips and Techniques with Dan Hotka More on-demand webinars     SPECIAL OFFER: DB PowerStudio Bundle – All the Tools you Need, at a Great Price Embarcadero DB PowerStudio XE6 gives you everything you need to build and maintain mission-critical database applications at an unprecedented price point. Available in DBA and SQL Developer editions, DB PowerStudio includes popular tools like DBArtisan, DB Optimizer, DB Change Manger and Rapid SQL. Give us a call at 1-888-233-2224 or click below for more information. Move up to DB PowerStudio     COMMUNITY: Get the Latest Updates and Database Tips via Social Media Now you can get informational resources and tips for DBAs and database developers when you follow DB PowerStudio on Twitter, Facebook, Google+ and the new Embarcadero Community web site. Click to see what’s new and follow us for the latest updates. Connect with Embarcadero

Webinar on demand - Back to the Future: Oracle SQL Performance Firefighting using AWR Thank you for your interest in this webinar.  To view or share the presentation with your colleagues, a recorded version is available that can be viewed at your convenience. Topics covered in this webinar include: How to mine and analyze AWR data to review the SQL's performance over time How to validate that the SQL is using the "good" execution plan  Tools to accelerate your analysis of AWR data and SQL code  A copy of the presentation slides is available here.  You can also download the SQL files from the presentation here.

Have a question? Give us a call  1-888-233-2224 or  contact sales

 

Watch the Webinar

Learn how DB Optimizer supports Oracle

Tune in for a live demo of DB Optimizer

These days the fundamentals of taking an interview have not changed much. The more experienced you are, the greater the likelihood of getting into multiple rounds of interviews. If you have been in the industry for close to a decade, when do you start preparing for the interview? This is a tough question with no simple answer. This blog post is around knowing how to prepare for data centric interviews.

Whether you are a DBA or a Developer, the landscape of data is changing fast and it is tough to keep pace with. However, the fundamentals of working with data have not changed for at least 3-4 decades. What are traits that potential employers will look for during a data centric interview?

The basics

Fundamentals don’t change in this industry. As the saying goes, most of the things we do are like the same old wine in a brand new bottle. As you prepare for your interview day, think of the basics. Refresh reading the documentation on what has changed from one version to another. Knowing these details can help you during interviews as you will be better able to articulate your answers.

Get hands-on experience

There is nothing better than getting first-hand experience in implementing a technology. In today’s world, we have seen people try to find shortcuts using search engines. They don’t quite realize that there is nothing better than trying hands-on. With trends like cloud catching on, it is best to actually get an environment to work on. 

If that isn't enough, these days there are tons of Virtual Classes or Virtual Hands-On Labs in the market that can be used to learn new technologies. We have heard from many in the industry that they are not able to work on new technologies because they are working on N-2 version today. Our take to this is simple, don’t expect to work on the latest and greatest every time. If you need to be relevant in the industry, put some extra effort to learn something new all by yourself and make sure to get hands-on experience.

Know the trends in the industry

If you are serious in the domain you work with, it is interesting and important that you keep track of the trends in the industry. If you are in the data field, then some of the basics that we hope every interviewee understands are around NoSQL, Big Data, Visualizations, Data Warehouse, In-Memory Technologies, Appliances, Storage Enhancements, Reporting techniques, Scalability, Performance, Testing, Deployment, Machine Learning and many more. These are just some of topics we thought were worth mentioning at this moment.

Know your tools – Inside-Out

There is no excuse not knowing what you state you know in your resume. We have seen a number of candidates write something and when grilled a layer into the topic they fall apart and the shallow knowledge on the topic surfaces immediately. In this competitive world, getting an interview call is the toughest process. If you have succeeded in getting to this spot, it is total injustice to just give it away so easily. So if you are serious about the point-2 we mentioned above, get hands-on with whatever you do when working. Even if the chance is not present, read and explore as much as possible to make the best impression during interviews.

Conclusion

As we wrap-up, be relaxed as you prepare for you interview. Many times we think the company is recruiting us after the interview. Our thought is simple. If you are really good in the industry, you are the person who is deciding whether you want to join the company. The industry is moving faster than we can realize, so be on top of releases and enhancements that happen inside the world of data. We always consider that any knowledge is valuable. You will surely re-discover yourself and you will be able to land the best for yourself.

Learn more about Embarcadero database tools and try them free:

• DB PowerStudio - The Ultimate Cross Platform Database Tools
• ER/Studio - The ultimate data design, modeling and collaboration solution

 

About Pinal Dave Technology Evangelist & Founder of SQL Authority Pinal Dave works as a Technology Evangelist (Database and BI) with Microsoft India. He has written over 2000 articles on the subject on his blog at http://blog.sqlauthority.com. During his career he has worked both in India and the US, mostly working with SQL Server Technology – right from version 6.5 to its latest form. Pinal has worked on many performance tuning and optimization projects for high transactional systems. He has been a regular speaker at many international events like TechEd, SQL PASS, MSDN, TechNet and countless user groups.