Databases and Tables
A SQL database is an organized collection of data. Or at least that’s what they taught me in college. In real life, it’s only as organized as the people putting data into the database. Databases contain schemas, tables, stored procedures, reports, views and other objects. Most databases will contain multiple tables. Tables contain rows that have data in them. I like to think of a database kinda’ like an Excel spreadsheet. Each tab on a spreadsheet is similar to a table; each row is similar to a row in a database and each column in the spreadsheet is somewhat similar to a column, or attribute. The headers are kinda’ like the schema.
These are overly simplistic explanations. And whenever you oversimplify something, you run the risk of miscommunication, but it helps as a starting place. This page is meant to be a short and easy guide to get started writing SQL queries. More links will appear throughout the page that point to other posts on my site, so stay tuned. Throughout my exercises in this page, I will use the following sample database, which has five records (one for each ID) and seven columns (ID,Site,Contact,Address,City,Zip, and Country).
Below is a selection from the “Customers” table (note that when querying data, SQL commands are NOT case sensitive)
ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US
Most tasks you will execute against a database are done with SQL statements. Think of this as a query, an insert, a change, or a delete operating. For example, to see all of your data, you would select all of the records from a database using the SELECT statement. Then we’ll ask for all, or *, and tell the command to show us where the data is coming from, which is the Customers table. Finally, we’ll be nice and tidy and put a semi-colon at the end; although if you forget, you can always do so after you hit return:
SELECT * FROM Customers;
The SELECT statement is the most common command I run in SQL. This is how you query data, build reports, derive the layout of a database and so, so much more.
Other Important SQL Commands covered in this series (if there is no link, I haven’t written that article yet):
- SELECT – Query and pull information from a database
- CREATE TABLE – Create tables in specified databases
- DELETE – Delete data
- UPDATE – Update data in a database
- DROP TABLE – Delete tables
- INSERT INTO – Inserts new data into a specified database
- CREATE DATABASE – Create databases
- ALTER DATABASE – Modify databases
- ALTER TABLE – Modify tables
- CREATE INDEX – Create indexes
- DROP INDEX – Deletes indexes
- INNER JOIN – Merge rows in a database
krypted January 26th, 2016
Posted In: SQL
alter, create, drop, index, join, query, select, SQL Introduction, table, update
By default, most computers come with one partition and one volume on that partition. Well, in OS X there’s also a recovery partition, but that’s hidden so we’ll pretend like there’s just one. You can create additional volumes, which are useful for a number of different scenarios. The operation of creating partitions usually involves resizing a partition. That can be somewhat dangerous, so make sure to backup your Mac before doing so.
To create an additional partition (and by default an HFS+ filesystem on that partition), first open Disk Utility from /Applications/Utilities.
Note that by default, the boot volume is highlighted. You can’t create a partition inside a volume or partition, so click on the name of the disk above that.
Here, you can choose to run First Aid, Erase, Mount/Unmount, and Info. Most are unavailable when clicked on a disk, so let’s click on Partition. Doing so shows you each partition on the physical disk.
You can click on each partition to see information about the partition. Let’s click on the plus sign (+) to create our new partition.
When prompted, provide a name for the partition. You can choose a different format for the partition, but let’s leave that as the default for now. Then enter a size and click on Apply.
If you’re taking space away from a partition, the old partition will be resized as a smaller partition, provided that there’s enough free space to do so.
Once the process is complete, you should see your new volume mount.
krypted December 14th, 2015
Posted In: Mac OS X, Mac OS X Server, Mac Security, Mass Deployment
create, HFS+, MAC, partition, resize, volume
Yosemite brought Xsan 4, which included a whole new way to add clients to an Xsan. Xsan Admin is gone. From now on, instead of scanning the network using Xsan Admin. we’ll be adding clients using a Configuration Profile. This is actually a much more similar process to adding Xsan clients to a StorNext environment than it is to adding clients to Metadata Controllers running Xsan 3 and below. But instead of making a fsnameservers file, we’re plugging that information into a profile, which will do that work on the client on our behalf. To make the Xsan configuration profile, we’re going to use Profile Manager. With OS X Server 5, this trend continues.
To get started, open the Profile Manager web interface and click on a device or device group (note, these are scoped to systems so cannot be used with users and user groups). Then click on the Settings tab for the object you’re configuring Xsan for.
Click Edit for the profile listed (Settings for <objectname>) and scroll down until you see the entry for Xsan.
From the Xsan screen, click Configure.
This next screen should look a little similar, in terms of the information you’ve plugged into the Xsan 4 setup screen. Simply enter the name of the Xsan in the Xsan Name field, the IP address or host names of your metadata controllers in the File System Name Servers field and the Authentication Secret from the Xsan screen in the Server app into the Authentication Secret field. Click OK to close the dialog.
Click Save to save your changes. Then you’ll see the Download button become clickable.
The profile will download to your ~/Downloads directory as Settings_for_<OBJECTNAME>.mobileconfig. So this was called test and will result in a name of Settings_for_test.mobileconfig. That profile will automatically attempt to install. If this is an MDC where you’re just using Profile Manager to bake a quick profile, or if you don’t actually want to install the profile yet, click Cancel.
If you haven’t worked with profiles that much, note that when you click Show Profile, it will show you what is in the profile and what the profile can do.
Simply open this file on each client (once you test it of course) and once installed, they’ll automatically configure to join your Xsan. If you don’t have a Profile Manager server, you can customize this file for your environment (YMMV): Settings_for_test.mobileconfig
krypted October 12th, 2015
Posted In: Mac OS X, Mac OS X Server, Mac Security, Mass Deployment, Xsan
configure xsan clients, create, Mac Servers, mobileconfig, profile manager, Xsan, xsan 4, xsan 5
Under the hood, OS X Server has a number of substantial changes; however, at first the Server app appears to have had very few changes. The changes in the Server app were far more substantial in the Yosemite version of OS X Server. All of the options from Yosemite are still there and using the new command line interface for managing the service, there are far more options than ever before.
The DNS service in OS X Server, as with previous versions, is based on bind 9 (BIND 9.9.2-P2 to be exact). This is very much compatible with practically every DNS server in the world, including those hosted on Windows, OS X, Linux and even Zoe-R.
The first time you open the DNS Service click on the DNS service in the ADVANCED section of the list of SERVICES.
Then, click on the cog wheel icon below the list of records and click on Show All Records.
At the Records screen, you’ll now see forward and reverse record information. Click the Edit… button for the Forwarding Servers field. Here, you’ll be able to enter a Forwarders, or DNS servers that resolve names that the server you’re using can’t resolve using its own DNS records.
Click the plus sign to enter the IP address of any necessary Forwarders. Enter the IP address of any Forwarding servers, then click OK to save your changes.
Once back at the main DNS service control screen, click the Edit… button for Perform lookups for to configure what computers the DNS server you are setting up can use the DNS service that the server is hosting.
At the Perform Lookups screen, provide any additional subnets that should be used. If the server should be accessible by anyone anywhere, just set the “Perform lookups for” field at the DNS service screen to “all clients”.
All you have to do to start the DNS is click on the ON button (if it’s not already started, that is). There’s a chance that you won’t want all of the records that are by default entered into the service. But leave it for now, until we’ve covered what everything is. To list the various types of records:
- Primary Zone: The DNS “Domain”. For example, www.krypted.com would likely have a primary zone of krypted.com.
- Machine Record: An A record for a computer, or a record that tells DNS to resolve whatever name is indicated in the “machine” record to an IP address, whether the IP address is reachable or not.
- Name Server: NS record, indicates the authoritative DNS server for each zone. If you only have one DNS server then this should be the server itself.
- Reverse Zone: Zone that maps each name that IP addresses within the zone answer with. Reverse Zones are comprised of Reverse Mappings and each octal change in an IP scheme that has records mapped represents a new Reverse Zone.
- Reverse Mapping: PTR record, or a record that indicates the name that should respond for a given IP address. These are automatically created for the first IP address listed in a Machine Record.
- Alias Record: A CNAME, or a name that points to another name.
- Service Record: Records that can hold special types of data that describe where to look for services for a given zone. For example, iCal can leverage service records so that users can just type the username and password during the setup process.
- Mail Exchanger Record (aka MX record): Mail Exchanger, points to the IP address of the mail server for a given domain (aka Primary or Secondary Zone).
- Secondary Zone: A read only copy of a zone that is copied from the server where it’s a Primary Zone when created and routinely through what is known as a Zone Transfer.
When you click on the plus sign, you can create additional records. Double-clicking on records (including the Zones) brings up a screen to edit the record.
These include the name for the zone. As you can see, a zone was created with the hostname rather than the actual domain name. This is a problem if you wish to have multiple records in your domain that point to the same host name. Theoretically you could create a zone and a machine record for each host in the domain, but the right way to do things is probably going to be to create a zone for the domain name instead of the host name. So for the above zone, the entry should be krypted.com rather than mavserver.krypted.com (the hostname of the computer). Additionally, the TTL (or Time To Live) can be configured, which is referenced here as the “Zone data is valid for” field. If you will be making a lot of changes this value should be as low as possible (the minimum value here is 5 minutes). Once changes are made, the TTL can be set for a larger number in order to reduce the amount of traffic hitting the server (DNS traffic is really light, so probably not a huge deal in most environments using a Yosemite Server as their DNS server). Check the box for “Allow zone transfers” if there will be other servers that use this server to lookup records.
Additionally, if the zone is to be a secondary zone configured on another server, you can configure the frequency to perform zone transfers at this screen, how frequently to perform lookups when the primary name server isn’t responsive and when to stop bothering to try if the thing never actually ends up coming back online. Click on Done to commit any changes made, or to save a new record if you’re creating a new zone.
Note: To make sure your zone name and TLD don’t conflict with data that already exists on the Internet, check here to make sure you’re not using a sponsored TLD.” — http://krypted.com/mac-os-x/dont-go-near-there-sponsored-top-level-domain-names/
Double-click on a Machine record next (or click plus to add one). Here, provide a hostname along with an IP address and indicate the Zone that the record lives in. The IP Addresses field seems to allow for multiple IPs, which is common in round robin DNS, or when one name points to multiple servers and lookups rotate amongst the servers. However, it’s worth mentioning that when I configure multiple IP addresses, the last one in the list is the only one that gets fed to clients. Therefore, for now at least, you might want to stick with one IP address per name.
Note that the above screen has a match for the host name to the zone name, including the zone name. This is not to be done for manually created records. Enter the name of a record, such as www for the zone called, for example, krypted.lan and not www.krypted.lan in the Host Name record, or you will end up creating a host called www.krypted.lan.krypted.lan, which is likely not very desirable. Given that this wasn’t the default behavior back in Server Admin, I personally consider this something that will likely get fixed in the future. Click Done to commit the changes or create the new record.
Next, let’s create a MX record for the domain. To create the MX for the domain, click on the plus sign at the list of records.
Select the appropriate zone in the Zone field (if you have multiple zones). Then type the name of the A record that you will be pointing mail to. Most likely, this would be a machine record called simply mail, in this case for krypted.lan, so mail.krypted.lan. If you have multiple MX records, increment the priority number for the lower priority servers.
As a full example, let’s create a zone and some records from scratch. Let’s setup this zone for an Xsan metadata network, called krypted.xsan. Then, let’s create our metadata controller record as starbuck.krypted.xsan to point to 10.0.0.2 and our backup metadata controller record as apollo.krypted.xsan which points to 10.0.0.3. First, click on the plus sign and select Add Primary Zone.
At the zone screen, enter the name krypted.xsan, check the box for Allow zone transfers (there will be a second server) and click on the Done button. Click on the plus sign and then click on Add Machine record.
At the New Machine Record screen, select krypted.xsan as the Zone and then enter starbuck as the Host Name and click on the plus sign for IP Addresses and type in 10.0.0.2. Click on Done to commit the changes.
Repeat the process for Apollo, entering apollo as the Host Name and 10.0.03 as the IP address. Click Done to create the record.
Setting Up Secondary Servers
Now let’s setup a secondary server by leveraging a secondary zone running on a second computer. On the second Yosemite Server running on the second server, click on the plus sign for the DNS service and select Add Secondary Zone.
At the Secondary Zone screen, enter krypted.xsan as the name of the zone and then the IP address of the DNS server hosting that domain in the Primary Servers field. Click Done and the initial zone transfer should begin once the DNS service is turned on (if it hasn’t already been enabled).
Managing DNS From The Command Line
Now, all of this work we’ve been doing has been pretty straight forward. Create a zone, create some records inside the zone and you’re good to go. But there are a lot of times when DNS just needs a little more than what the Server app can do for you. For example, round robin DNS records, bind views, etc. Therefore, getting used to the command line is going to be pretty helpful for anyone with more than a handful of records. The first thing to know about the DNS command line in OS X Yosemite Server is to do everything possible using the serveradmin command. To start the service, use the start option:
sudo serveradmin start dns
To stop the service, use the stop option:
sudo serveradmin stop dns
To get the status of the service, including how many zones are being hosted, the last time it was started, the status at the moment, the version of bind (9.8.1 right now) and the location of the log files, use the fullstatus option:
sudo serveradmin fullstatus dns
A number of other tasks can be performed using the settings option. For example, to enable Bonjour Client Browsing, an option previously available in Server Admin, use the following command:
sudo serveradmin settings dns:isBonjourClientBrowsingEnabled = yes
Subnets can be created programmatically through serveradmin as well. Let’s look at what our krypted.xsan subnet looks like, by default (replace your zone name w/ krypted.xsan to see your output):
sudo serveradmin settings dns:views:_array_id:com.apple.ServerAdmin.DNS.public:primaryZones:_array_id:krypted.xsan
Now, let’s say we’d like to disable bonjour registration of just this zone, but leave it on for the others on the server:
sudo serveradmin settings dns:views:_array_id:com.apple.ServerAdmin.DNS.public:primaryZones:_array_id:krypted.xsan:bonjourRegistration = no
The entire block can be fed in for new zones, if you have a lot of them. Just remember to always make sure that the serial option for each zone is unique. Otherwise the zones will not work properly.
While serveradmin is the preferred way to edit zone data, it isn’t the only way. In /private/var/named are a collection of each zone the server is configured for. Secondary zones are flat and don’t have a lot of data in them, but primary zones contain all the information in the Server app and the serveradmin outputs. To see the contents of our test zone we created, let’s view the /Library/Server/named/db.krypted.xsan file (each file name is db. followed by the name of the zone):
Add another record into the bottom and stop/start DNS to immediately see the ramification of doing so. Overall, DNS is one of those services that seems terribly complicated at first. But once you get used to it, I actually find manually editing zone files far faster and easier than messing around with the Server app or previously Server Admin. However, I also find that occasionally, because the Server app can make changes in there that all my settings will vanish.
Troubleshooting is another place where the command line can be helpful. While logs can be found in the Server app, I prefer to watch log entries live as I perform lookups using the /Library/Logs/named.log file. To do so, run tail -f followed by the name of the file:
tail -f /Library/Logs/named.log
Also, see http://krypted.com/mac-os-x-server/os-x-server-forcing-dns-propagation for information on forcing DNS propagation if you are having issues with zone transfers. Finally, you can manage all records within the DNS service using the new /Applications/Server.app/Contents/ServerRoot/System/Library/PrivateFrameworks/DNSManager.framework/dnsconfig command line tool. I’ve written an article on managing DNS using this tool at
krypted October 16th, 2014
Posted In: Mac OS X, Mac OS X Server, Mac Security, Mass Deployment
create, DNS, Mac OS X Server, named, server admin, server.app, serveradmin, zones
I was recently building some scripts to alternate shadow copy storage locations (long story). In so doing, I found that pretty much anything I would normally do in the GUI is exposed at the command prompt using the vssadmin command. The first and one of the most important things that you can do is list settings. This includes the following:
- providers – lists components that can create and maintain shadow copies
- shadows – lists shadow copies
- shadowstorage – lists storage that can be used for shadow copies
- volumes – lists volumes that have shadow copy enabled
- writers – lists registered components of applications that store persistent information on a volume with shadow copy enabled
You can create a shadow copy of a volume using the vssadmin command along with the create shadow verb. You would also need to specify which drive you will be creating a shadow copy of using the /for option. For example:
vssadmin create shadow /for=C:
Once you have created a shadow copy you can then add more storage to it using the vssadmin add shadowstorage command. Here, you’d indicate the volume to enable shadow copy /for and then the volume to put the shadow copy storage /on and finally the maximum size with /maxsize, as follows:
vssadmin add shadowstorage /for=c: /on=d: /maxsize=1tb
Once you have storage, you can also resize it. To do so use the resize verb and then mimic what you did previously, when adding shadowstorage but replacing the maxsize variable with a new number. For example:
vssadmin resize shadowstorage /for=c: /on=d: /maxsize=100gb
In addition to being able to resize the storage you can also delete it using the delete verb along with shadowstorage. For example:
vssadmin delete shadowstorage /for=c: /on=d:
And finally you can also delete shadows themselves
vssadmin delete shadows /for=c: /all
Or to just delete the oldest:
vssadmin delete shadows /for=c: /oldest
Or finally, to just delete one from the list of shadows (where the X’s are replaced with your shadow id obtained using the list shadows):
vssadmin delete shadows /for=c: /shadow=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX
Still can’t seem to make it work with Samba on Mac OS X, but that’s another story for another day… 🙂
krypted October 20th, 2009
Posted In: Windows Server
create, list, shadow copy, shadows, shadowstorage, vssadmin