ArcGIS Server is a multi function Application Server which is used to Serve out Spatial Data.
To truly understand what all it can do, let us try to build something of our own from scratch, when your Data is in Microsoft SQL Server.
Firstly, Your clients will be requesting data over the internet. The Clients could be Browsers. Now Browsers understand only HTTP (No TCP/IP or any custom protocol), so we will need to first make a service which will receive a HTTP request from a client, query the Database and then return the results to the client over HTTP.
The Data that your clients are requesting, are usually not textual. It is not just some simple rows from a table, that you can output as JSON. The Client is requesting an Image of how the spatial data looks. This is not a trivial problem. You will first need to read the data, then draw it in an image. Sometimes the clients are requesting the data to be rendered in a specific way. Sometimes they need Labels. Sometimes more than one table(layer) is requested in the same image. Sometimes the spatial reference is different from the spatial reference of the data. So your server needs to do some heavy computation to transform the data from one projection to another. All this is not easy to do, and will require a lot of code.
Sometimes the client does not have resources. So it will request our service to do some heavy computation, based on some data that is already in our database, and something that the client has provided. (I am talking about Geoprocessing Services). In some cases, the client wants to describe the business logic in the processing, using modular tools (Gp tools, in models and scripts). So you need to provide a framework in which a client can design the workflow and then put the built model on your server, so that it can run on the server.
Remember I told you about the data being rendered in a specific way? The Client wants to change that, or set that. Now you need to build a framework so that the client can design the map output according to their liking.
See how quickly things have become complicated? We have just scratched the surface. I haven't even talked about other kinds of services like Feature services, editing, Network analysis, pdf creation, security aspects, REST endpoint and so on.
All this isn't specific to ArcGIS server. Even an Open Source GIS Server like GeoServer, or MapServer needs to do all this.
Now coming to your question about the 'arcgis' account. This is the OS Account that is used by the service to run. It will be used to provision and use system resources, work with user-based file access, connect to your RDBMS if you use Windows Authentication, and so on.
I would recommend that you go through the ArcGIS help, starting with this Article: What is ArcGIS for Server? After that you should read about the various types of services, and then how to manage it.
