SelectViews Show is Live
Eric Harlan on the show today – SharePoint tips and experiences, SQL Server in the cloud, utilities and more. Also, relational databass a thing of the past? (Nope), upcoming events and the SQL Server tip of the day.
[Watch the Show]
Featured Article(s)
High Speed Data Import Using Synthetic Keys
Synthetic keys are a common database design in SQL Server. A synthetic key is a system assigned value that has nothing to do with any of the data contained in the record it represents. Integers or GUIDs are often used as synthetic keys. Many methods are used to generate syntetic keys. Most built in methods are not efficient for the persistence of high volume data (ie. Auto Number or Identity columns), requiring complicated coding or multiple round trips, especially when synthetic keys provide foreign key values in other tables. While GUIDS are a popular option which may be generated in any layer, many developers prefer a sequential synthetic key; especially when no natural key exists in the data being stored. This article demonstrates one method for centrally assigning sequential synthetic keys prior to data persistence, resulting in highly scalable data generation and persistence.
SQL Server Performance Virtual Workshop
Our next workshop is the SQL Server Performance Workshop – I’ll be going through different things to consider, different things to do as you work with SQL Server and need to tune performance. How do things work, what types of things can you monitor (and how) and what can you do with your systems to tune and tweak them? Check out this virtual workshop here and get registered today. I’ll see you there for Q&A and to make sure you get the most help and information possible out of the workshop.
The next workshop date is March 12 starting at 9:00a.
[Get More Information] ** [Register]
Naming Approaches to Avoid… and Tips
Ralph wrote in with some extreme examples of what not to do… (seems like we need a "do this, not this" on naming…)
"I did a contract at a company that dealt with movie rentals and, as a result, named their servers based upon various blockbuster movie titles.
Another place I worked, the databases were databases were named for various planets in the Star Wars series of movies and the Admin passwords were cryptic variations of key players (e.g. D@rt4V8d3r or Darth Vader ;-).
However the worst I’ve run into had servers named SERVER001, SERVER002, etc., and database instances named SQLSERVER001, SQLSERVER002. That naming convention was even carried into the databases as DATABASE001, etc., tables as TABLE001, etc. . . . and they wondered why development was a problem and there were the "occassional" errors in table usages. 😉 "
Just say no. You will have hard-to-imagine issues maintaining this type of approach going forward. I think the most common thing I’ve seen in working with people is somthing like this:
<type><city><location><###>
With variations of course, but an example would be
SQLTUCNORE001
which translates to SQL Server, Tucson, North East Office, server 001. Very close to this type of thing, Kent writes –
"Here’s a solution that worked for an organization with many hundreds of linux servers in 5 data centers on 3 continents. The server name is composed of the data center name, server function, and an ordinal.
DC1WEB01
DC1WEB02
DC2SQL01
DC2SQL02
DC2SQL03
It’s boring, but it provides uniqueness and minimal information (site and usage).
Our main database was partitioned over 6 servers with the database being replicated twice (18 servers). 24/7 access was required so we’d take one of the replication servers offline take backups, and would occasionally swap a replication server into the role of the primary database server. This was a complete nuisance when nearly 1,000 servers had connectivity requirements to servers with specific functionality so we added an entry to our local DNS (or /etc/hosts file) that identified key servers.
DBPRIMARY would map to the server that had the primary database connections. DBREP1 and DBREP2 were the replication servers. All of the servers accessed the database via DBPRIMARY, not DC1SQL01 / DC3SQL04, etc. Swapping the DBPRIMARY server was a coordinated effort that required only about a second to accomplish.
So we solved the problem by giving the server a permanent, boring name and then creating a second name based on functionality."
Whatever you choose – imagine looking at the name outside the context of SQL Server or sitting there staring at your server. For example, digging through your DNS host files, or looking at an extended Active Directory listing of servers… it should be clear what you’re looking at as you consider your servers.