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
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
Here are 10 quick SQL Server tips and tricks for DBAs compiled by SQL Server expert Pinal Dave.
- RAISERROR in the format RAISERROR integer 'string' is discontinued from SQL Serevr 2012.
- SQL Server should be behind the firewall not exposed to Internet.
- We can check the network packet size, by querying the system catalog view sys.confiurations.
- Audited events can be written to the audit log by using the new sp_audit_write procedure.
- SQL Server error logs can reveal a great deal of information about your server.
- Use "allow only encrypted connections" only if needed for end-to-end encryption of sensitive sessions.
- Enable only the optional features that you will immediately use.
- The tcp/ip packet consists of a header that is at least 20 bytes in size.
- The header of the ping packet is 28 bytes plus the size of the buffr you specify.
- 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.
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
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
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.
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 a version control project
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
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 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
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.
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
|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?
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.
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
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.