Let me first summarise descent data for modules, following the Stacks project:
Given a ring homomorphism $R \to A$ we can extend scalars for $R$-modules $V$ to get $A$ modules $A \otimes V$ ($\otimes = \otimes_R$). If $M$ is an $A$-module we can ask whether $M$ arises in this way, i.e. is there an $R$-module $V$ such that $M \cong A \otimes V$?
If again $V$ is an $R$-module then there is an $A\otimes A$-linear isomorphism $$ T : A \otimes (A \otimes V) \to (A \otimes V) \otimes A $$ given by $T(a_1 \otimes (a_2 \otimes v)) = (a_1 \otimes v) \otimes a$. Which satisfies a certain cocycle condition.
In general, such an $A\otimes A$-linear isomorphism $$T : A \otimes M \to M \otimes A$$ satisfying the cocycle condition is called descent data and there is a straightforward notion of morphisms, so that we have a category of such data $(M,T)$. Let us denote this category $Desc(A/R)$.
So extension of scalars forms a functor $Mod_R \to Desc(A/R)$ from the category of $R$-modules to that of descent data.
If $R \to A$ is faithfully flat then this functor is an equivalence of categories (cf. the above link).
General question: What are good examples of ring homomorphisms $R \to A$ such that the above functor is not essentially surjective? Of special interest are examples for the restricted case of finitely generated projective modules.
I would really like an example where the map $R \to A$ is as "close" to faithfully flat as possible. Or at least where $Spec(R)$ is "small".
Even more specifically: consider the map $\mathbb{Z}_{(p)}[x] \to \mathbb{Z}_{(p)}$ sending $x$ to $p$. Is the functor surjective in this case? I.e. do there exist (f.g. projective, which are necessarily finite free) $\mathbb{Z}_{(p)}$-modules $F$ with non-effective descent data for the map $\mathbb{Z}_{(p)}[x] \to \mathbb{Z}_{(p)}$?
Let me denote $$ \pi : R \to A $$ the given ring map and $$ F : Mod(R) \to Desc(A/R) $$ the functor induced by extension of scalars and $$ G : Desc(A/R) \to Mod(A) $$ the functor sending $(M,T) \mapsto M$ (i.e. forgets the isomorphism $T$ in the descent datum).
Claim 1: If the canonical map $A \otimes_{\pi,R,\pi} A \to A$ is an isomorphism, then $G$ is an equivalence of categories and the composite $G \circ F$ is naturally isomorphic to the extension of scalars functor $- \otimes_{R,\pi} A$.
Proof idea: We have $A \otimes_{\pi,R,\pi} A \simeq A$ and $A \otimes_{\pi,R,\pi} A \otimes_{\pi,R,\pi} A \simeq A \otimes_{\pi,R,\pi} A \simeq A$; a descent datum $(M,T)$ for $\pi$ consists of an $A$-module $M$ and an $A$-linear isomorphism $T : M \to M$ satisfying the cocycle condition $T \circ T = T$, but this means $T = \mathrm{id}_{M}$.
Claim 2: Suppose $\pi$ is surjective. Then the functor $Mod(R) \to Desc(A/R)$ is essentially surjective.
Proof: Note that $A \otimes_{\pi,R,\pi} A \to A$ is an isomorphism, hence by Claim 1 the category $Desc(A/R)$ is equivalent to $Mod(A)$. For any $A$-module $M$, let $M_{R}$ be $M$ viewed as an $R$-module via $\pi$; then $M_{R} \otimes_{R,\pi} A \simeq M$.
Regarding general question: I could not come up with an example of $\pi$ such that $F$ is not essentially surjective, and I am curious to see an example as well.
However, here is an example where essential surjectivity fails (for sort of silly reasons) if we restrict source and target categories of $F$ to finitely generated, projective modules. Set $R := \mathbb{Z}$ and $A := \mathbb{Z}[\frac{1}{p}] \oplus \mathbb{Z}/(p)$; then $A \otimes_{\pi,R,\pi} A \simeq A$, so by Claim 1 the functor $F$ is naturally isomorphic to the extension of scalars functor $Mod(R) \to Mod(A)$. Now we can take some $A$-module $M$ which is finitely generated projective but has different ranks on the two components of $\operatorname{Spec} A$, e.g. $M := (\mathbb{Z}[\frac{1}{p}])^{\oplus m} \oplus (\mathbb{Z}/(p))^{\oplus n}$ where $m,n$ are distinct positive integers. Since all finitely generated projective $\mathbb{Z}$-modules are finite free, the module $M$ is not isomorphic to an $A$-module of the form $V \otimes_{R,\pi} A$ for a finitely generated projective $\mathbb{Z}$-module $V$. (Note that $M$ is isomorphic to $V \otimes_{R,\pi} A$ for $V := M_{R}$, i.e. the $R$-module obtained by viewing $M$ as an $R$-module via $\pi$, but $M$ is neither finitely generated nor projective as an $R$-module.)
Here is one reason why it seems hard to come up with an example of a ring map $\pi$ where $F$ is not essentially surjective, if we want to keep the flatness hypothesis:
Claim 3: Suppose $\pi$ is finitely presented and flat. Then the functor $Mod(R) \to Desc(A/R)$ is essentially surjective (i.e. all descent data are effective).
Proof: Set $S := \operatorname{Spec} R$ and $X := \operatorname{Spec} A$. Let $U \subseteq S$ be the (set-theoretic) image of the morphism $\varphi : X \to S$ corresponding to $\pi$; it is an open subset of $S$ (e.g. SP Tag 00I1), and it is quasi-compact since it is the image of $X$, which is quasi-compact. Let $j : U \to S$ denote the open immersion; since open immersions are monomorphisms, we have $X \times_{S} X \simeq X \times_{U} X$ and $X \times_{S} X \times_{S} X \simeq X \times_{U} X \times_{U} X$, etc, so there is a natural equivalence of categories $Desc(X/S) \simeq Desc(X/U)$. By faithfully flat descent for quasi-coherent sheaves (see SP Tag 023T), the functor $QCoh(U) \to Desc(X/U)$ is an equivalence. Moreover the restriction functor $j^{\ast} : QCoh(S) \to QCoh(U)$ essentially surjective. Indeed, since $j$ is a quasi-compact quasi-separated morphism, if $\mathscr{F}$ is a quasi-coherent $\mathcal{O}_{U}$-module then the $\mathcal{O}_{S}$-module $j_{\ast}\mathscr{F}$ is quasi-coherent (see SP Tag 01LC), so the pushforward functor $j_{\ast} : QCoh(U) \to QCoh(S)$ gives a right inverse to $j^{\ast}$.
Regarding specific question: The map $\mathbb{Z}_{(p)}[x] \to \mathbb{Z}_{(p)}$ sending $x \mapsto p$ satisfies the conditions of Claim 2, hence all descent data are effective in this case.