Where does Skyrim store information about active mods and their order

First things first before I get to the advice: Nexus Mod Manager is beta software. If you're experiencing problems with it, consider reporting the problem so that the coders and other users on the official beta feedback forum can read about it and any bugs can be fixed. If there are installation problems, they'll want to know about it and it's more likely to be fixed there than by asking here.

In the meantime, it might help to know that a mod manager is optional—you can install mods without ever touching Nexus Mod Manager. Since it's beta-quality software and you've probably just run into a bug, I'd advise learning how to cut out the middleware and install mods manually. Most mods are simple and it's just a matter of putting the right files in the right place.

Here's the simplified overview of how to install a mod manually:

1. Download the mod you want to install and unpack it to a temporary folder. You'll want to use 7zip for this, because it can handle the popular archiving formats that Skyrim modders pack their mods with. If you have the option of multiple file formats, pick "7zip", the one that end with .7z. Failing that, get the .zip file.

2. Look inside the freshly unzipped mod folder. Look for the readme file. This will tell you what you need to do to install the mod correctly.

   Sometimes the mod author will tell you to "put the files in your data folder" and you'll swear at them for not explaining what that is. They mean \Steam\SteamApps\common\skyrim\Data. If they tell you to put files in "your Data folder", they mean there.

   If they're not clear about what files to put there, you have to look a bit closer at the mod. If you have a folder named like the mod, don't put that folder in your Data folder—you want to put the contents in your Data folder. Same thing if your unpacked mod has a Data folder—you don't want to put that in your Data folder (which would end up with a Data/Data/[mod files] layout that doesn't work), you want to put what's inside it in your Data folder. You want to be looking for folders like textures and meshes, and files that end in .esp or .esm to dump into your Data folder.

   (If you're on Windows XP, you may want to disable UAC to simplify this process, since the game folder is in a UAC-protected area.)

3. Start Skyrim's launcher. If your mod had any .esp or .esm files (these are "plugin" files), you need to tell Skyrim to load these up when it starts. If there weren't any (for example, most texture mods that don't add new items won't have a plugin file), then Skyrim will find the mod files all by itself and you're done.

   So if there are plugin files, the launcher will let you activate them if you click on "Data Files". There will be a list of things, including Skyrim.esm, with checkboxes beside them. Make sure your new mod's plugin files have checkmarks, hit OK to close that window, and then click "Play" to enjoy. Once you've told Skyrim about a plugin file, it remembers, so you only have to do this once for a new mod, assuming it has plugin files.

If you want to read that all again using different words (which sometimes helps immensely in figuring this stuff out), there's a short article on the Nexus Wiki: How to install Skyrim mods. Manual installation is covered in item (8).

Finally, the Nexus forums and official Bethsoft Skyrim forums are very helpful when you run into trouble.

It can be done. Mods from the Workshop are just like mods from any other site except they are directly dropped into your data folder. But because of the way Steam Workshop updates mods, you will need to pay attention to a few things: Skyrim Launcher will check your Workshop subscriptions every time it's launched and merged ESPs would cause the launcher to detect a change in the mod and download the mod again.

You're fine using it as long as you (1) never use the default launcher but SKSE (for example) instead or (2) unsubscribe from the file on Steam Workshop after downloading it, which is the recommended way. You will lose the mod from your nice subscribed files list on Workshop, but you gain the ability to modify and merge the mod without issues. You can still keep the files in favorites or in a collection to keep track of them though.